Another "Object doesn't support..."

[davegb]

I have this code (still under development):

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:

Sub cbOK_Click()
sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT
THIS....
If sPwrd = "" Then
MsgBox "Please enter the password to access this worksheet.",
vbOKCancel
End If
End Sub

I tried changing the .text to .value, but that didn't help.

Any ideas on why?

Thanks!

 

[Dave Peterson]

First, I'm not sure why you changed the workbook_sheetchange arguments from:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Second, I don't see where you declared sPwrd.
Is that in a General module like:
Public sPwrd as string

Third, check your typing.
Since the routine is behind the UserForm, I think I'd use code like:

sPwrd = me.tbPwrdEntry.Text
(Me is the object that owns the code--in this case, the userform.)

Are you sure tbPwrdEntry is really a textbox?

 

[davegb]

First, I'm not sure why you changed the workbook_sheetchange arguments from:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Thanks for your reply.
I didn't think they had to be by those specific names. I changed them
to a format I use so I can tell what kind of variable it is. I changed
them back.

Second, I don't see where you declared sPwrd.
Is that in a General module like:
Public sPwrd as string

sPwrd is declared in another general module as you've described.

Third, check your typing.
Since the routine is behind the UserForm, I think I'd use code like:

sPwrd = me.tbPwrdEntry.Text

I used the name of the form rather than "me". I don't really
understand the "me" thing, but it worked!

 

[Dave Peterson]

They can be changed--there's someone who changes them so that they match company
standards--but it's pretty unusual.

I'd guess that you misspelled the name of the userform if the "me." stuff
worked.

 

[davegb]

They can be changed--there's someone who changes them so that they match company
standards--but it's pretty unusual.

I'd guess that you misspelled the name of the userform if the "me." stuff
worked.

I checked it about 10 times, but you never know...
Thanks again, Dave!