CODEWITHSUNDEEP
vba
Stan
Stan

Reputation: 95

VBA obtain a variable value from an userform

I'm trying to obtain the value for Teller from an userform in my module. Here's my userform code:

Private Sub UserForm_Initialize()

Dim LastRow As Long
Dim i As Long
Dim Teller As Long
Dim chkBox As MSForms.CheckBox

Teller = 1
LastRow = Worksheets("Sheet").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
    If Worksheets("Sheet").Cells(i, 1).Value = Worksheets("Sheet").Range("S1").Value Then
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & Teller)
        chkBox.Caption = Worksheets("Sheet").Cells(i, 9).Value
        chkBox.Left = 5
        chkBox.Top = 25 + ((Teller - 1) * 20)
        Teller = Teller + 1
    End If
Next i    
End Sub

And my module code:

Dim p As Long
Dim x As String
With UserForm
    .Show
    For p = 1 To .Teller
        x= .Controls("CheckBox_" & p).Caption
        MsgBox (x)
    Next p
    End
End With

UserForm.Teller won't give me the value of Teller. How do I get this?

Upvotes: 0

Views: 151

Answers (1)

wrslphil
wrslphil

Reputation: 258

Teller isn't an object or method of the userform, it's a variable you have defined in the userform_initialize module.

What you could do is declare the variable as a public variable in your module and then call it in your userform. You do this by declaring the variable above your subroutine in the module as public as below. 

Public Teller As Long

Then you would just use

For p = 1 to teller

in your module

You would need to reset the variable to 0 manually at the end of the code/ when the userform is closed if you want it to reset on each run

Thanks

Upvotes: 2

Related Questions