How to publicly declare a variable?

[davegb]

I have this code in "ThisWorkbook":

Option Explicit
Public sShName As String
Public bPwrdEntrd As Boolean
Public sPwrd As String
Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal
Target As Range)

If Right(wsShName.Name, 7) <> "Monthly" Then 'ignore these sheeets
Select Case wsShName.Name
Case "(Code Key)" 'Excluded sheets
Exit Sub

Case Else 'if password hasn't been entered yet, then ask
for password
If bPwrdEntrd = False Then

Call PasswordEntry
End If
End Select
End If

End Sub

Sub PasswordEntry()
Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
'Dim sPwrd As String

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("A1:A8")
wsPwrdNames.Visible = True

ufPwrdEntry.Show

Set rFoundShName = rShNames.Find(ActiveSheet.Name, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If sPwrd = rFoundShName.Offset(1, 0).Value Then
bPwrdEntrd = True

End If
wsPwrdNames.Visible = False

End Sub

And this code in the userform (ufPwrdEntry):

Sub cbOK_Click()
sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR
If sPwrd = "" Then
MsgBox "Please enter the password to access this worksheet.",
vbOKCancel
End If
End Sub

I'm getting a "Variable not defined" error on the noted line.

I checked in Walkenbach, which says, "To make a variable available to
all the procedures in all the VBA modules in a project, declare the
variable at the module at the module level by using the "Public"
keyword rather than "Dim"."

So is a userform not counted as part of the project? Why isn't this
varible being recognized?

Thanks!

 

[Susan]

because you have them declared in a workbook/worksheet sub instead of
a module....
open a new module & declare all your public variables THERE.
susan

 

[davegb]

Thanks for your reply. I moved the declarations out of "ThisWorkbook"
and put them in a Module. So I guess public declarations in
"ThisWorkbook" don't count?

In any case, now I'm getting an error message on the next line where I
set the variable equal to the textbox string saying "Object doesn't
support this method or property"! Any ideas on that?

 

[Susan]

whenever i get that error, i either add "set" to the statement (if it
isn't there), or delete it if it is......... cuz i can never remember
when i have to use it or not.
try that.


set sPwrd = ufPwrdEntry.tbPwrdEntry.Value

it's also POSSIBLE (but i'm not sure) that by using .value you are
implying a numerical variable, where sPwrd is declared as a
string.....
if a "guru-type-person" checks in, they will tell us if that's true or
not.


susan

 

[davegb]

Thanks for your reply, Susan. In this case, that won't help. Set is
for setting an object equal to something, like a workbook, worksheet,
range, etc. This is a string, so "Set" doesn't apply.

 

[Susan]

see, i'm never sure! ha ha
maybe you should start a new thread with your most recent problem,
since i obviously don't know the answer.

susan

 

[davegb]

see, i'm never sure! ha ha
maybe you should start a new thread with your most recent problem,
since i obviously don't know the answer.

susan




- Show quoted text -

Thanks for your help! I'll re-post.