Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
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" wrote in message ... 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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
On Tue, 24 May 2005, Bob Phillips 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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
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" wrote in message ... On Tue, 24 May 2005, Bob Phillips 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). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
On Mon, 23 May 2005 21:17:18 -0400, "Tom Ogilvy" 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). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
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" wrote in message ... On Mon, 23 May 2005 21:17:18 -0400, "Tom Ogilvy" 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). |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't hide/unhide rows under worksheet change event?
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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
How to Hide and Unhide Rows | Excel Discussion (Misc queries) | |||
Hide Unhide Rows | Excel Discussion (Misc queries) | |||
Hide/Unhide rows | Excel Programming | |||
How to hide and unhide rows | Excel Programming |