Menu for user forms

H

Hank Hendrix

I have about 10 user forms. How can I create a new menu to select a user
form. The user forms contain prepared statements for use in a given area
(i.e. electrical, plumbing, grounds, etc)
The menu should be available only for this workbook.

Thanks
Hank
 
V

Vasant Nanavati

This is pretty crude but should give you some ideas. CreateMenu should be
called from the Workbook_Activate event and DeleteMenu from the
Workbook_Deactivate event.

Sub CreateMenu()
Dim UFBar As CommandBar
Dim newButton As CommandBarButton
Set UFBar = CommandBars.Add("UserForms")
Set newButton = UFBar.Controls _
.Add
With newButton
.Style = msoButtonCaption
.Caption = "UserForm1"
.OnAction = "ShowUF1"
End With
Set newButton = UFBar.Controls _
.Add
With newButton
.Style = msoButtonCaption
.Caption = "UserForm2"
.OnAction = "ShowUF2"
End With
UFBar.Visible = True
End Sub

Sub ShowUF1()
UserForm1.Show
End Sub

Sub ShowUF2()
UserForm2.Show
End Sub

Sub DeleteMenu()
CommandBars("UserForms").Delete
End Sub

Of course, you will need to add error handling. Also, you can avoid writing
10 separate subs for the 10 forms but it requires quite a bit of effort.
 
B

Bob Phillips

Suggested amendment

Set UFBar = CommandBars.Add("UserForms")

to

Set UFBar = CommandBars.Add("UserForms", temporary:=True)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
V

Vasant Nanavati

Thanks, Bob!

Regards,

Vasant.

Bob Phillips said:
Suggested amendment

Set UFBar = CommandBars.Add("UserForms")

to

Set UFBar = CommandBars.Add("UserForms", temporary:=True)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
R

ross

This is the code i used, em, called from open this work book and close
this work book like the other chap said, i added i've stuck them in
the charts menu bar and the file menue bar, and i have charts in my
workbook, may not be relivent for you:

This code is orgianlly from J-walk, cant remeber if i have adapteit it
any.

Just another example really

Good Luck

Ross.


Public Sub AddMenuBars()

'delets if alreadg there, stops muilily menu if error occures ;-)
Call RemoveMenuBars

'Set up nu menu
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton

Set HelpMenu = CommandBars(1).FindControl(Id:=30010)

If HelpMenu Is Nothing Then
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, Temporary:=True)
Else
Set NewMenu = CommandBars(1).Controls _
.Add(Type:=msoControlPopup, Before:=HelpMenu.Index,
Temporary:=True)
End If

NewMenu.Caption = "E&DCSM"


Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "&Controls"
.FaceId = 1845
.OnAction = "OpenControlsForm"
End With

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "C&hart Options"
.FaceId = 433
.OnAction = "OpenChartsForm"
End With

End Sub

Public Sub RemoveChartsMenuBars()
On Error Resume Next
CommandBars(2).Controls("E&DCSM").Delete
End Sub

Public Sub RemoveMenuBars()
On Error Resume Next
CommandBars(1).Controls("E&DCSM").Delete
End Sub
Public Sub OpenControlsForm()
Dim wb As Workbook
Dim cs As Chart

For Each wb In Application.Workbooks
For Each cs In wb.Charts
If cs.Name = "DisSatLines" Then
wb.Activate
ActiveWindow.WindowState = xlMaximized
frmControls.Show
Else: End If
Next cs
Next wb

End Sub
Public Sub OpenChartsForm()
Dim wb As Workbook
Dim cs As Chart

For Each wb In Application.Workbooks
For Each cs In wb.Charts
If cs.Name = "DisSatLines" Then
wb.Activate
ActiveWindow.WindowState = xlMaximized
Charts("DisSatLines").Activate
Else: End If
Next cs
Next wb

End Sub

Public Sub OpenChartsForm1()
Dim wb As Workbook
Dim cs As Chart

For Each wb In Application.Workbooks
For Each cs In wb.Charts
If cs.Name = "DisSatLines" Then
wb.Activate
ActiveWindow.WindowState = xlMaximized
frmCharts.Show
Else: End If
Next cs
Next wb

End Sub


Public Sub AddChartMenuBars()
'delets if alreadg there, stops muilily bar if error occures ;-)
Call RemoveChartsMenuBars

'Set up nu menu
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim Submenuitem As CommandBarButton

Set HelpMenu = CommandBars(2).FindControl(Id:=30010) ' number for
charts menu bar

If HelpMenu Is Nothing Then
Set NewMenu = CommandBars(2).Controls _
.Add(Type:=msoControlPopup, Temporary:=True)
Else
Set NewMenu = CommandBars(2).Controls _
.Add(Type:=msoControlPopup, Before:=HelpMenu.Index,
Temporary:=True)
End If

NewMenu.Caption = "E&DCSM"


Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "&Controls"
.FaceId = 1845
.OnAction = "OpenControlsForm"
End With

Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = "C&hart Options"
.FaceId = 433
.OnAction = "OpenChartsForm1"
End With

End Sub
 
Top