Consulting

Results 1 to 14 of 14

Thread: Application.Caller return value

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Application.Caller return value

    I'm trying to have A Calendar Userform return the name of the button that called it, so that it can put the date in a different cell or TextBox, depending on which form or button it was called from, and if called from a UserForm, return the name of the UserForm.
    I call that Calendar from different Macros from different locations.
    Anyone know if Application.Caller can be used to do something like this.
    If so, how would I code it to return the name of the caller as either a string or a variable.

    Thanks

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The UserForm should have a Public Variable. ie Public "CalledBy" As String
    You will need an intermediary sub to determine the caller and to call the calendar.
    The buttons should call the intermediary sub
    Sub Intermediary()
       Load UserFormCalendar
       UserFormCalendar.CalledBy = Application.Caller
       UserFormCalendar.Show
    End Sub
    Edit terminology to suit
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Application.Caller return value

    Hi:
    Thanks for the input.
    Still a little lost, but you've pointed me in some direction to investigate.
    Thanks

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Application.Caller will not work if you call the code from another form.
    Be as you wish to seem

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Hi Aflatoon:

    Thanks. I figured that bit out. & That's exactly what I was hoping was possible.
    I did a kludge work around.
    Put a hidden field into both userforms and stored the calling Userform button value there.

    I appreciate all of your input.

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    A Userform control can have a _Click event.
    In that case the 'calling control' is evident.

    Private Sub Commandbutton1_Click()
      msgBox commandbutton1.name
    End Sub

  7. #7
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    snb:

    On the Calendar, I was trying to find out what was calling the Calendar form.
    The Calendar will return the date value to entirely different locations based on what called it.
    That's what I was investigating.

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    No rocket science.

    Alternative Code:

    Private Sub CommandButton1_Click()
        M_snb 1
    End Sub
    
    Private Sub CommandButton2_Click()
        M_snb 2
    End Sub
    
    Sub M_snb(y)
        UserForm1.Caption = OLEObjects(y).Name
        UserForm1.Show
    End Sub
    Attached Files Attached Files

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by simora View Post
    I'm trying to have A Calendar Userform return the name of the button that called it, so that it can put the date in a different cell or TextBox, depending on which form or button it was called from, and if called from a UserForm, return the name of the UserForm.
    I call that Calendar from different Macros from different locations.
    Anyone know if Application.Caller can be used to do something like this.
    If so, how would I code it to return the name of the caller as either a string or a variable.

    Thanks


    https://trevoreyre.com/portfolio/excel-datepicker/

    Why re-invent the wheel?

    Trevor Eyre has a free and very flexible date picker that I've found works very well

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10

  11. #11
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    snb

    This is what I did.

    I send a value from any button calling the form to a hidden field on the UserForm1.
    The form will return the date to a location based on what value the hidden field has.
    Works like a charm for my purposes.
    Anytime I call the UserForm1.Calendar, I just send it some specific button name or whatever, and it will return the date to the location that I desire.
    'Place the date from the calendar into a location based on where its called from.

    If UserForm1.TextBox1.Value = "1Form" Then
        PForm.TextBox5.Value = UserForm1.Calendar1.Value    
        Else
        MultiCheckForm.TextBox20.Value = UserForm1.Calendar1.Value
    End If
    
        'Dismiss the userform
        Unload Me

  12. #12
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    Paul_Hossler
    Hi Thanks:

    But I am NOT trying to create a Calendar form.
    I already have one that I'm using.
    My question was about having different buttons call it, and getting it to return the date to a specific location based on the caller.

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In each of the Button_Click subs that call the Userform, store the .Caption (or any identifying data) in a Public variable

    Use that after you select a date from the userform to put the answer where you want
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14

Posting Permissions

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