Macro help needed

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Macro help needed

  1. #1
    New Member
    Join Date
    Jul 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    I have a spreadsheet with 4 buttons (with macros 'attached') - say Macro1 in b1, Macro2 in b2, Macro3 in b3, Macro4 in b4...and I want to add a 5th button in b5 that randomly chooses between the 4 buttons above and makes them start.
    I am an absolute novice in this regard...could someone please help.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Newark, Delaware
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    Since it doesn't seem like you are going to get a solution, I will post one. (I pulled some some code from Chip Pearson about how to get the macro names.)

    Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects. If you are using Excel97, this library will appear in the References list without a version number: "Microsoft Visual Basic For Applications Extensibility". If you are using Excel 2000 or 2002, it will appear with a version number: "Microsoft Visual Basic For Applications Extensibility 5.3". It is very important that you reference the proper library. If you reference the wrong library, you will receive "Type Mismatch" errors. If you don't reference the extensibility library at all, you will receive "User Defined Type Not Defined Error".
    Place your macros in module1 (i.e. Macro1, Macro2, Macro3, Macro4 from your post). Insert a new module and place the following code:
    Sub RndChooseMacro()

    Dim VBCodeMod As CodeModule
    Dim StartLine As Long
    Dim Macros As String
    Dim ProcName As String
    Dim x As Variant
    Dim RndMacro As Long

    Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
    With VBCodeMod
    StartLine = .CountOfDeclarationLines + 1
    Do Until StartLine >= .CountOfLines
    Macros = Macros & " " & .ProcOfLine(StartLine, vbext_pk_Proc)
    StartLine = StartLine + _
    .ProcCountLines(.ProcOfLine(StartLine, _
    vbext_pk_Proc), vbext_pk_Proc)
    End With

    Macros = Application.Trim(Macros)
    x = Split(Macros, " ")
    RndMacro = Int((UBound(x) * Rnd) + 1)

    Run x(RndMacro)

    End Sub

    Assign button 5 to RndChooseMacro.
    Best regards,
    Allan Chara

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