I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I need
something like this or else I'll get an error when I try to add the same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle, Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle, Range:=myRange
End Sub

TIA for any insights

I suspect you need to unprotect the sheet to make these changes.

so at the top of your code, unprotect the sheet,
make you changes,
Then protect it again when you are done.

--
Regards,
Tom Ogilvy

"Eric" wrote in message
ink.net...
I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I need
something like this or else I'll get an error when I try to add the same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle,
Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle,
Range:=myRange
End Sub

TIA for any insights

Yes, that did clear it up, thanks.

I had actually unprotected the worksheet in the calling procedure, but
obviously the way I coded it, VBA considered them to be two separate
objects. I've include that sub in the hopes you can answer a larger
question. Given my druthers, I would have passed the item from the collect,
ws, into the defineEditableRanges sub as an argument (ie,
defineEditableRanges(wkSheet As WorkSheet), by VBA didn't seem to like that
(I forget the exact complaint right now). I'm new to VBA and obviously
haven't got a handle on some basic concepts yet. If I could do this, my
defineEditableRanges sub wouldn't need to go about and set the
ActiveWorksheet as 'ws' would be it, no?

Thanks!

Private Sub prepareWorksheets(makeSecure As Boolean)
'unlock it first so we can modify things
For Each ws In Worksheets
ws.Unprotect
'Key code column is in C for each sheet
With ws
.Columns("C").Hidden = makeSecure
End With
If makeSecure Then
defineEditableRanges---------------------------------- would prefer
defineEditableRanges(ws) but gets error
ws.Protect
Else
ws.Unprotect
End If
Next ws
End Sub

"Tom Ogilvy" wrote in message
...
I suspect you need to unprotect the sheet to make these changes.

so at the top of your code, unprotect the sheet,
make you changes,
Then protect it again when you are done.

--
Regards,
Tom Ogilvy

"Eric" wrote in message
ink.net...
I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I need
something like this or else I'll get an error when I try to add the same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle,
Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle,
Range:=myRange
End Sub

TIA for any insights

The reason you get an error is that you do not Dim ws as Worksheet
specifically in your calling routine and the sub expects a Worksheet
variable. The correction is to declare it. Also, don't put () around the
argument to defineEditableRanges unless you preface the call with the Call
Keyword
defineEditableRanges ws is correct
defineEditableRanges( ws) would cause ws to be evaluated before passing
and would cause an error
Call defineEditableRanges(ws) is also correct

Private Sub prepareWorksheets(makeSecure As Boolean)
'unlock it first so we can modify things
Dim ws as Worksheet '<== added line
For Each ws In Worksheets
ws.Unprotect
'Key code column is in C for each sheet
With ws
.Columns("C").Hidden = makeSecure
End With
If makeSecure Then
defineEditableRanges ws '<== removed parens
ws.Protect
Else
ws.Unprotect
End If
Next ws
End Sub

--
Regards,
Tom Ogilvy


"Eric" wrote in message
nk.net...
Yes, that did clear it up, thanks.

I had actually unprotected the worksheet in the calling procedure, but
obviously the way I coded it, VBA considered them to be two separate
objects. I've include that sub in the hopes you can answer a larger
question. Given my druthers, I would have passed the item from the
collect,
ws, into the defineEditableRanges sub as an argument (ie,
defineEditableRanges(wkSheet As WorkSheet), by VBA didn't seem to like
that
(I forget the exact complaint right now). I'm new to VBA and obviously
haven't got a handle on some basic concepts yet. If I could do this, my
defineEditableRanges sub wouldn't need to go about and set the
ActiveWorksheet as 'ws' would be it, no?

Thanks!

Private Sub prepareWorksheets(makeSecure As Boolean)
'unlock it first so we can modify things
For Each ws In Worksheets
ws.Unprotect
'Key code column is in C for each sheet
With ws
.Columns("C").Hidden = makeSecure
End With
If makeSecure Then
defineEditableRanges---------------------------------- would
prefer
defineEditableRanges(ws) but gets error
ws.Protect
Else
ws.Unprotect
End If
Next ws
End Sub

"Tom Ogilvy" wrote in message
...
I suspect you need to unprotect the sheet to make these changes.

so at the top of your code, unprotect the sheet,
make you changes,
Then protect it again when you are done.

--
Regards,
Tom Ogilvy

"Eric" wrote in message
ink.net...
I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I
need
something like this or else I'll get an error when I try to add the
same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where
I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle,
Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle,
Range:=myRange
End Sub

TIA for any insights

Arguments without parens looks rather nasty to me, but it beats typing the
word Call.

Any particular books you'd recommend for someone with oo programming
experience, but new to VBA?

Thanks for all your help!

"Tom Ogilvy" wrote in message
...
The reason you get an error is that you do not Dim ws as Worksheet
specifically in your calling routine and the sub expects a Worksheet
variable. The correction is to declare it. Also, don't put () around the
argument to defineEditableRanges unless you preface the call with the Call
Keyword
defineEditableRanges ws is correct
defineEditableRanges( ws) would cause ws to be evaluated before passing
and would cause an error
Call defineEditableRanges(ws) is also correct

Private Sub prepareWorksheets(makeSecure As Boolean)
'unlock it first so we can modify things
Dim ws as Worksheet '<== added line
For Each ws In Worksheets
ws.Unprotect
'Key code column is in C for each sheet
With ws
.Columns("C").Hidden = makeSecure
End With
If makeSecure Then
defineEditableRanges ws '<== removed parens
ws.Protect
Else
ws.Unprotect
End If
Next ws
End Sub

--
Regards,
Tom Ogilvy


"Eric" wrote in message
nk.net...
Yes, that did clear it up, thanks.

I had actually unprotected the worksheet in the calling procedure, but
obviously the way I coded it, VBA considered them to be two separate
objects. I've include that sub in the hopes you can answer a larger
question. Given my druthers, I would have passed the item from the
collect,
ws, into the defineEditableRanges sub as an argument (ie,
defineEditableRanges(wkSheet As WorkSheet), by VBA didn't seem to like
that
(I forget the exact complaint right now). I'm new to VBA and obviously
haven't got a handle on some basic concepts yet. If I could do this, my
defineEditableRanges sub wouldn't need to go about and set the
ActiveWorksheet as 'ws' would be it, no?

Thanks!

Private Sub prepareWorksheets(makeSecure As Boolean)
'unlock it first so we can modify things
For Each ws In Worksheets
ws.Unprotect
'Key code column is in C for each sheet
With ws
.Columns("C").Hidden = makeSecure
End With
If makeSecure Then
defineEditableRanges---------------------------------- would
prefer
defineEditableRanges(ws) but gets error
ws.Protect
Else
ws.Unprotect
End If
Next ws
End Sub

"Tom Ogilvy" wrote in message
...
I suspect you need to unprotect the sheet to make these changes.

so at the top of your code, unprotect the sheet,
make you changes,
Then protect it again when you are done.

--
Regards,
Tom Ogilvy

"Eric" wrote in message
ink.net...
I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I
need
something like this or else I'll get an error when I try to add the
same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------
Where
I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle,
Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle,
Range:=myRange
End Sub

TIA for any insights

Try:
activesheet.protection.alloweditranges(1).delete
1 - the first range, it can be the name of the range.

"Eric" wrote:

I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange

Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I need
something like this or else I'll get an error when I try to add the same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle, Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle, Range:=myRange
End Sub

TIA for any insights