Control panel detection

[Jags]

Hi guys,

Im working on a spreadsheet and would like to make sure that when a
viewer opens the spreadsheet the toolbars(the save and open icons etc
etc) on the top of the screen are removed. However, I would like to
return the users choices of toolbars back once they close the
spreadsheet.

I have been looking at envoking a macro on open and close of
spreadsheet but this still does not help me detect which toolbars the
user has selected... below is some test code of a lil something i tried
(didnt work though!)

' TEST Macro
' Macro recorded 14/02/2006 by x

If (Application.CommandBars("Standard").Visible = False) Then
Range("A20").Select
ActiveCell.FormulaR1C1 = "1"
Range("A21").Select
If (Application.CommandBars("Standard").Visible = True) Then
Range("A21").Select
ActiveCell.FormulaR1C1 = "0"
Range("A21").Select
End Sub

thanks in advance for any help

 

[Norman Jones]

Hi Jags,

Try;

'=============>>
Private Sub Workbook_Activate()
Dim CBar As CommandBar

For Each CBar In Application.CommandBars
CBar.Enabled = False
Next
End Sub
'<<=============

'=============>>
Private Sub Workbook_Deactivate()
Dim CBar As CommandBar

For Each CBar In Application.CommandBars
CBar.Enabled = True
Next
End Sub
'<<=============

This is workbook event code and should be pasted into the workbook's
ThisWorkbook module *not* a standard module or a sheet module:

Right-click the Excel icon on the worksheet
(or the icon to the left of the File menu if your workbook is maximised)

Select 'View Code' from the menu and paste the code.

 

[Jags]

cheers norman, works a treat