Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Hello all
I'm certainly no expert, and I'm trying to botch my way through this project. Surprise, surprise I got stuck again I found a macro on the web: http://excel.tips.net/Pages/T001940_...ell_Value.html I adjusted it a bit, but it's still not working, can anyone help? I'm looking for a macro that will hide/unhide rows depending on whether the value in the N column is "Yes", or "-". If "Yes" then show, if "-" then hide. The value could change so the macro needs to be able to handle that as well. Thanks in advance Sub HideRows() BeginRow = 51 EndRow = 1354 ChkCol = 14 For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Since you wanted the flexibility of macro to handle any change as
well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Socko
Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Your HideRows macro works for me but maybe you want it to run automatically
when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Gord
Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
This is not a macro to be run by you..............it is sheet event code and
runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Gord,
I am trying to do something almost identical to Joanne, but am not having any luck with your code. I modified it to: Private Sub Worksheet_Calculate() BeginRow = 1 EndRow = 1 ChkCol = 1 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "1" Then Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub I type the value 1 in cell, A1, but row 2 does not hide. I also changed the if to look for 1 rather than "1", but there was no change. Do we need to do something special to activate this code ? Thanks Bill H "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Works for me as you have it written but requires Calc mode to be on
Automatic. If you are just typing the 1 in A1 you should be using Private Sub Worksheet_Change(ByVal Target As Range) instead of Private Sub Worksheet_Calculate() Gord On Tue, 16 Dec 2008 14:56:04 -0800, Bill H wrote: Gord, I am trying to do something almost identical to Joanne, but am not having any luck with your code. I modified it to: Private Sub Worksheet_Calculate() BeginRow = 1 EndRow = 1 ChkCol = 1 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "1" Then Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt + 1, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub I type the value 1 in cell, A1, but row 2 does not hide. I also changed the if to look for 1 rather than "1", but there was no change. Do we need to do something special to activate this code ? Thanks Bill H "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Perfect, thanks ! I've got it working Bill H |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Gord,
THanks for all the help, I've got it working now ! Just a side question, can I reference a cell on another work sheet, or by a named cell ? Thanks Bill H |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
To run some event code or???
I would say most likely Give some details Gord On Tue, 16 Dec 2008 17:31:02 -0800, Bill H wrote: Gord, THanks for all the help, I've got it working now ! Just a side question, can I reference a cell on another work sheet, or by a named cell ? Thanks Bill H |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Same problem.
But instead of using If Cells(RowCnt, ChkCol).Value = "-" can I refer to a cell in another sheet by naming it ? If so, what would the code look like. THanks BIll H "Gord Dibben" wrote: To run some event code or??? I would say most likely Give some details Gord On Tue, 16 Dec 2008 17:31:02 -0800, Bill H wrote: Gord, THanks for all the help, I've got it working now ! Just a side question, can I reference a cell on another work sheet, or by a named cell ? Thanks Bill H |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
if worksheets("somesheetnamehere").range("a1").value 999 then
or if worksheets("somesheetnamehere").range("somenameher e").value 999 then Bill H wrote: Same problem. But instead of using If Cells(RowCnt, ChkCol).Value = "-" can I refer to a cell in another sheet by naming it ? If so, what would the code look like. THanks BIll H "Gord Dibben" wrote: To run some event code or??? I would say most likely Give some details Gord On Tue, 16 Dec 2008 17:31:02 -0800, Bill H wrote: Gord, THanks for all the help, I've got it working now ! Just a side question, can I reference a cell on another work sheet, or by a named cell ? Thanks Bill H -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Gord
I do realise it should run when calculations are made. It didn't work when a change was made and the calculation was done. In my hope to get it working I thought I'd try running it anyway. Sorry if I've not been very clear in my explanations. The formula in the N column (ChkCol) is linked to another worksheet. When the workbook is blank the N column shows a value of #REF!. Depending on data entry in other worksheets this cell will change to Yes or -. (No it is not literally "-"). I only want the row to be displayed if the cell contains "Yes". So if it contains - it should be hiden. The macro does seem to work, when the value changes from "Yes" to "-". But although that may happen when the user does data entry, it will not be the most common occurence. Maybe the best solution would be to hide all rows and set the macro to unhide the row if the value is changed to "Yes". If this a more effective method, how would the code look, my guess was as follows, but it didn't work Thanks for patience and your help Sub HideRows() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "Yes" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt Application.EnableEvents = True End Sub "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
You are mixing up the two sets of code in your latest example.
Do you want the rows to be hidden automatically or by manually running the code? The Worksheet_Calculate code in the sheet module should hide the rows automatically when the value changes to - in any cell in column N and unhide on any other value. Column N has formulas that derive their results from cell values in another sheet............right? I don't understand why they would show #REF! when the source cells are blank. Please post a few examples of the formulas in column N and we can work from there. Otherwise send me the workbook to look at. Gord On Wed, 17 Dec 2008 01:05:01 -0800, Joanne wrote: Gord I do realise it should run when calculations are made. It didn't work when a change was made and the calculation was done. In my hope to get it working I thought I'd try running it anyway. Sorry if I've not been very clear in my explanations. The formula in the N column (ChkCol) is linked to another worksheet. When the workbook is blank the N column shows a value of #REF!. Depending on data entry in other worksheets this cell will change to Yes or -. (No it is not literally "-"). I only want the row to be displayed if the cell contains "Yes". So if it contains - it should be hiden. The macro does seem to work, when the value changes from "Yes" to "-". But although that may happen when the user does data entry, it will not be the most common occurence. Maybe the best solution would be to hide all rows and set the macro to unhide the row if the value is changed to "Yes". If this a more effective method, how would the code look, my guess was as follows, but it didn't work Thanks for patience and your help Sub HideRows() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "Yes" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt Application.EnableEvents = True End Sub "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Gord
I have sent you a de-sensitised version of my workbook to: gorddibbATshawDOTca. There is a full explanation in the email. Thanks in advance "Gord Dibben" wrote: You are mixing up the two sets of code in your latest example. Do you want the rows to be hidden automatically or by manually running the code? The Worksheet_Calculate code in the sheet module should hide the rows automatically when the value changes to - in any cell in column N and unhide on any other value. Column N has formulas that derive their results from cell values in another sheet............right? I don't understand why they would show #REF! when the source cells are blank. Please post a few examples of the formulas in column N and we can work from there. Otherwise send me the workbook to look at. Gord On Wed, 17 Dec 2008 01:05:01 -0800, Joanne wrote: Gord I do realise it should run when calculations are made. It didn't work when a change was made and the calculation was done. In my hope to get it working I thought I'd try running it anyway. Sorry if I've not been very clear in my explanations. The formula in the N column (ChkCol) is linked to another worksheet. When the workbook is blank the N column shows a value of #REF!. Depending on data entry in other worksheets this cell will change to Yes or -. (No it is not literally "-"). I only want the row to be displayed if the cell contains "Yes". So if it contains - it should be hiden. The macro does seem to work, when the value changes from "Yes" to "-". But although that may happen when the user does data entry, it will not be the most common occurence. Maybe the best solution would be to hide all rows and set the macro to unhide the row if the value is changed to "Yes". If this a more effective method, how would the code look, my guess was as follows, but it didn't work Thanks for patience and your help Sub HideRows() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "Yes" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt Application.EnableEvents = True End Sub "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with conditional hide/unhide row macro
Is there an easy way to set this to run only on the active worksheet?
I found this method extremely useful, but when I run a multi-tabbed workbook, Excel runs the code for all tabs every time I make any update in any single sheet. It even runs it every time updates are made in other workbooks. "Gord Dibben" wrote: You are mixing up the two sets of code in your latest example. Do you want the rows to be hidden automatically or by manually running the code? The Worksheet_Calculate code in the sheet module should hide the rows automatically when the value changes to - in any cell in column N and unhide on any other value. Column N has formulas that derive their results from cell values in another sheet............right? I don't understand why they would show #REF! when the source cells are blank. Please post a few examples of the formulas in column N and we can work from there. Otherwise send me the workbook to look at. Gord On Wed, 17 Dec 2008 01:05:01 -0800, Joanne wrote: Gord I do realise it should run when calculations are made. It didn't work when a change was made and the calculation was done. In my hope to get it working I thought I'd try running it anyway. Sorry if I've not been very clear in my explanations. The formula in the N column (ChkCol) is linked to another worksheet. When the workbook is blank the N column shows a value of #REF!. Depending on data entry in other worksheets this cell will change to Yes or -. (No it is not literally "-"). I only want the row to be displayed if the cell contains "Yes". So if it contains - it should be hiden. The macro does seem to work, when the value changes from "Yes" to "-". But although that may happen when the user does data entry, it will not be the most common occurence. Maybe the best solution would be to hide all rows and set the macro to unhide the row if the value is changed to "Yes". If this a more effective method, how would the code look, my guess was as follows, but it didn't work Thanks for patience and your help Sub HideRows() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "Yes" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = False Else Cells(RowCnt, ChkCol).EntireRow.Hidden = True End If Next RowCnt Application.EnableEvents = True End Sub "Gord Dibben" wrote: This is not a macro to be run by you..............it is sheet event code and runs when calculation takes place. You said the cells with values of "-" in column 14 were formula-generated. When the value changes from "-" to something else because of a calculation, the row(s) will unhide. Is the cell value literally "-" or just a - If "-" change the code to read If Cells(RowCnt, ChkCol).Value = ""-"" Then If you want to send me a de-sensitized copy of your workbook with a detailed explantion of what you want to happen, I will see what I can do. I can't ell from the posts so far exactly what you need done. Change the AT and DOT in my email address to send to me. Gord On Tue, 16 Dec 2008 11:49:01 -0800, Joanne wrote: Gord Thank you for your help I have copied your code into the sheet module, however it still doesn't seem to work. Neither when the ChkCol is changed, or when I try to run the macro. When I try to run the macro there is a problem with the line 'If Cells(RowCnt, ChkCol).Value = "-" Then'. Could the problem be that at the moment I am testing this macro when there is little data entry, so most of the values start out (before data is entered) as error values such as #N/A? "Gord Dibben" wrote: Your HideRows macro works for me but maybe you want it to run automatically when values change in column 14? Right-click on the sheet tab and "View Code". Copy/paste this event code into that sheet module. Private Sub Worksheet_Calculate() BeginRow = 51 EndRow = 1354 ChkCol = 14 Application.EnableEvents = False For RowCnt = BeginRow To EndRow If Cells(RowCnt, ChkCol).Value = "-" Then Cells(RowCnt, ChkCol).EntireRow.Hidden = True Else Cells(RowCnt, ChkCol).EntireRow.Hidden = False End If Next RowCnt Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 08:04:01 -0800, Joanne wrote: Socko Thanks for your help. I pasted in this macro, but it doesn't seem to work. On the debugger, it has a problem with "If Target.Column = 14 Then", the error "object required" comes up. But even so, the cell that will change is linked to another cell and is NOT modified manually. It would be a specific change not 'any', so it wouldn't be the right macro anyway. Any other suggestions would be very much appreciated "Socko" wrote: Since you wanted the flexibility of macro to handle any change as well, the following code will take care of it. Please insert the following code to the worksheet codemodule of a given worksheet. (please be advised that this code will work only if there is any change in the content of a cell... if the cell links to another cell and if the related cell is changed, this code wont help)., Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 14 Then Call HideRows End If End Sub I hope this helps. Selva V Pasupathy For more on Excel, VBA, and other resources Please visit: http://socko.wordpress.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format - Hide/Unhide | Excel Worksheet Functions | |||
CONDITIONAL HIDE/UNHIDE ROWS | Excel Discussion (Misc queries) | |||
Excel 97 hide unhide problem | Excel Discussion (Misc queries) | |||
Is there a conditional formula to hide and unhide columns? | Excel Programming | |||
Conditional Hide/Unhide Rows | Excel Discussion (Misc queries) |