Macro Buttons

[Igor]

Hi guys.

I am sorry for bothering you all, but I have a question I cannot seem to be able to solve.

Is there any way to asign, selected macros to a button?

I have over 40 Macros in a spreadsheet, and therefore 40 Buttons.

Is there any way I can make several drop down buttons (or otherwise) that will have the ability to have selected macros as options within the drop down?

For example, of the 40 Macros, 10 are for Suppliers, and 15 are for Customers.

So I would like a button that can list for me the 10 Supplier macros, without having all the other macros within the list.

Is there a way to do this?

Any help is really appreciated.


P.S. Are there any good online VBA courses that anyone knows of?

[Jack in the UK]

Cources

Dave Hawley on http://www.ozgrid.com does just this, not got a clue about prices thou, he post ion this site check the web page its cool stuff, loads of excel goodies, Dave has online VBA also and from what i have hurd from 2 guys i have communicated with they are super good, but ive not seen, check Dave site and his work, you decide.

BTW stick on this site wonderful reference, also everyone forget Bill (MrExcel and Juan) are top guys check this site tips and ideas are littered all over the front of this site Bill put them there for us all.

Also keep checking change all the time.

[KniteMare]

You could do this by calling a userform from a button and placing your various buttons on the userform.

I have a rather complex scheduling program and I use a custom menu for this process in place of buttons or userforms. It is created upon open or activate and deleted upon deactivate or close

I did not post the code here as I gather from your note that you need more Excel VBA training. (The procedures are somewhat complex)

Have you tried the online help that comes with the VBE you just put the mouse ofer a key work and hit F1 all the related info comes up and you can branch off to your hearts content.

There are like a zillion books out there on Excel and VBA go to the book store and see if any fit you needs. remember to bring fifty bucks as they are not cheap. You will find that over time you'll buy more!!!
Good luck
Yours in EXCELent Frustration
KniteMare

[Derek]

Hi

You might try using an input box as this is fairly simple. This little snippet calls an input box which lists macros numbered 1, 2 and 3. The number entered in the input box determines which macro runs.

Answer = InputBox("INSERT No OF THE MACRO YOU WANT TO RUN" & vbCrLf & "1. Color cell green" & vbCrLf & "2. Color cell red" & vbCrLf & "3. remove cell color")
If Answer = 1 Then Application.Run "Book12!Button2_Click"
If Answer = 2 Then Application.Run "Book12!Button3_Click"
If Answer = 3 Then Application.Run "Book12!Button4_Click"
End Sub

Hope this gives you some ideas

Regards
Derek

Why don't you just do a custom toolbar (easy, look up in help) and a drop down menu for "suppliers" with the macros below that, a drop down menu for "vendors" or whatever, with the macros for that below it, etc.

[Ivan F Moala]

Hi Igor

Another way:
Suggest you change the original code to this

Private Sub ComboBox1_DropButt*******()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp

ComboBox1.Clear

For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Name = "Module1" Then
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
ComboBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Set VBCodeMod = Nothing
Next VBComp

End Sub


Then Insert a new Module and move all relevant code to this Module

eg In Module1 have all your code for suppliers

In another module have all code for Customers

etc.

Then just change the reference to the module in the code above......

If you like I can sen example


Ivan

[Igor]

Ok thanks, I'll send you what I have so far.

Thanks for all your help, its been fantastic.

[Ivan F Moala]

On 2002-03-14 18:25, Igor wrote:
Ok thanks, I'll send you what I have so far.

Thanks for all your help, its been fantastic.

Hi Igor WBk recieved and amended.....
on it's way........


HTH

Ivan