Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA-Passing a variable between modules

  1. #1
    Guest

    Default

    I've created a userform in ExcelXP-VBA to enter data. There is some code in the userform module to control some behavior. I need to pass the result of a MsgBox to the main worksheet module for further processing. The following are the key code snippets:

    Userform Module

    Private Sub
    WriteDataReponse = MsgBox("blah blah", vbYesNo)
    UserForm1.hide
    End Sub

    Worksheet Module
    Public WriteDataResponse
    Private Sub
    WriteResponseData = vbNo
    Userform1.Show
    If WriteDataResponse = vbYes Then
    WriteData1 = Userform1.TextBox1.Text
    WriteData2 = Userform1.TextBox2.Text
    End If
    End Sub

    I thought a Public variable would be avaiable in all modules and its value preserved. I've set a couple of breakpoints and monitored WriteResponseData in the Locals window.

    WriteResponseData sets to vbYes (value 6) in the Userform module as it should. When I check the Locals window in the Worksheet module, WriteResponseData is still set to vbNo (value 7). It stays this way when the Userform subroutine ends and control is returned to the worksheet module.

    I don't get it!!

    Help!

    TIA,

    ...Eric

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you putting it in a Worksheet module ? or in a regular module ?

    Try putting it in a standar module.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Guest

    Default

    I don't think I understand the difference. The modules I seem to have available are:

    VBAProject.Sheet1
    VBAProject.Userform1

    Is there something else I should be doing? I've got the nagging feeling I'm doing/not doing something fairly stupid for this to be fighting me so much!!

    TIA,

    ...Eric

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, you should put it in a Module1, not in the Sheet's module, why ? because Sheet's module are Private Modules. Standard modules aren't (Unless you make them)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Guest

    Default

    YES! YES!

    Thank you sooooo much!!

    ...Eric

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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