question on scope of a global variable

X

XL Programmer

I have a user form. it loads a combo box , with a list of patients,
1 of the options is print a single sheet. I don't know how to save
the selected value in a global and get that to the module from the
userform. I tried a public global varible in the userform. I tried
accessing this from the module like
UserForm1.g_fnameLast and I don't get the value, what am I doing
wrong? the asterisks is where I'm not getting the value.

tia,

-----combo box event in userform1----------
Option Explicit
Public g_rng As Range
Public g_fNameLName As String

Public Sub ComboBox2_change()

Select Case ComboBox2.Value
Case Is = "All"
prntAllPatientsShts
Case Is = "Exit"

'do nothing

Case Else

g_fNameLName = UserForm1.ComboBox2.Value
prntSinglePatientSht
SingleMonths
End Select

Unload Me
Me.ComboBox2.Clear
End Sub


-------code where I'm trying to access g_fnamelname-----------------

Sub CpySinglePatientSht()
Dim wb As Workbook
Dim ws As Worksheet
Dim Lname As String
Dim sStr As String


Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
'copies the control worksheet and puts the copied sheet in front
of it
Set ws = wb.Sheets(2)
sStr = UserForm1.g_fNameLName *********
Lname = Mid(sStr, InStr(1, sStr, " ") + 1)
'parses the FName and LName concatenation into _
just the Lname and renames worksheet
ws.Name = Lname
End Sub
 
X

XL Programmer

This question is answered. I figured out what the "scope" problem
was. It was a simple but annoying. I set the ws to be the right
sheet and get the global variable which was set up properly but when I
looked at the variable in the debug window there was no value. A clue
was the drop down list was only 22 or so lines. What was happening
was I didn't select the active sheet so it was reading values in
column c on the wrong sheet. the user form is correct. Thanks
anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top