Question re view options in excel 2003

  oo7juk 10:26 01 Aug 2007
Locked

Hi,

I have created a spreadsheet and have unticked some of the options within the view tab e.g scroll bars, tabs, and row/column headers. Is there anyway I can apply these changes to this workbook only rather than the complete software.

Thanks

  VoG II 10:48 01 Aug 2007

Yes, you can do this using a macro:

Sub HideOptionItems()
ThisWorkbook.Protect Windows:=True
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With
End Sub


To restore, use the reverse:

Sub ShowOptionItems()
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
End Sub


If you rename these macros to Auto_Open and Auto_Close respectively they will run automatically when the workbook is opened and closed.

  oo7juk 11:11 01 Aug 2007

VoG,

When you say rename do you mean replace'Sub ShowOptionItems()'. Also want to hide all the toolbars, when I go into toolbars/customize it won't let me untick the worksheet menu bar?

Many thanks.

  VoG II 11:26 01 Aug 2007

Try this

Sub Auto_Open()
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=True
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With
For Each cBar In CommandBars
cBar.Enabled = False
Next cBar
End Sub

Sub Auto_Close()
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
End Sub

  oo7juk 23:38 01 Aug 2007

Thanks VoG,

Forgot to mention, looking also to remove the formula bar and status bar. Looked in the VBA display options but couldn't see them. Also I have protected document apart from one cell (drop-down within data val) but it still asks if I want to save changes, is this a result of the drop-down menu.

When other open sheet will they have to reduce their security settings to prevent macro message appearing?

Many thanks.

  VoG II 08:39 02 Aug 2007

Sub Auto_Open()
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=True
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
For Each cBar In CommandBars
cBar.Enabled = False
Next cBar
Application.DisplayAlerts = False
End Sub

Sub Auto_Close()
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
Application.DisplayAlerts = True
End Sub

Obviously the users will have to agree to run macros or reduce their security settings in order for these to run. Macro security settings cannot be adjusted using VBA code, for obvious reasons.

  oo7juk 11:16 02 Aug 2007

Thanks Vog that done the trick, I was putting addtions directly beneath 'displayheadings'. One more question if I may.


I have a combo box (drop-down menu) within my sheet but, when I go to protect sheet it won't let me select to access box. Can it be done where the whole sheet is protected apart from the combo box. The combo is linked to a cell whicj trigger my vlookups. Also any good sites re learning how to do the basics of writing macros.

Many thanks.

  oo7juk 11:25 02 Aug 2007

VoG,

Copied the above macros into my worksheet, but when I opened another exel doc it only displayed the following -

row header
column header
sheet tabs

When I copied your macro I entered a module for the 'false' options then another macro for the 'true' options.

  VoG II 11:32 02 Aug 2007

If you right click the combo box and select Format Control you should be able to untick Locked on the Protection tab. You need to do this before protecting the sheet. You might also need to unprotect the cell linked to the combo box.

If you want to learn VBA for Excel I recommend click here - it isn't free but covers just about everything you might need to know in a fairly informal way - i.e. it doesn't start off by giving a lecture about Objects, Methods and Properties.

  VoG II 11:37 02 Aug 2007

Sorry - I cross-posted.

Right click a sheet tab and select View Code. Copy and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
Beep
End Sub

Close the code window, then select a different cell. The missing menus etc. should reappear.

  oo7juk 15:05 02 Aug 2007

Thanks VoG,

In relation to the combo box I managed to untick the box, but as you said the linked cell also had to be unticked which meant that anyone could edit cell and delete data etc. I think I will just use data valadation that way I can protect the whole sheet. Thanks.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?