On Apr 1, 2:00 pm, "Norman Jones" <normanjo...@whereforartthou.com>
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" <sreeram....@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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").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
Similar Threads
