VBA obtain a variable value from an userform

Question

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?

teller is declared as private to the userform, so modules will not be able to read it. You can declare teller as public in a module, which will give you the ability to read it from other modules. — brett_x
– brett_x, Commented Jul 21, 2017 at 11:31

wrslphil · Accepted Answer · 2017-07-21 13:02:21Z

2

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

edited Jul 21, 2017 at 13:02

answered Jul 21, 2017 at 11:39

wrslphil

2581 silver badge10 bronze badges

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

5 Comments

Stan Over a year ago

Thanks. I tried it but it seems like the value of Teller is empty in the module, since it gives me no output.

Stan Over a year ago

When I put a MsgBox(Teller) at the end of the With Userform it shows a blanco messagebox. Not even a 0 or 1, just nothing.

wrslphil Over a year ago

Try and declare it at the top of the module rather than in the userform, it should work then. My mistake! But make sure to remove it from the Userform Module first

wrslphil Over a year ago

No problem, happy to help Stan!

brett_x Over a year ago

@user8234201 you should edit and fix your answer so it is accurate. (Maybe show it as public in Stan's module code)

Collectives™ on Stack Overflow

VBA obtain a variable value from an userform

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related