Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    I have a dialog box with buttons to trigger a number of subroutines, and would like to add a feature that enables the user to click a check box next to some of the buttons to automatically run all subroutines associated with the checked buttons.

    I can make this happen by using conditional statements to test each check box and run the associate subroutine if requested. However, I would like to be able to loop through all objects in the dialog box with a "chk" prefix, peel off the name following the prefix (eg get "Report1" from "chkReport1" using the MID() function) and run the associated button_click subroutine (eg "cmdReport1_Click").

    But I can't find a way to use a variable text string to call a subroutine. Using the example above, I could use the following conditional:

    If chkReport1 then call cmdReport1_Click

    and repeat this for each check box/button combination.

    Is there any way I can loop it instead?

    For Each c in Me.Controls
    If Left(c.Name, 3) = "chk" then
    strSRName = "cmd" & Mid(c.Name, 4) & "_Click"
    'Then something here to call the subroutine called strSRName
    End If
    Next c

    I realise there is more than one way to select just the check boxes; my real interest is in the equivalent of a call function that uses a variable procedure name.

    Thanks

    Aaaaarghhhhh! Where's my signature gone???!!! And my hand-spaced indents?!??!

    AliFrog

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Hmmm... my signature IS there , just didn't preview.

    Pity about the indents, though .

  3. #3
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Not sure if your context is VB or VBA (or if it makes a difference), but in VBA, you can use
    [pre]Application.Run strSRName[/pre]

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    [quote name='st3333ve' post='773377' date='02-May-2009 14:45']Not sure if your context is VB or VBA (or if it makes a difference), but in VBA, you can use
    [pre]Application.Run strSRName[/pre][/quote]

    So simple - thank you. Just need to work on a couple of things, as it doesn't seem to like calling private subroutines this way, but no real biggie.

    (Shows it's while since I posted, but I'm finding this new interface a bit odd!)

    Cheers

    AliFrog

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    It's possible that Application.Run won't work to call an event procedure in a Private module.

    Another way to do this might be to move the code that you've now got running inside (for example) cmdReport1_Click, out into its own procedure (which could be placed in a standard module).

    You could name that new procedure (for example) "Handle_cmdReport1_Click" and you could construct the call to it by parsing the control name like you're now doing. You should then be able to use a statement like "Call strSRName" in your loop to run the macro (although have to admit, haven't tested it!). If calling it that way doesn't work, you should alternatively be able to use "Application.Run strSRName" to call the new procedure, since it would now be calling a standard procedure in a standard module.

    And in either case, in the cmdReport1_Click procedure, you can replace the code you just moved out into its own subprocedure, with a call to that subprocedure (i.e. "Handle_cmdReport1_Click").

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='alifrog' post='773375' date='02-May-2009 03:48']Pity about the indents, though .[/quote]
    That's what the CODE and CODEBOX tags are for (CODEBOX has scrollbars and is for longer pieces of sample code).

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='alifrog' post='773375' date='01-May-2009 22:48']Hmmm... my signature IS there , just didn't preview.

    Pity about the indents, though .[/quote]
    Further to Stuart's post, take note of the thread which starts here.
    Regards
    Don

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you alter the commandbutton click events so they are not private (either replace Private with Public, or just delete Private) then you can use CallByName:
    Code:
    For Each c in Me.Controls 
    If Left(c.Name, 3) = "chk" then 
    strSRName = "cmd" & Mid(c.Name, 4) & "_Click" 
    callbyname, me, strSRName, vbMethod 
    End If 
    Next c
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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