deleting an AllowEditRange

[Eric]

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

[Tom Ogilvy]

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" <efingerhut@earthlink.net> wrote in message
news:SI7Jd.4225$YD5.1104@newsread3.news.pas.earthlink.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
>
>

[Eric]

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" <twogilvy@msn.com> wrote in message
news:%23%23XamSiAFHA.3708@TK2MSFTNGP14.phx.gbl...
>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" <efingerhut@earthlink.net> wrote in message
> news:SI7Jd.4225$YD5.1104@newsread3.news.pas.earthlink.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
>>
>>
>
>

[Tom Ogilvy]

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" <efingerhut@earthlink.net> wrote in message
news:YSjJd.4717$YD5.855@newsread3.news.pas.earthlink.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" <twogilvy@msn.com> wrote in message
> news:%23%23XamSiAFHA.3708@TK2MSFTNGP14.phx.gbl...
> >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" <efingerhut@earthlink.net> wrote in message
> > news:SI7Jd.4225$YD5.1104@newsread3.news.pas.earthlink.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
> >>
> >>
> >
> >
>
>

[Eric]

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" <twogilvy@msn.com> wrote in message
news:urpuDQuAFHA.2180@TK2MSFTNGP12.phx.gbl...
> 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" <efingerhut@earthlink.net> wrote in message
> news:YSjJd.4717$YD5.855@newsread3.news.pas.earthlink.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" <twogilvy@msn.com> wrote in message
>> news:%23%23XamSiAFHA.3708@TK2MSFTNGP14.phx.gbl...
>> >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" <efingerhut@earthlink.net> wrote in message
>> > news:SI7Jd.4225$YD5.1104@newsread3.news.pas.earthlink.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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[excelus]

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
>
>
>