Macro Buttons
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro Buttons

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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.

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thanks for all your help, its been fantastic.

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com