Solved: Add an item for MACROS to the Excel menu bar

[brunces]

Friends,

Please, is it possible...?

Here we have the usual Excel menu bar:

File / Edit / View / Insert / Format / Tools / Data / Window / Help

When we press ALT+F8, a small window appears with all MACROS available for us to run. Is it possible to add an item to the menu bar which shows the same contents that are shown when we press ALT+F8?

The menu bar should be something like this:

File / Edit / View / Insert / Format / Tools / Data / Window / Help / Macros

Then when we press the item "Macros", it shows all macros available (just like the other itens from the menu bar) and when we click one of those macros, it is run!

(If we open a file which doesn't contain any macros, the item Macros can be hidden... Or not! It's up to you!)

Anybody knows how to do that?

Thanks for your attention, guys.

Hugs.

Bruno

[Bob Phillips]

Bruno,

Here is some code to do it

[VBA] Sub AddMenuItem()
Dim octl As CommandBarControl

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Macros").Delete
On Error GoTo 0

With Application.CommandBars("Worksheet Menu Bar")
Set octl = .Controls.Add(msoControlButton, , , , True)
octl.Caption = "Macros"
octl.OnAction = "MacrosDialog"
octl.Style = msoButtonCaption
End With

End Sub

Private Sub MacrosDialog()
SendKeys "%{F8}", True
End Sub

[/VBA]

[Zack Barresse]

Seems like it'd be easier to programmatically check for macros in the workbook .. but anyway. While xld's solution may work, it's not too efficient - or reliable - to use SendKeys. That should be a last resort. We can make use of the native dialog boxes...

[vba]Sub ShowMacroDialog()
Application.Dialogs(xlDialogRun).Show
End Sub[/vba]

[brunces]

Friends,

Sorry, I guess I didn't make myself clear. I'll try to explain it better...

Actually, I don't want to open/show the Macros Dialog Box. I just want all availables macros "inside" the new menu.

For example...

Inside the menu "File" we have: New / Open / Close / Save / Save As / etc...
Inside the menu "Edit" we have: Can't Undo / Can't Repeat / Cut / Copy / Paste / etc...
And so on...

So, I'd like to know if it's possible to add something like that...

Inside the new option "Macros" we would have: Macro1 / Macro2 / Macro3 / Macro4 / etc... (all macros available)

Then, if we want to run Macro3, for example, we click on "Macros" and then click on "Macro3" (and Excel runs Macro3 right away).

The solutions posted here was a button (a shortcut) to call/show the Macros Dialog Box, but this is not what I really want. Sorry if I didn't express myself correctly.

So, is it possible to do that (show all available macros inside the menu)? If not, that's OK. I'll use what you taught me first, no problem.

Anyway, thanks everybody for the attention. I really appreciate it.

Hugs for all.

Bruno

[Zack Barresse]

Okay, you want to make your own menu. Check out J-Walk's helper here: http://j-walk.com/ss/excel/tips/tip53.htm

Also, if you like it and use it often enough, I have created a helper userform that I use to edit it constantly (because mine is an adapted add-in) on the fly: http://www.vbaexpress.com/forum/showthread.php?t=1369

[brunces]

Zack,

Yes, I want my own menu. But it's not a "fixed" menu with "fixed" macros. I want a menu which shows only the macros available for the opened files. If the opened files have no macros, the menu becomes empty (or even hidden).

I want a menu which shows the Macros Dialog Box contents. That's it!

Gonna check your links out now!

Thanks for your help, buddy.

Bruno

[Bob Phillips]

But it's not a "fixed" menu with "fixed" macros. I want a menu which shows only the macros available for the opened files. If the opened files have no macros, the menu becomes empty (or even hidden).

Bruno,

It seems that the run dialog items are not exposed to VBA, at least not as far as I can see.

I have been able to create a menu with all of the items that would appear in that lst, but I am struggling to run the associated macros at present.

I'll look at it again tomorrow at post back if I get anywhere.

[Zack Barresse]

xld, post what you have if you need help. I hesitated to make anything for this in lieu of time. Sounds interesting. Are you using Class events? You'd almost need to ..

[Bob Phillips]

xld, post what you have if you need help. I hesitated to make anything for this in lieu of time. Sounds interesting. Are you using Class events? You'd almost need to ..

Zack,

The problem arose in my case because I had the routine Test() in many workbooks, so in my original code, Test appeared 4 times. Easy to fix thought !, I would add the workbook name to the menu caption and to the OnAction clause. Unfortunately, it keeps saying that it cannot find the macro in this case.

I then tried to have a generic macro for each menu item, use Parameter to setup a run clause, and use Application.Run. That failed also, at which point it was time for bed.

I am not using application events, and would be interested to hear in what way you think they might solve the problem. What I am doing is to loop through every module in the VBA, strip out the Public Subs, and create menu items for those. As such, the code is pretty basic, it just doesn't work properly.

[Andy Pope]

FYI cross posted http://www.ozgrid.com/forum/showthread.php?t=33640

[Bob Phillips]

Briuno,

Cracked it. Don't know what the problem was yesterday, but it is woirking fine today.

The solution consists of two parts. The first part is a procedure that loops through every module in every open workbook and pulls out the Public subs with no arguments, taht is the ones you would see in the Run Dialog. This is that procedure

[VBA]
#Const inDebug = False
'---------------------------------------------------------------
Public Function RunMacros(RunTypesOnly As Boolean, _
Optional PublicOnly As Boolean = True)
'---------------------------------------------------------------
' Function: Builds an array of procedure names
' Synopsis: Loops through each code module of each open
' workbook, and:
' - checks if the module is Private
' - else loop all procedures in module
' - to check if Private
' - to check if a Sub
' - to check if any arguments
' - adds details to arrya for all valids
' Parameters: RunTypesOnly
' True - ignores Subs with parameters and
' Functions
' PublicOnly
' True - ignores Private modules and Private Subs
' and Functions
'---------------------------------------------------------------
Const COMPONENT_MODULE As Long = 1
Dim oCodeModule As Object, oComponent As Object
Dim oWb As Workbook
Dim fStart As Boolean, fPrivate As Boolean
Dim iStart As Long, iCurrent As Long
Dim cLines As Long, cProcs As Long
Dim iPosComment As Long, iPosPrivate As Long
Dim sProcName As String
Dim lProcKind As Long
Dim aryProcs

ReDim aryProcs(1 To 3, 1 To 1)

For Each oWb In Application.Workbooks
#If inDebug Then
Debug.Print oWb.Name
#End If

For Each oComponent In oWb.VBProject.VBComponents
#If inDebug Then
Debug.Print "___" & oComponent.Name
#End If

If oComponent.Type = COMPONENT_MODULE Then
With oComponent.CodeModule

For iCurrent = 1 To .CountOfDeclarationLines
iPosPrivate = InStr(1, .Lines(iCurrent, 1), "Option Private Module", vbTextCompare)
iPosComment = InStr(1, .Lines(iCurrent, 1), "'")
fPrivate = iPosPrivate > 0 And (iPosComment > iPosPrivate Or iPosComment = 0)
If fPrivate Then
Exit For
End If
Next iCurrent

If Not PublicOnly Or Not fPrivate Then

iStart = .CountOfDeclarationLines + 1

Do Until iStart >= .CountOfLines
'get the procedure name and count of line
'.ProcOfLine modifies ProcKind to type
sProcName = .ProcOfLine(iStart, lProcKind)
cLines = .ProcCountLines(sProcName, lProcKind)
#If inDebug Then
Debug.Print "______" & sProcName
#End If

iCurrent = iStart - 1
Do
iCurrent = iCurrent + 1
fStart = .Lines(iCurrent, 1) Like "*Sub *" Or _
.Lines(iCurrent, 1) Like "*Function *" Or _
.Lines(iCurrent, 1) Like "*Property *"
Loop Until fStart

'determine procedure type
If .Lines(iCurrent, 1) Like "*Sub *" Or _
.Lines(iCurrent, 1) Like "*Function *" Then

iPosPrivate = InStr(1, .Lines(iCurrent, 1), "Private", vbTextCompare)
If PublicOnly And iPosPrivate > 0 Then
'do nothing
Else
If RunTypesOnly Then
If InStr(1, .Lines(iCurrent, 1), "()") > 0 And _
InStr(1, .Lines(iCurrent, 1), "Sub ") > 0 Then
cProcs = cProcs + 1
ReDim Preserve aryProcs(1 To 3, 1 To cProcs)
aryProcs(1, cProcs) = oWb.Name
aryProcs(2, cProcs) = oComponent.Name
aryProcs(3, cProcs) = sProcName
End If
Else
cProcs = cProcs + 1
ReDim Preserve aryProcs(1 To 3, 1 To cProcs)
aryProcs(3, cProcs) = sProcName
aryProcs(1, cProcs) = oWb.Name
aryProcs(2, cProcs) = oComponent.Name
aryProcs(3, cProcs) = sProcName
End If
End If

End If '.Lines(iCurrent, 1)

'onto the next procedure
iStart = iStart + _
.ProcCountLines(sProcName, lProcKind)
Loop

End If 'Not fPrivate

End With 'oComponent

End If 'oComponent.Type
Next oComponent
Next oWb

RunMacros = aryProcs

End Function

[/VBA]

Next we have a procedure to create the menu, which calls the above

[VBA]
Sub MacrosMenu()
Dim oCtl As CommandBarControl
Dim oCtlSub As CommandBarControl
Dim aryProcs
Dim i As Long

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Macros").Delete
On Error Resume Next

aryProcs = RunMacros(RunTypesOnly:=True, PublicOnly:=True)

With Application.CommandBars("Worksheet Menu Bar")
Set oCtl = .Controls.Add(Type:=msoControlPopup, temporary:=True)

With oCtl
.Caption = "Macros"

For i = LBound(aryProcs, 2) To UBound(aryProcs, 2)
Set oCtlSub = .Controls.Add(Type:=msoControlButton)
With oCtlSub
.Caption = "" & aryProcs(1, i) & "!" & _
aryProcs(2, i) & "." & aryProcs(3, i)
.Style = msoButtonCaption
.OnAction = "" & aryProcs(1, i) & "!" & aryProcs(3, i)
'.OnAction = "RunMacro"
End With
Next i
End With

End With
End Sub
[/VBA]

What it does not do is to update the menu should a new macro be created or one deleted. I have an idea on this and will post if and when it works.

[brunces]

xld,

I just wrote a message to post here and when I was about to post it, I realized that you had alerady put the codes you created. I'm gonna post it anyway (right below) because I believe it can be important. I hope you don't mind.

I've made a test with your codes and, man... That's exactly it!!! You're good!

As you said, now it's just a matter of improvements (like the refreshing process). Unfortunately, I'm a beginner and don't know much about VBA to help you. I wish I could. You're good, man!

I'll keep on waiting for you HERE if you have improvements to show us.

Thank you very, very much for your attention, buddy.

Hugs,

Bruno

-----

Andy,

Sorry for the cross-posts. It's because I believe the more sources we have, the more different "thoughts" we get.

Different people access different forums and, consequently, bring us excellent different solutions. Some of them wiser than others, smoother, etc.

I know it's wrong and goes against the rules of the Forums, but that's my point of view.

But I apologize for that, anyway.

Hugs,

Bruno

-----
(Here's what I've written before xld posted his codes.)


Friends,

I see you are also interested on this new menu for macros and I feel happy for that.

I've been thinking about something that I'm sure I didn't mention before...

I don't even know if it's possible, but I'll tell you anyway.

I'm not sure about what you guys are planning to do, but when I thought of this menu for the first time, I thouhgt about a .XLA file which would be "loaded" at Excel startup. I guess this is called "Add-ins", right? (My Excel is in Portuguese, for I'm Brazilian, so I don't know the exact translation for that. Sorry!)

This way, even if no files were opened, the menu Macros would already be available, empty or hidden (I guess it's better empty than hidden, I don't know).

Then, when we open any file, the menu Macros "loads" all macros available for that file. If we open another file, the menu Macros is refreshed and "loads" again all macros available for both opened file. And the same process goes on every time we open a different file.

If we open a file which has no macros, the menu Macros loads nothing and remains empty (or hidden).

When we close a file, the menu Macros is refreshed and loads only the macros available for the current opened files.

Wow! Too much text here. (lol) It's kinda confused, but I'm sure you guys got the logic!

As I said, I'm not sure if all of this is possible. I thought about a .XLA file because I think it would be easier than putting the same code into each and every .XLS file. But, maybe I'm wrong, I don't know.

Well, guys. That's it.

Bruno

[Bob Phillips]

I thouhgt about a .XLA file which would be "loaded" at Excel startup. I guess this is called "Add-ins", right?

Bruno,

I think that is a sensible route. What I will do is create an add-in when I crack the refresh issue, and post it here, You can then load at leisure.

PS Apologies for typing Briuno last time

[Bob Phillips]

Different people access different forums and, consequently, bring us excellent different solutions. Some of them wiser than others, smoother, etc.

For instance, I never go anywhere near OzGrid!

[Bob Phillips]

Quick update.

I have created an add-in and it loads nicely, updates nicely when a new workbook is opened or an existing one closed, but .... there is always a but.

When closing Excel, it takes three clicks oh the X button to close down. Must be something I am doing with events, but not found yet.

[brunces]

I have created an add-in and it loads nicely, updates nicely when a new workbook is opened or an existing one closed, but .... there is always a but.

xld,

Don't worry, buddy! The most difficult you have already made! (lol)

Now you're pretty close to your "grand final release"!

We're anxious!

Bruno

[Zack Barresse]

.. when I crack the refresh issue..

This is what I was suggesting using Class Modules for. Not necessarily needed, but to catch application level events, it could be employed.

Post your code. Not sure how you are closing your add-in. It also depends on how you are creating your menu. We'd need to see the code though.

[mdmackillop]

Hi Bob,
Just came acrosss this little gem. Did you ever complete the final tweak?
Regards
Malcolm

[Bob Phillips]

Blimey Malcolm, this was 3 years ago, I had forgotten all about it.

I will look back on my machine of the time and see where I got to.

[bdsii]

I would like to see it as well if you can find it XLD. Thanks !