+ Reply to Thread
Results 1 to 19 of 19

Call userform control click event indirectly

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    anamosa, iowa
    MS-Off Ver
    Excel 2007
    Posts
    34

    Call userform control click event indirectly

    I have a user form with a list box. as I click on different labels and command buttons (each label and command button selects a different page of a multipage control), the name of the label or command button is added to the list box thus creating a log of previous control clicks. I also have 2 command buttons on the same user form. I call them forward and backward buttons. so when I click the backward button, it will choose the previous control name from the list box values and call its click event. Example: current selected list box value is Label325, I click the backward button and it calls Label325's click event. I've tried: Call[Me.listbox2.value & "_Click"], this doesn't do anything. if I were to type Call[Label325_Click] it calls the Click event perfectly, but referencing the control indirectly does not. what am I doing wrong?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Call userform control click event indirectly

    Try the Application.Run method.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-07-2013
    Location
    anamosa, iowa
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Call userform control click event indirectly

    I've tried Application.run(Me.ListBox2.value & "_Click"). It says, "Cannot run the macro 'Label325_Click'. The macro may not be available in this workbook or all macros may be disabled."

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Call userform control click event indirectly

    command buttons are easy because you can use the .Value property to fire the click event. Not so fore Labels.

    You would need to move the contents of the event code to separate routine in a standard code module. Then you can implement shg's suggestion of using the run method
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    Why not? - though I've probably missed something

    Please Login or Register  to view this content.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Call userform control click event indirectly

    Thank for jumping in, Andy, Kyle, and nice suggestions. Ryan, I leave you in the hands of two smarter guys.

  7. #7
    Registered User
    Join Date
    03-07-2013
    Location
    anamosa, iowa
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Call userform control click event indirectly

    Tried CallByName, got "Object doesn't support this property or method"

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    You didn't make the sub public:
    Please Login or Register  to view this content.
    Thanks shg, but certainly not true not in my case anyway!

  9. #9
    Registered User
    Join Date
    03-07-2013
    Location
    anamosa, iowa
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Call userform control click event indirectly

    Yes! This worked! I didn't notice the change from Private to Public in your first reply. Thanks a million!

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Call userform control click event indirectly

    Kyle, that would require that you use the default instance of the userform without some additional qualification, correct?

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    No, it wouldn't unless I've misunderstood you, since you pass a reference to the object in as a param quick test:
    Please Login or Register  to view this content.
    Form:
    Please Login or Register  to view this content.

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Call userform control click event indirectly

    shg, your too modest.

    Here is an example using both approaches.
    Attached Files Attached Files

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    That's a nifty way of increasing encapsulation Andy

  14. #14
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Call userform control click event indirectly

    @ Kyle: Ah, the CallByName code is in the form. I missed the Me.

    @ Andy, thank you for the examples.

    Still not entirely grokking, though. Why is it necessary to make the subs public if the code is in the same module? What's the downside to making them public?

  15. #15
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    There isn't really a downside in this case, but generally it's better encapsulation to keep as much private as possible and only expose the bare minimum of methods/props/functions required to perform the role of the object, this solution is a bit of a hack.

    There should be no real need to expose the Label_Click since all its interaction is really done within the form, it's not nice to have outside influences fiddling with it

    CallbyName will only work on public methods since you can't call private methods from outside the object. CallByName is called from outside the object and thus has no access to private members
    Last edited by Kyle123; 03-26-2015 at 11:27 AM.

  16. #16
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Call userform control click event indirectly

    CallbyName will only work on public methods since you can't call private methods from outside the object.
    That makes sense, I think ...

    I've used CallByName maybe twice in my life. I need to write that down someplace.

    Thanks, Kyle.

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Call userform control click event indirectly

    Is it me or is the help on CallByName for VB6 rather than VBA?

  18. #18
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,219

    Re: Call userform control click event indirectly

    @Andy I don't know honestly, never did VB6.

    I think CallbyName is one of those obscure functions that you can never think of a use case for until you actually need it; though admittedly I reckon I've used it about twice as well

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,249

    Re: Call userform control click event indirectly

    It's certainly obscure.
    The help suggests you can pass the control as the object, but the Form_Load event and Move method on a button suggests it's not VBA

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform multipage control - exit event not firing or event order
    By jane serky in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2013, 10:23 AM
  2. [SOLVED] call a function on control click event
    By tkraju via OfficeKB.com in forum Excel General
    Replies: 4
    Last Post: 08-23-2006, 12:55 AM
  3. [SOLVED] MouseMove & Click event over an image control
    By furbiuzzu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 04:50 AM
  4. userform label double-click goes to click event
    By John Paul Fullerton in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2006, 01:00 PM
  5. Why does the click event get control?
    By Lee Hunter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1