Code to Unhide set of rows (SC).

[Ram]

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.

 

[Norman Jones]

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
'<<=============

 

[Ram]

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









- 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").Value)
Set nodX = .Add("Path", tvwChild, "Mod2",
Worksheets("Cert_Path_module").Range("Module_2").Value)
Set nodX = .Add("Path", tvwChild, "Mod3",
Worksheets("Cert_Path_module").Range("Module_3").Value)
Set nodX = .Add("Path", tvwChild, "Mod4",
Worksheets("Cert_Path_module").Range("Module_4").Value)
Set nodX = .Add("Path", tvwChild, "Mod5",
Worksheets("Cert_Path_module").Range("Module_5").Value)
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

 

[Norman Jones]

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.