The following code is triggered by a workbook change event, then if
the activeworksheet name is in the list, asks for a password to grant
the user access to edit the activesheet. This sets the boolean
variable bPwrdEntered to true, which allows the user to edit that
sheet. When they move to another worksheet, the
Workbook_SheetDeactivate event is supposed to reset bPwrdEntered to
false so the sheet is once again protected from editing. But when I
activate another sheet, I'm getting the "Object variable or with block
variable not set" error on bPwrdEntered. I have the variable publicly
declared, but it's set elsewhere in another part of the program. How
do I get around this?

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

Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
Dim vResponse As Variant

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
If rPwrdEnt.Value = "True" Then
End
End If

wsPwrdNames.Visible = True
'bPwrdEntrd = False
PwrdForm:
ufPwrdEntry.Show

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

If sPwrd = rFoundShName.Offset(0, 1).Value Then
bPwrdEntrd = True
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True


Else
vResponse = MsgBox("Incorrect Password! Click OK to try again,
Cancel to exit", _
vbOKCancel)
If vResponse = vbCancel Then
Application.EnableEvents = True
ufPwrdEntry.Hide
Application.Undo
End
Else
GoTo PwrdForm

End If
End If
wsPwrdNames.Visible = False

End
Application.EnableEvents = False
Application.Undo

bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd <----- OBJECT VARIABLE OR....
End Sub

Thanks in advance!

Dave,

I recognize this workbook!

I believe the error is actually referring to rPwrdEnt.Value - the message is
saying that the range variable has not been set to an actual range. Check
your code and see what value - if any - it has at that point.

hth,

Doug

"davegb" wrote in message
oups.com...
The following code is triggered by a workbook change event, then if
the activeworksheet name is in the list, asks for a password to grant
the user access to edit the activesheet. This sets the boolean
variable bPwrdEntered to true, which allows the user to edit that
sheet. When they move to another worksheet, the
Workbook_SheetDeactivate event is supposed to reset bPwrdEntered to
false so the sheet is once again protected from editing. But when I
activate another sheet, I'm getting the "Object variable or with block
variable not set" error on bPwrdEntered. I have the variable publicly
declared, but it's set elsewhere in another part of the program. How
do I get around this?

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

Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
Dim vResponse As Variant

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
If rPwrdEnt.Value = "True" Then
End
End If

wsPwrdNames.Visible = True
'bPwrdEntrd = False
PwrdForm:
ufPwrdEntry.Show

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

If sPwrd = rFoundShName.Offset(0, 1).Value Then
bPwrdEntrd = True
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True


Else
vResponse = MsgBox("Incorrect Password! Click OK to try again,
Cancel to exit", _
vbOKCancel)
If vResponse = vbCancel Then
Application.EnableEvents = True
ufPwrdEntry.Hide
Application.Undo
End
Else
GoTo PwrdForm

End If
End If
wsPwrdNames.Visible = False

End
Application.EnableEvents = False
Application.Undo

bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd <----- OBJECT VARIABLE OR....
End Sub

Thanks in advance!

End clears all varaibles. You have used it here

If rPwrdEnt.Value = "True" Then
End
End If

You need to fix that line of code. Check out this link...
http://msdn2.microsoft.com/en-us/lib...ba(VS.80).aspx
--
HTH...

Jim Thomlinson


"davegb" wrote:

The following code is triggered by a workbook change event, then if
the activeworksheet name is in the list, asks for a password to grant
the user access to edit the activesheet. This sets the boolean
variable bPwrdEntered to true, which allows the user to edit that
sheet. When they move to another worksheet, the
Workbook_SheetDeactivate event is supposed to reset bPwrdEntered to
false so the sheet is once again protected from editing. But when I
activate another sheet, I'm getting the "Object variable or with block
variable not set" error on bPwrdEntered. I have the variable publicly
declared, but it's set elsewhere in another part of the program. How
do I get around this?

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

Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
Dim vResponse As Variant

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
If rPwrdEnt.Value = "True" Then
End
End If

wsPwrdNames.Visible = True
'bPwrdEntrd = False
PwrdForm:
ufPwrdEntry.Show

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

If sPwrd = rFoundShName.Offset(0, 1).Value Then
bPwrdEntrd = True
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True


Else
vResponse = MsgBox("Incorrect Password! Click OK to try again,
Cancel to exit", _
vbOKCancel)
If vResponse = vbCancel Then
Application.EnableEvents = True
ufPwrdEntry.Hide
Application.Undo
End
Else
GoTo PwrdForm

End If
End If
wsPwrdNames.Visible = False

End
Application.EnableEvents = False
Application.Undo

bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd <----- OBJECT VARIABLE OR....
End Sub

Thanks in advance!