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

[Don Wiss]

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

 

[Bob Phillips]

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

 

[Don Wiss]

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

 

[Tom Ogilvy]

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.

 

[Don Wiss]

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

 

[Tom Ogilvy]

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]

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