Can't hide/unhide rows under worksheet change event?

I am using xl 2002. I have this macro behind the worksheet that I wish to
use to unhide/hide some rows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("AdjBasis").Address Then
HideRateRows
End If

End Sub

The AdjBasis range is a Data Validation drop down list. The above macro is
successfully calling the HideRateRows macro. Inside that macro I am trying
to unhide or hide some rows on the same sheet the change event and Data
Validation are on. I can step through the code and the lines are being run.
But the rows will not switch states between being hidden or not. I have
stopped the macro and in immediate mode issued the code to hide or unhide.
The rows still do not change their state. I have spent a bit of time on
this and have no idea why it doesn't work.

If the HideRateRows macro is run not under a change event then it works
fine.

Don <www.donwiss.com> (e-mail link at home page bottom).

I just knocked up a quick test and it worked fine for me. What code do you
have in HideRateRows?

--
HTH

Bob Phillips

"Don Wiss" <donwiss@no_spam.com> wrote in message
news:ido4915pohg066ofbcn7fu9t3b77rcesaj@4ax.com...
> I am using xl 2002. I have this macro behind the worksheet that I wish to
> use to unhide/hide some rows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address = Range("AdjBasis").Address Then
> HideRateRows
> End If
>
> End Sub
>
> The AdjBasis range is a Data Validation drop down list. The above macro is
> successfully calling the HideRateRows macro. Inside that macro I am trying
> to unhide or hide some rows on the same sheet the change event and Data
> Validation are on. I can step through the code and the lines are being
run.
> But the rows will not switch states between being hidden or not. I have
> stopped the macro and in immediate mode issued the code to hide or unhide.
> The rows still do not change their state. I have spent a bit of time on
> this and have no idea why it doesn't work.
>
> If the HideRateRows macro is run not under a change event then it works
> fine.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

On Tue, 24 May 2005, Bob Phillips <phillips@tiscali.co.uk> wrote:

>I just knocked up a quick test and it worked fine for me. What code do you
>have in HideRateRows?

I don't have the code with me at home. And I have no Usenet access from
work. But basically something like this:

Application.ScreenUpdating = False
With Sheets("Pricing")
.Protect UserInterfaceOnly:=True
.Range("56:56").EntireRow.Hidden = BoundFlag
End With

Don <www.donwiss.com> (e-mail link at home page bottom).

Good chance that BoundFlag isn't set properly or is out of scope. (or maybe
it is misspelled - using option explicit?).

Perhaps
Range("56:56").EntireRow.Hidden = Not _
Range("56:56").EntireRow.Hidden

to toggle.

--
Regards,
Tom Ogilvy


"Don Wiss" <donwiss@no_spam.com> wrote in message
news:rgv491pchdo9v26r6fakjsfh2rd1v0s1pa@4ax.com...
> On Tue, 24 May 2005, Bob Phillips <phillips@tiscali.co.uk> wrote:
>
> >I just knocked up a quick test and it worked fine for me. What code do
you
> >have in HideRateRows?
>
> I don't have the code with me at home. And I have no Usenet access from
> work. But basically something like this:
>
> Application.ScreenUpdating = False
> With Sheets("Pricing")
> .Protect UserInterfaceOnly:=True
> .Range("56:56").EntireRow.Hidden = BoundFlag
> End With
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

On Mon, 23 May 2005 21:17:18 -0400, "Tom Ogilvy" <twogilvy@msn.com> wrote:

>Good chance that BoundFlag isn't set properly or is out of scope. (or maybe
>it is misspelled - using option explicit?).

I always use Option Explicit.

>Perhaps
>Range("56:56").EntireRow.Hidden = Not _
> Range("56:56").EntireRow.Hidden
>
>to toggle.

No. I have stopped the code and tried the line in immediate mode using True
or False. BoundFlag is a boolean. And the macro works fine if I run it
outside of the change event.

Don <www.donwiss.com> (e-mail link at home page bottom).

> No. I have stopped the code and tried the line in immediate mode using
True
> or False. BoundFlag is a boolean. And the macro works fine if I run it
> outside of the change event.

Well that's just the thing - isn't it. Running it outside the change event
is a whole different world from running it inside the change event.

For instance,

Range used inside the change event refers to the sheet containing the code.
Used outside the change event in a general module, it refers to the
activesheet. So maybe it is working perfectly, but not on the sheet you
think - then again this may not be applicable at all. The visibility of a
variable could also be affected by location.

--
Regards,
Tom Ogilvy





"Don Wiss" <donwiss@no_spam.com> wrote in message
news:q43591h95uhajf9u9hr82cpiti8mo456ah@4ax.com...
> On Mon, 23 May 2005 21:17:18 -0400, "Tom Ogilvy" <twogilvy@msn.com> wrote:
>
> >Good chance that BoundFlag isn't set properly or is out of scope. (or
maybe
> >it is misspelled - using option explicit?).
>
> I always use Option Explicit.
>
> >Perhaps
> >Range("56:56").EntireRow.Hidden = Not _
> > Range("56:56").EntireRow.Hidden
> >
> >to toggle.
>
> No. I have stopped the code and tried the line in immediate mode using
True
> or False. BoundFlag is a boolean. And the macro works fine if I run it
> outside of the change event.
>
> Don <www.donwiss.com> (e-mail link at home page bottom).

All of the ranges are fine. All of the variables are fine. I have stepped
through the code and checked everything. After realizing that I also had a
problem on this sheet with a change event trying to change the format of a
cell, I gave up on the task and simply leave those two lines unhidden all
the time. Used or not. Thanks for your suggestions, but something is
problematic with the sheet. With 48 sheets, and a 4 MB size when all code
is compiled, the workbook can be flaky. (And Open and Repair does change a
thing.)

On Tue, 24 May 2005 09:16:59 -0400, "Tom Ogilvy" <twogilvy@msn.com> wrote:

>> No. I have stopped the code and tried the line in immediate mode using True
>> or False. BoundFlag is a boolean. And the macro works fine if I run it
>> outside of the change event.
>
>Well that's just the thing - isn't it. Running it outside the change event
>is a whole different world from running it inside the change event.
>
>For instance,
>
>Range used inside the change event refers to the sheet containing the code.
>Used outside the change event in a general module, it refers to the
>activesheet. So maybe it is working perfectly, but not on the sheet you
>think - then again this may not be applicable at all. The visibility of a
>variable could also be affected by location.