Add Menu to a VBA Form

[Guest]

How can I add a popup menu to a VBA Form?

 

[papou]

Hello Rajesh
Yes, here's a sample code:
'** Userform ****
Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then
DisplayMenu
End If
End Sub
Private Sub UserForm_Terminate()
DeleteMenu
End Sub
'** Module ****
Const MenuName As String = "USFMenu"
Sub DisplayMenu()
DeleteMenu
Dim cb As CommandBar
Set cb = CommandBars.Add(MenuName, msoBarPopup, False, True)
With cb
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro1"
.FaceId = 71
.Caption = "Menu 1"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro2"
.FaceId = 72
.Caption = "Menu 2"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro3"
.FaceId = 73
.Caption = "Menu 3"
End With
End With
Application.CommandBars(MenuName).ShowPopup
End Sub
Sub DeleteMenu()
On Error Resume Next
CommandBars(MenuName).Delete
On Error GoTo 0
End Sub
Sub Macro1()
MsgBox "Macro 1 running"
End Sub
Sub Macro2()
MsgBox "Macro 2 running"
End Sub
Sub Macro3()
MsgBox "Macro 3 running"
End Sub

HTH
Cordially
Pascal

 

[Guest]

Thanks Very much Pascal. Great Job!!!

Hello Rajesh
Yes, here's a sample code:
'** Userform ****
Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then
DisplayMenu
End If
End Sub
Private Sub UserForm_Terminate()
DeleteMenu
End Sub
'** Module ****
Const MenuName As String = "USFMenu"
Sub DisplayMenu()
DeleteMenu
Dim cb As CommandBar
Set cb = CommandBars.Add(MenuName, msoBarPopup, False, True)
With cb
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro1"
.FaceId = 71
.Caption = "Menu 1"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro2"
.FaceId = 72
.Caption = "Menu 2"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro3"
.FaceId = 73
.Caption = "Menu 3"
End With
End With
Application.CommandBars(MenuName).ShowPopup
End Sub
Sub DeleteMenu()
On Error Resume Next
CommandBars(MenuName).Delete
On Error GoTo 0
End Sub
Sub Macro1()
MsgBox "Macro 1 running"
End Sub
Sub Macro2()
MsgBox "Macro 2 running"
End Sub
Sub Macro3()
MsgBox "Macro 3 running"
End Sub

HTH
Cordially
Pascal

 

[hce]

hi pascal

the code u did was fantastic... never knew this was possible... just
question here... how can i find out what number will show whic
faceid...? or is it possible to show the faceid as a specified pictur
or specified word like "Country"???

Cheer

 

[Jim Rech]

how can i find out what number will show which faceid...?

You might grab BtnFaces.zip from http://www.bmsltd.ie/MVP/Default.htm

 

[Bob Phillips]

Try John Walkenbach's FaceId add-in

http://www.j-walk.com/ss/excel/tips/tip67.htm

 

[hce]

hi jim & bob

thanks for your help... so there's no way we can specify our our image
or caption...???

cheer

 

[Bob Phillips]

Yes you can. Create a picture and insert it into a worksheet somewhere
(Insert>Picture>From File), and name them. Then to add them to the toolbar,
use

cbTable.Shapes(shapename).CopyPicture
cbCtl.PasteFace

where cbTable is the codename of the sheet containing the picture, shapename
is the name of the shape, such as 'Picture 30', and cbCtl is an object
variable for the control being added.

 

[hce]

hi bob

thanks for your help but i tried to modify pascal's code but was no
able to get your code working together...

first i wasn't able to change the picture's name so i used the defaul
"Picture 1"... second when i put the picture name where you specified
i must take out the space if not there would be an error... i trie
another way and that is to put "Picture 1" like this and it was ok..
lastly, i didn't manage to get it work though i tried various ways...

my vb is not that good so maybe that's y i wasn't able to get it t
work... by the way, is it possible to use words... such as "Country"??


cheer