Variable in UserForm module disappears between initialize and click events

[cpu97]

Hello all, and thanks for your help in advance.

The structure I've set up is for the user to run a macro, which brings up one userform, and my goal is to allow the user to identify a string from a worksheet and then pass both that worksheet and string to a module for processing. I have declared the worksheet variable in the scope of the userform, and during initialization the code tests the worksheet names in the active workbook to determine which one is correct, then the form shows and asks the user to choose between certain strings found in that worksheet. However, while debugging I see from the locals window that while I set the worksheet correctly in the initialization, once I click on a button within that form, the variable does not exist in the locals window, and the initialization procedure has disappeared from the call stack, which might explain that. I am probably missing something basic, but I can't seem to figure out what it is. How can I get the worksheet variable I define in the initialization to still exist on the click event, in order to pass it back to the original macro?

Dim TestSheet as Worksheet
Private Sub Userform_Initialize()
Dim str as String
Dim objWb as Object
Dim objWS as Object
dim TestBook as Workbook
Dim a as Integer
Dim b as Integer
Dim carr
Dim f as Boolean
Set TestBook = ActiveWorkbook
f= False
For Each objWs in Testbook.Worksheets
      If Instr(1,objWs.Name, " Table", 0)> 0 Then
           c = objWs.Name
           Set TestSheet = TestBook.Worksheets(c)
      End If
Next
str = str & TestSheet.Cells(1,2).Value & "," & TestSheet.Cells(1,6).Value
carr = Split(str, ",")
Listbox2.List = carr
End Sub

Private Sub CommandButton1_Click()
dim g as string
If Listbox2.Value <> "" Then
     g = Listbox2.Value
     Unload Me
     Reconcile_Data TestSheet, g
End If
End Sub

 

[Micron]

Variables are private in scope by default. By placing it at the module level like that, it should have scope throughout the entire module, including button click code. It's not clear in your post if the stack goes outside the form module but if it does you will lose scope there. Try declaring TestSheet as Public. Also, if not careful, other code can alter the value of variables that are module level and/or Public, but I don't think you're saying that is the issue. If you are, then it's worth noting that the default reference is ByVal not ByRef IIRC.