Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Unhide set of rows (SC).
I'm designing a form where Cell A5, which is named Off_Num. This cell
has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Mr. JE McGimpsey'S Reply One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub This code Hides and unhides single row. Now how can I modify this code to unhide a set of rows. My form has a cell named Mod_Num which has values (Select one, 1to5) in a dropdown. The code has to work in such a way that when I select 1, in cell Mod_Num then rows range (17:22) should unhide. On selecting 2, rows (17:27) should unhide. On selecting 3, rows (17:32) should unhide. On selecting 4, rows (17:36) should unhide. And lastely on selecting 5 the complete row range (17:41) should unhide. On selecting Select one from drop down the row range(17:36) should hide. The above code works for unhiding single rows, so I think little modification needs to be made for the above code to unhide set of rows. Please help me modify this code. Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Unhide set of rows (SC).
Hi Ram,
Try: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Mod_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("17:41").Hidden = True If IsNumeric(.Value) Then Rows("17:17"). _ Resize(1 + CLng(.Value) * 5).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub '<<============= --- Regards, Norman "Ram" wrote in message oups.com... I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Mr. JE McGimpsey'S Reply One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub This code Hides and unhides single row. Now how can I modify this code to unhide a set of rows. My form has a cell named Mod_Num which has values (Select one, 1to5) in a dropdown. The code has to work in such a way that when I select 1, in cell Mod_Num then rows range (17:22) should unhide. On selecting 2, rows (17:27) should unhide. On selecting 3, rows (17:32) should unhide. On selecting 4, rows (17:36) should unhide. And lastely on selecting 5 the complete row range (17:41) should unhide. On selecting Select one from drop down the row range(17:36) should hide. The above code works for unhiding single rows, so I think little modification needs to be made for the above code to unhide set of rows. Please help me modify this code. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Unhide set of rows (SC).
On Apr 1, 2:00 pm, "Norman Jones"
wrote: Hi Ram, Try: '============= Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Mod_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("17:41").Hidden = True If IsNumeric(.Value) Then Rows("17:17"). _ Resize(1 + CLng(.Value) * 5).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub '<<============= --- Regards, Norman "Ram" wrote in message oups.com... I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Mr. JE McGimpsey'S Reply One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub This code Hides and unhides single row. Now how can I modify this code to unhide a set of rows. My form has a cell named Mod_Num which has values (Select one, 1to5) in a dropdown. The code has to work in such a way that when I select 1, in cell Mod_Num then rows range (17:22) should unhide. On selecting 2, rows (17:27) should unhide. On selecting 3, rows (17:32) should unhide. On selecting 4, rows (17:36) should unhide. And lastely on selecting 5 the complete row range (17:41) should unhide. On selecting Select one from drop down the row range(17:36) should hide. The above code works for unhiding single rows, so I think little modification needs to be made for the above code to unhide set of rows. Please help me modify this code. Thanks for your help.- Hide quoted text - - Show quoted text - Hi Norman Thanks a billion your code just works the way i wanted. Now I have another question regrading TreeView I have a post in this groups as well below is the history of this post. Please help me with this as well. I'm using the tree view functionality, where the node values are taken from a cell. Using the following code, With TreeView1.Nodes ..Clear Set nodX = .Add(, , "CName", Worksheets("Cert_Details").Range("C_Name").Value) Set nodX = .Add("CName", tvwChild, "Path", Worksheets("Cert_Path_module").Range("Path").Value ) Set nodX = .Add("Path", tvwChild, "Mod1", Worksheets("Cert_Path_module").Range("Module_1").V alue) Set nodX = .Add("Path", tvwChild, "Mod2", Worksheets("Cert_Path_module").Range("Module_2").V alue) Set nodX = .Add("Path", tvwChild, "Mod3", Worksheets("Cert_Path_module").Range("Module_3").V alue) Set nodX = .Add("Path", tvwChild, "Mod4", Worksheets("Cert_Path_module").Range("Module_4").V alue) Set nodX = .Add("Path", tvwChild, "Mod5", Worksheets("Cert_Path_module").Range("Module_5").V alue) Here is my question. If i have few cells that do not have any values for ex.Worksheets("Cert_Path_module").Range("Module_1" ).Value)... then i want that tree node with this cell value to be deleted, rather than having a blank tree node. Any Suggestions??? The Reply I got was: Depending exactly what you are doing, you can probably simplify the code somewhat with a loop; Dim i as long with Worksheets("Cert_Path_module") For i=1 to 10 With .Range("Module_" & i) if .Value<"" Then Set nodX = .Add("Path", tvwChild, "Mod" & i,.... ....etc Now my code looks like this With Worksheets("Cert_Path_module") For i = 1 To 5 With .Range("Module_" & i) If .Range("Module_" & i).Values < "" Then Set nodX = .Add("Path", tvwChild, "Mod", Worksheets("Cert_Path_module").Range("Module_" & i).Value) End If End With Next i End With But when the system executes Set nodX = .Add("Path", tvwChild, "Mod", Worksheets("Cert_Path_module").Range("Module_" & i).Value) I get error Run-time error '438': Object doesn't support this property or method. I need your help on this as well Norman, Thanks a lot Regards, Ram |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Unhide set of rows (SC).
Hi Ram,
'----------------- Now I have another question regrading TreeView I have a post in this groups as well below is the history of this post. Please help me with this as well. [...] I need your help on this as well Norman, Thanks a lot '----------------- This question has no obvious nexus with the subject of the current thread. You should, therefore, post this follow up question im your original thread. This will enable you to receive further assistance from Nick and, in any case, will maximise the possibility of a successful resolution of your problem. Retaining the question within the confines of the original thread may also assist others who experience similar problems. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to Unhide Rows using Macro | Excel Worksheet Functions | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
unhide row does not unhide the hidden rows | Excel Worksheet Functions | |||
VB Code to hide and unhide rows | Excel Discussion (Misc queries) | |||
Code for button to hide/unhide rows | Excel Worksheet Functions |