Getting runtime error 91 Object variable or With block variable not set when
running a protect macro.

I have cell D255 selected to allow users to edit with it's own password.
But when I run the protect all sheets macro, I get the above error.
Here is the code on the sheet I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Range("D255") '<<==== CHANGE

'If Not Intersect(Rng, Target) Is Nothing Then
If Intersect(Rng, Target) = "Y" Then '<<Error occurs here
Call HideAllWageData
End If
End Sub

I've looked, but see nothing on setting a seperate password to allow users
to edit certain cells/ranges.

David

Maybe something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Range("D255") '<<==== CHANGE

If target.cells.count 1 then exit sub 'only one cell at a time

If Not Intersect(Rng, Target) Is Nothing Then
if lcase(target.value) = "y" then
Call HideAllWageData
end if
End If
End Sub

David wrote:

Getting runtime error 91 Object variable or With block variable not set when
running a protect macro.

I have cell D255 selected to allow users to edit with it's own password.
But when I run the protect all sheets macro, I get the above error.
Here is the code on the sheet I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Range("D255") '<<==== CHANGE

'If Not Intersect(Rng, Target) Is Nothing Then
If Intersect(Rng, Target) = "Y" Then '<<Error occurs here
Call HideAllWageData
End If
End Sub

I've looked, but see nothing on setting a seperate password to allow users
to edit certain cells/ranges.

David

--

Dave Peterson

Thanks much! I found this out on the web and it works great:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$255" And Target = "Y" Then
Call HideAllWageData
End If
If Target.Address = "$D$255" And Target = "y" Then
Call HideAllWageData
End If
Call UseChangePassword
End Sub

One more question if you don't mind, Cell D255 is range protected with
password wages. When you attempt to enter the Y in the cell, password box
comes up, enter password, then you can edit the cell...which calls the macros
if Y or y. However, the cell stays open, even when I run my macro to protect
the sheet. I just want to protect that cell again. I've looked and found some
code regarding .Protection.AllowEditRanges but can't seem to make anything
work. I thought of just trying to protect it again using the same password,
but the code always bombs out. Here's what I'm using:

Dim wksOne As Worksheet

Set wksOne = Application.ActiveSheet

' Establish a range that can allow edits
' on the protected worksheet.
wksOne.Protection.AllowEditRanges.Add _
Title:="Classified", _
Range:=Range("D255:D255"), _
password:="wages"

MsgBox "Cells A1 to A4 can be edited on the protected worksheet."

' Change the password.
wksOne.Protection.AllowEditRanges.Item(1).ChangePa ssword _
password:="wages"

MsgBox "The password for these cells has been changed."

Any thoughts?
Thanks so much again for you help!

"Dave Peterson" wrote:

Maybe something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Range("D255") '<<==== CHANGE

If target.cells.count 1 then exit sub 'only one cell at a time

If Not Intersect(Rng, Target) Is Nothing Then
if lcase(target.value) = "y" then
Call HideAllWageData
end if
End If
End Sub

David wrote:

Getting runtime error 91 Object variable or With block variable not set when
running a protect macro.

I have cell D255 selected to allow users to edit with it's own password.
But when I run the protect all sheets macro, I get the above error.
Here is the code on the sheet I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range

Set Rng = Range("D255") '<<==== CHANGE

'If Not Intersect(Rng, Target) Is Nothing Then
If Intersect(Rng, Target) = "Y" Then '<<Error occurs here
Call HideAllWageData
End If
End Sub

I've looked, but see nothing on setting a seperate password to allow users
to edit certain cells/ranges.

David

--

Dave Peterson