+ Reply to Thread
Results 1 to 4 of 4

Trouble passing variable to Userform in Personal.xlsb

  1. #1
    Registered User
    Join Date
    08-27-2013
    Location
    UK
    MS-Off Ver
    Excel 20010
    Posts
    25

    Trouble passing variable to Userform in Personal.xlsb

    I am stumped again by what is probably a simple problem.

    I'm trying to get a variable onto a userform in Personal.xlsb which triggers when the user clicks on a hyperlink that's created by another macro.

    I've declared a public variable:

    Please Login or Register  to view this content.
    And then when the hyperlink is clicked this code runs. The MsgBox shows the variable correctly at this point:

    Please Login or Register  to view this content.
    The sub "showmyform" is as follows, and again the MsgBox shows the variable correctly here:

    Please Login or Register  to view this content.
    Then the following in UserForm1. The MsgBox here is blank.

    Please Login or Register  to view this content.
    If anyone has any ideas I'd really appreciate it.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Trouble passing variable to Userform in Personal.xlsb

    Hi davepoth,

    I haven't tested anything, but I think your problem is in:
    Please Login or Register  to view this content.
    The item in red is a Formal Parameter and has no relation to the global you define in personal.xlsb. As a 'formal parameter' it is local in scope in Sub showmyform(), even though it has the same name as your global variable. I think a small change in the above module would fix your problem:
    Please Login or Register  to view this content.
    If you are still having problems, please upload a sample workbook, so that I or someone else can more easily test a solution. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Lewis

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Trouble passing variable to Userform in Personal.xlsb

    Couldn't you put the value in the textbox on the userform directly?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Trouble passing variable to Userform in Personal.xlsb

    I tested the original code, my code, and Norie's code in Excel 2003, substituting PERSONAL.XLS for PERSONAL.XLSB with the following results:

    Original Code:
    UserForm Module Global DelNoBoxVar Value: BLANK
    UserForm TextBox Display: BLANK

    Lewis' Code:
    UserForm Module Global DelNoBoxVar Value: Text Value in the Hyperlink Cell
    UserForm TextBox Display: Text Value in the Hyperlink Cell

    Norie's Code:
    UserForm Module Global DelNoBoxVar Value: BLANK
    UserForm TextBox Display: Text Value in the Hyperlink Cell

    Conclusion:
    a. If you need the value DelNoBoxVar in the UserForm code somewhere other than the initial value of UserForm1.DelNoBox, use Lewis' code.
    b. If you only need the value DelNoBoxVar in the initial value of UserForm1.DelNoBox:
    (1) use Norie's code, and
    (2) Delete Global variable DelNoBoxVar
    (3) The existing code in UserForm_Initialize() is not needed and should be deleted.

    Lewis

+ 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] Passing a variable to a userform
    By OldMan62 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-08-2014, 12:58 PM
  2. Userform not passing through 1 particular public variable
    By newbvba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2014, 09:59 PM
  3. Trouble passing variable to another function
    By Jasmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-28-2011, 10:15 AM
  4. passing variable from userform
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-31-2010, 08:22 PM
  5. Passing Variable from Sheet to Userform
    By gti_jobert in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2006, 05:38 AM

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