Vertical Custom Menubar

[Guest]

Please
I would like a create a custom menubar and have it display vertically on the right side of the scree
when a specific worksheet is activated. Can this be done
thanks in advance

 

[Vasant Nanavati]

Put this code in the worksheet module:

Private Sub Worksheet_Activate()
CommandBars.Add "NewBar", msoBarRight
CommandBars("NewBar").Visible = True
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
CommandBars("NewBar").Delete
End Sub

 

[Doug Glancy]

Bret,

This is how I do it. You need the various Activate and Deactivate events to
make sure it behaves correctly moving between workbooks as well as
worksheets. Paste this code into the ThisWorkbook module:

Option Explicit
Private Sub Workbook_Activate()
If ThisWorkbook.ActiveSheet.Name = "Sheet1" Then
Call create_tester
End If
End Sub

Private Sub Workbook_Deactivate()
Call delete_tester
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" Then
Call create_tester
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Call delete_tester
End Sub

Sub create_tester()
Dim cbar As CommandBar
Call delete_tester 'delete if already exists
Set cbar = Application.CommandBars.Add _
(Name:="tester", Position:=msoBarRight, temporary:=True)
cbar.Visible = True
End Sub

Sub delete_tester()
On Error Resume Next
Application.CommandBars("tester").Delete
On Error GoTo 0
End Sub

hth,

Doug