I have tested this code a little and it seems to work fine in taking away user control and then restoring it when another workbook is active but am I going to run into other problems down the road using it?
Dim CB As CommandBar
Dim C As CommandBarControl
Dim ID
Option Explicit
Private Sub Workbook_Activate()
Disable_RightClick_Toolbar
End Sub
Private Sub Workbook_Deactivate()
Enable_RightClick_ToolBar
End Sub
Sub Disable_RightClick_Toolbar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
Application.CommandBars("Toolbar List").Enabled = False
Application.CommandBars("Visual Basic").Enabled = False
Dim cntrl As CommandBarControl
With CommandBars("Worksheet Menu Bar")
.Controls("file").Enabled = False
.Controls("edit").Enabled = False
.Controls("view").Enabled = False
.Controls("insert").Enabled = False
.Controls("file").Enabled = False
.Controls("data").Enabled = False
.Controls("tools").Enabled = False
.Controls("format").Enabled = False
End With
End Sub
Sub Enable_RightClick_ToolBar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = True
Next
Application.CommandBars("ToolBar List").Enabled = True
Application.CommandBars("Visual Basic").Enabled = True
Dim cntrl As CommandBarControl
With CommandBars("Worksheet Menu Bar")
.Controls("file").Enabled = True
.Controls("edit").Enabled = True
.Controls("view").Enabled = True
.Controls("insert").Enabled = True
.Controls("file").Enabled = True
.Controls("data").Enabled = True
.Controls("tools").Enabled = True
.Controls("format").Enabled = True
End With
End Sub
Thanks,
John
Dim CB As CommandBar
Dim C As CommandBarControl
Dim ID
Option Explicit
Private Sub Workbook_Activate()
Disable_RightClick_Toolbar
End Sub
Private Sub Workbook_Deactivate()
Enable_RightClick_ToolBar
End Sub
Sub Disable_RightClick_Toolbar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = False
Next
Application.CommandBars("Toolbar List").Enabled = False
Application.CommandBars("Visual Basic").Enabled = False
Dim cntrl As CommandBarControl
With CommandBars("Worksheet Menu Bar")
.Controls("file").Enabled = False
.Controls("edit").Enabled = False
.Controls("view").Enabled = False
.Controls("insert").Enabled = False
.Controls("file").Enabled = False
.Controls("data").Enabled = False
.Controls("tools").Enabled = False
.Controls("format").Enabled = False
End With
End Sub
Sub Enable_RightClick_ToolBar()
ID = 30017
For Each CB In Application.CommandBars
Set C = CB.FindControl(ID:=ID, recursive:=True)
If Not C Is Nothing Then C.Enabled = True
Next
Application.CommandBars("ToolBar List").Enabled = True
Application.CommandBars("Visual Basic").Enabled = True
Dim cntrl As CommandBarControl
With CommandBars("Worksheet Menu Bar")
.Controls("file").Enabled = True
.Controls("edit").Enabled = True
.Controls("view").Enabled = True
.Controls("insert").Enabled = True
.Controls("file").Enabled = True
.Controls("data").Enabled = True
.Controls("tools").Enabled = True
.Controls("format").Enabled = True
End With
End Sub
Thanks,
John