VBE pop-up menu

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

Tom;

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

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


Regards,