Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a form from Tool bar
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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a form from Tool bar
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, befo=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 On Mar 8, 5:53 pm, "Fredriksson via OfficeKB.com" <u27002@uwe wrote: 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 -- Message posted viahttp://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i use form/skjema tool for meny list spred in dif arks | Excel Worksheet Functions | |||
how to keep the tool bar open | Setting up and Configuration of Excel | |||
Using a template form, advance a form number everytime you open | Excel Discussion (Misc queries) | |||
Worksheets open behind tool bars | Excel Discussion (Misc queries) | |||
Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? | Excel Programming |