lock out menus?

[Laphan]

Hi All

I want to create a VBA/SQL report in Excel, which I will sort out, but once
I have done this I'd like to lock out either the Data menu or preferably
just the External Data and Macro menus. Is this possible in Excel 2000 on
PC and Excel 98/2001 for Mac??

Thanks

Laphan

 

[J.E. McGimpsey]

Hi All

I want to create a VBA/SQL report in Excel, which I will sort out, but once
I have done this I'd like to lock out either the Data menu or preferably
just the External Data and Macro menus. Is this possible in Excel 2000 on
PC and Excel 98/2001 for Mac??

One way:

To initially disable (put in a regular code module):

Public Sub DisableMenuItems()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = False
.Controls("Data").Controls( _
"Get External Data").Enabled = False
End With
End Sub

However, I would trash your application if you disabled those menus
for any other workbooks I may have open, so please put these in the
ThisWorkbook code module of your workbook:

Private Sub Workbook_Activate()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = False
.Controls("Data").Controls( _
"Get External Data").Enabled = False
End With
End Sub

Private Sub Workbook_Deactivate()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = True
.Controls("Data").Controls( _
"Get External Data").Enabled = True
End With
End Sub

 

[Laphan]

Many thanks JE

Hi All

I want to create a VBA/SQL report in Excel, which I will sort out, but once
I have done this I'd like to lock out either the Data menu or preferably
just the External Data and Macro menus. Is this possible in Excel 2000 on
PC and Excel 98/2001 for Mac??

One way:

To initially disable (put in a regular code module):

Public Sub DisableMenuItems()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = False
.Controls("Data").Controls( _
"Get External Data").Enabled = False
End With
End Sub

However, I would trash your application if you disabled those menus
for any other workbooks I may have open, so please put these in the
ThisWorkbook code module of your workbook:

Private Sub Workbook_Activate()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = False
.Controls("Data").Controls( _
"Get External Data").Enabled = False
End With
End Sub

Private Sub Workbook_Deactivate()
With Application.CommandBars("Worksheet Menu Bar")
.Controls("Tools").Controls("Macro").Enabled = True
.Controls("Data").Controls( _
"Get External Data").Enabled = True
End With
End Sub