Open a form from Tool bar

[Fredriksson via OfficeKB.com]

I would like to create a button on the tool bar that will open a form. I
only want this button to appear on the tool bar when a particular workbook is
open.

Can this be done. If so, how?

Thanks

 

[paul.robinson]

Hi
Put this code in your "ThisWorkbook" Module in the Visual Basic Editor
Private Sub Workbook_Activate()
Call Add_Menu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Remove_Menu
End Sub

Private Sub Workbook_Deactivate()
Call Remove_Menu
End Sub

Private Sub Workbook_Open()
Call Add_Menu
End Sub

In a general Module put in the code

Public Sub Add_Menu()
Dim cbWSMenuBar 'Variable name for main Worksheet Menu Bar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer 'item number of Help menubar item

Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
'If Excel crashed while last opened so that Before_Close() event
didn't happen
'the menubar may still exist. So delete it just in case
On Error Resume Next
cbWSMenuBar.Controls("Form Menu").Delete
On Error GoTo 0

iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup,
before:=iHelpIndex)

With muCustom
.Caption = "Form Menu"
With .Controls.Add(Type:=msoControlPopup)
With .Controls.Add(Type:=msoControlButton)
.Caption = "Form Input"
.OnAction = "Show_Userform"
End With
Set cbWSMenuBar = Nothing
Set muCustom = Nothing
End Sub

Public Sub Remove_RegisterMenu()
Dim cbWSMenuBar As CommandBar

On Error Resume Next 'Incase it has already been deleted
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Form Menu").Delete
Set cbWSMenuBar = Nothing
End Sub

The macro to call up your userform is called "Show_Userform" and
should again be in a general module.

regards
Paul