Passing variable sheet module to user form in VBA

Question

Firstly, this is my first ever post on stack overflow, so I hope I'm following the correct procedure. I have browsed through dozens of posts on this and other websites but I can't seem to extrapolate solutions for similar cases to mine. I have also tried using debug lines, but I can't pinpoint the problem, probably due to the fact that I'm new to VBA. Here's what I have in short, I hope you can help:

A commandbutton on Sheet1 that raises a Yes/No/Cancel msgbox, I want a mechanism to remember this choice in the UserForms and Modules that follow, so I declared boolNieuweOpdrachtgever as a Public variable, however, in the subsequent form, the Debug line indicates that it doesn't remember its value at all. Here's the code:

Public boolNieuweOpdrachtgever As Boolean
Public Sub nieuw_project_Click()

Dim nieuweOpdrachtgever As Variant

    nieuweOpdrachtgever = MsgBox("Text", vbYesNoCancel)
    Select Case nieuweOpdrachtgever
        Case vbYes
            boolNieuweOpdrachtgever = True
            Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
            nieuweOpdrachtgeverForm.Show
        Case vbNo
            boolNieuweOpdrachtgever = False
            Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
            nieuweOpdrachtForm.Show
        Case Else
            Exit Sub
    End Select

End Sub

For instance in the case of vbYes, it goes through a working form after which it goes into a second one that has an IF statement based on boolNieuweOpdrachtgever. However, by then it has already lost its value. Can you tell me what I am doing wrong?

yeah the debug would suggets so. I have found a workaround to get it working though, I am however still interested in how to take variable values from a (Sheet) module into a UserForm. — Sjors Hijgenaar
– Sjors Hijgenaar, Commented Jan 3, 2015 at 16:28

peege · Accepted Answer · 2015-01-04 08:40:37Z

1

I believe the problem is that the code that is associated with Worksheets is not the same type as a module. Worksheet Code doesn't have the ability to set public or global variables.

I've tested different scenarios and the solution would be in taking all your code inside the Worksheet code, and putting it into a separate Module, then Call the Module from the Worksheet events that trigger your code.

At that point, the module declares the public variable, and it IS accessible as a public variable.

Sheet Code:

Private Sub SomeValue_Change()
    'What would have been your code, now moved to another module
    Call NewModule    
End Sub

Module Code:

Option Explicit
Public tempValue As String

Sub NewModule()
    'Code that was previously in the Worksheet Code
    tempValue = InputBox("Please input the public variable value.","Public Variable")

    'You can test it by calling it from here to simplify the process.
    Call TestValues
End Sub

Other Code: Put this in a different module. Note there is no declaration of the variable at all. Only in the module that contains "NewModule".

Sub TestValues()

    MsgBox("The value from the public variable is :" & tempValue & ".")

End Sub

By declaring the variable from a Module instead of Worksheet-Code, the variable is captured and accessible globally. Doing the same thing from the worksheet code fails.

edited Jan 4, 2015 at 8:40

answered Jan 4, 2015 at 8:33

peege

2,4771 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sjors Hijgenaar Over a year ago

Sounds logical, thank you very much. I will try your and Edward Bagby's code later on and post the results.

E. A. Bagby · Accepted Answer · 2015-01-03 19:06:27Z

0

I tried your code and I think I know your problem. when you acquire the variable in your form , say the form nieuweOpdrachtgeverForm, then you need to call the module and the variable, not just the variable. For example: Me.Label1.Caption = Module1.boolNieuweOpdrachtgever

Here is the code I used for the Sub (in Module1) that the button calls:

Public boolNieuweOpdrachtgever As Boolean

Sub nieuw_project_Click()

Dim nieuweOpdrachtgever As Variant

    nieuweOpdrachtgever = MsgBox("Text", vbYesNoCancel)
    Select Case nieuweOpdrachtgever
        Case vbYes
            boolNieuweOpdrachtgever = True
            Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
            nieuweOpdrachtgeverForm.Show
        Case vbNo
            boolNieuweOpdrachtgever = False
            Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
            nieuweOpdrachtForm.Show
        Case Else
            Exit Sub
    End Select

End Sub

Here is the code in the forms, I used to test passing the variable. Note that I added a label called Label1, and placed the resulting value in the label:

Private Sub UserForm_Initialize()

  Me.Label1.Caption = Module1.boolNieuweOpdrachtgever

End Sub

Here are the results on forms after selecting Yes and No:

Form returning false

Form returning true

answered Jan 3, 2015 at 19:06

E. A. Bagby

95610 silver badges29 bronze badges

1 Comment

Sjors Hijgenaar Over a year ago

Thank you for your answer, it indeed sounds reasonable. One follow-up question: how would you refer to the code in Sheet1? Would you use Sheet1 or the name of the sheet? (In your example what should I replace Module1 with?)

E. A. Bagby · Accepted Answer · 2015-01-04 18:48:54Z

0

Sjors - I was wondering if my answer was helpful (I didn't see the check checked), but I'm glad it was. To Answer your last comment, I've added code. THe first bit is the code in Module1, the second is in Sheet1, and the other are from the Forms (per my answer above). I'd recommend you cut and paste the code into your project and step through it (F8) to see how it is working. This should give you a good basic understanding how to make calls between objects.

MODULE1:

Public boolNieuweOpdrachtgever As Boolean

Sub nieuw_project_Click()

Dim nieuweOpdrachtgever As Variant
Dim strFromSheet1 As String

nieuweOpdrachtgever = MsgBox("Text", vbYesNoCancel)
Select Case nieuweOpdrachtgever
    Case vbYes
        boolNieuweOpdrachtgever = True
        Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
        nieuweOpdrachtgeverForm.Show
    Case vbNo
        boolNieuweOpdrachtgever = False
        Debug.Print "In nieuw_project_Click() boolNieuweOpdrachtgever = " & boolNieuweOpdrachtgever
        nieuweOpdrachtForm.Show
    Case Else
        Exit Sub
End Select

Call Sheet1.setString

strFromSheet1 = Sheet1.strPublic

Call Sheet1.Test(strFromSheet1)


End Sub

SHEET1:

Public strPublic As String


Public Sub Test(ByVal strTest As String)

    MsgBox (strTest)

End Sub

Public Sub setString()

    strPublic = "Hello"

End Sub

EXAMPLE OF ONE OF THE FORMS:

Private Sub UserForm_Initialize()

   Me.Label1.Caption = Module1.boolNieuweOpdrachtgever

End Sub

answered Jan 4, 2015 at 18:48

E. A. Bagby

95610 silver badges29 bronze badges

1 Comment

Sjors Hijgenaar Over a year ago

Hi Edward, thank you for the elaborate second response. Sadly, I have been very busy over the weekend and regular studies have started again. I will try your code as soon as possible and post the results here.

Collectives™ on Stack Overflow

Passing variable sheet module to user form in VBA

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related