Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro for checkboxes (2000)

    I have a userform with 15 checkboxes. Each checkbox indicates whether a macro should run or not. The User can select any combination of checkboxes and then click on an "OK" button (also in the userform).

    Can anyone advise a simple way to write the code to fire off the selected macros?

    Eg If only the Checkbox1 value = true, then only run Macro 1. If say, Checkbox1 and Checkbox 3 are selected, then fire off macro 1 and macro 3 only etc

    I'm sure this can be done with simple code, but I cant seem to get my head round it!

    Many thanks

    Rob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for checkboxes (2000)

    If your check boxes are really named CheckBox1, CheckBox2 etc., and the corresponding macros are really named Macro1, Macro2 etc., you can do it like this:

    Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To 15
    If Me.Controls("CheckBox" & i) Then
    Application.Run "Macro" & i
    End If
    Next i
    End Sub

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro for checkboxes (2000)

    Hello Hans,

    The Checkboxes and Macros have uniform names, so this code works perfectly. Thank you

    I had tried a For/Next loop, but couldn't get it to work. You used the "Me" keyword. Please could you explain what this keyword does. Can't understand how it works and can't find it in the "Help" either.

    Many thanks for your help

    Regards

    Rob

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro for checkboxes (2000)

    ME is a property of the current object on which your code is running. It acts like an object, so of stead of calling the objects name, you can use ME.
    Works on forms in word and access and excel. Shortcut for refering to the current object!
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro for checkboxes (2000)

    Me refers to the object that is running the code. In this case, it is the userform; if you have event code for a worksheet, it is the worksheet object, etc.

    (Technically speaking, a userform, a worksheet and a workbook are all examples of a class. In the class module, Me refers to the current instance of the class. Don't worry if this doesn't mean much to you.)

Posting Permissions

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