VBE pop-up menu

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
Hi everybody;

After adding the reference "Microsoft Visual Basic for Applications Extensibility 5.3" to my project, I created the following ModuleNames procedure, to add the names of the existing sheet and general modules in the active workbook to the right-click of the mouse to be used in VBE window.

The module names are added in the pop-up menu but, the "OnAction" part of the procedure is not working.

According to the lines below, when any of the module names listed in the pop-up menu is clicked, the procedure named "Test" should run and a MsgBox should appear on the screen. But, it's not.

Do you have an idea with that ? Or, my brain is confused ??

Also; if that is possible, how I can return the clicked menu caption ? (Related with the new menus added with the procedure named ModuleNames)

(Running XL9 on Win98)

The related code is below;

Code:
Sub ModuleNames()
Dim cb As CommandBar
Set cb = Application.VBE.CommandBars("Code Window")
With cb
.Reset
For Each ExistingModul In ThisWorkbook.VBProject.VBComponents
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Test"
.FaceId = 36
.Caption = ExistingModul.Name
End With
Set cb = Nothing
Next
End With
End Sub

Sub Test()
MsgBox "Hello !"
End Sub

Sub Auto_Close()
Application.VBE.CommandBars("Code Window").Reset
End Sub

Regards,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Using commandbar controls in the VBIDE is not the same as Office. Do the following and visit this website for more info...

http://www.cpearson.com/excel/vbemenus.htm

In a new workbook with three new worksheets.
All names default(Sheet1,2,3)<pre>
In Module1

Option Explicit

Dim MnuEvt As VBECmdHandler
Dim CmdItem As CommandBarControl
Dim EvtHandlers As New Collection


Sub ModuleNames()
'http://www.cpearson.com/excel/vbemenus.htm
Dim cb As CommandBar
Dim ExistingModule

While EvtHandlers.Count > 0
EvtHandlers.Remove 1
Wend

With Application.VBE.CommandBars("Code Window")
.Reset
For Each ExistingModule In ThisWorkbook.VBProject.VBComponents
Set CmdItem = .Controls.Add
CmdItem.FaceId = 36
CmdItem.Caption = ExistingModule.Name
Set MnuEvt = New VBECmdHandler
Set MnuEvt.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdItem)
EvtHandlers.Add MnuEvt
Next
End With
Set cb = Nothing
Set CmdItem = Nothing

End Sub

Sub Auto_Close()
Application.VBE.CommandBars("Code Window").Reset
End Sub

in a class module named "VBECmdHandler"

Option Explicit

Public WithEvents EvtHandler As VBIDE.CommandBarEvents


Private Sub EvtHandler_Click(ByVal CommandBarControl As Object, Handled As Boolean, CancelDefault As Boolean)

On Error Resume Next

'you could also use a tag or other properties here
Select Case CommandBarControl.Caption
Case "Sheet1"
MsgBox "You clicked Sheet1. Call a procedure here."
Case "Sheet2"
MsgBox "You clicked Sheet2. Call a procedure here."
Case "Sheet3"
MsgBox "You clicked Sheet3. Call a procedure here."
Case "ThisWorkbook"
MsgBox "You clicked ThisWorkbook. Call a procedure here."
Case "Module1"
MsgBox "You clicked Module1. Call a procedure here."
Case "VBECmdHandler"
MsgBox "You clicked VBECmdHandler. Call a procedure here."
End Select

Handled = True
CancelDefault = True

End Sub</pre>

Study the code and visit the link above for a better understanding...

Tom



_________________
Using Office 8, 9, & 10 on Windows 2000
This message was edited by TsTom on 2003-02-21 01:11
 
Upvote 0
Tom;

Thanks so much. I guess, I'll be able to do what I need now.

Thank you very very much, again.....


Regards,
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,697
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top