Help with VB code!

[pdharrison85]

Hi guys

Any help would be gratefully accepted! I am trying to write VB code to hide my rows depending upon the value entered into a cell. There will be a value entered into a cell in my excel sheet (the value will be a whole number 1,2,3 or 4) then depending upon this value I will want to hide 1,2 or 3 rows within the same sheet. I have written the code but I think it contains errors! Here is the code:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
'           We only want to do something if the changed cell is D20
If Target.Address = "$D$20" Then
'              Check it is a number
If IsNumeric(Target.Value) Then
'                 Check if its number 1
If (Target.Value) = "1" Then
Rows("22:24").EntireRow.Hidden = True
Else
Rows("22:24").EntireRow.Hidden = False
'                    Check if its number 2
If (Target.Value) = "2" Then
Rows("23:24").EntireRow.Hidden = True
Else
Rows("23:24").EntireRow.Hidden = False
'                       Check if its number 3
If (Target.Value) = "3" Then
Rows("24:24").EntireRow.Hidden = True
End If
End If
End If
End If
End If
End Sub

[/pre]

 

[sgmpatnaik]

@ pdharrison85


Hi


Firstly welcome to Chandoo.org Forums, glad your are here


your code is working fine for me if the Range $d$20 has 1 then it's hide the Rows 22 to 24 and what ever you mention


actually what do you want to achieve


Thanks


SP

 

[Debraj]

Hi pdharrison85

You started If D2 =1 and in Else part.. you started increasing.. conditions.. you need to close each else loop before starting new IF loop.. Please check difference below..


YOUR CODE

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
'We only want to do something if the changed cell is D20
If Target.Address = "$D$20" Then
'Check it is a number
If IsNumeric(Target.Value) Then
'Check if its number 1
If (Target.Value) = "1" Then
Rows("22:24").EntireRow.Hidden = True
Else
Rows("22:24").EntireRow.Hidden = False
'Check if its number 2
If (Target.Value) = "2" Then
Rows("23:24").EntireRow.Hidden = True
Else
Rows("23:24").EntireRow.Hidden = False
'Check if its number 3
If (Target.Value) = "3" Then
Rows("24:24").EntireRow.Hidden = True
End If
End If
End If
End If
End If
End Sub
MODIFIED CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'We only want to do something if the changed cell is D20
If Target.Address = "$D$20" Then
'Check it is a number
If IsNumeric(Target.Value) Then
'Check if its number 1
If (Target.Value) = "1" Then
Rows("22:24").EntireRow.Hidden = True
Else
Rows("22:24").EntireRow.Hidden = False
End If

'Check if its number 2
If (Target.Value) = "2" Then
Rows("23:24").EntireRow.Hidden = True
Else
Rows("23:24").EntireRow.Hidden = False
End If

'Check if its number 3
If (Target.Value) = "3" Then
Rows("24:24").EntireRow.Hidden = True
End If
End If
End If
End Sub

[/pre]
Regards,

Deb

 

[Debraj]

at the time of check .. dont forget to delete old one.. as two same Routine will not work..

By the way.. Check this one also..

SUGGESTED CODE

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$D$20") * (Target = 1 Or 2 Or 3) Then
Rows("22:24").Hidden = False
Rows(21 + Target & ":24").Hidden = True
End If
End Sub

[/pre]

 

[pdharrison85]

Hi Debraj


That's great thanks for that I will try it out.