lock out menus?

  • Thread starter Thread starter Laphan
  • Start date Start date
L

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
 
Laphan said:
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??
Click to expand...

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
 
Many thanks JE


Laphan said:
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??
Click to expand...

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
 
Back
Top