Want to run two macros without errors

  • Thread starter Thread starter daidipya
  • Start date Start date
D

daidipya

This is the code at present

Sub HideRows1()
Dim Rng As Range
Set Rng = Sheets("input-assumptions").Range("E88")
If Rng.Value = "Yes" Then
Rows("89:89").EntireRow.Hidden = False
Range("E89").Select
ElseIf Rng.Value = "No" Then
Rows("89:122").EntireRow.Hidden = True
Range("E88").Select
End If
End Sub

Sub HideRows2()
Dim Rng As Range
Set Rng = Sheets("input-assumptions").Range("E89")
If Rng.Value = "Yes" Then
Rows("90:122").EntireRow.Hidden = False
Range("E89").Select
ElseIf Rng.Value = "No" Then
Rows("90:121").EntireRow.Hidden = True
Range("E89").Select
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 88 Then Exit Sub
If Target.Column <> 5 Then Exit Sub
HideRows1
If Target.Row <> 89 Then Exit Sub
If Target.Column <> 5 Then Exit Sub
HideRows2
End Sub

however to run the HideRows2 macro i need to run the macro from the
tools > macro - tab of the excel, while the HideRows1 macro runs on
automatically on slection of Yes or No

Please help me
 
Please change the code to the following

Sub HideRows1()
Dim Rng As Range
Set Rng = Sheets("input-assumptions").Range("E88")
If Rng.Value = "Yes" Then
Rows("89:89").EntireRow.Hidden = False
Range("E89").Select
ElseIf Rng.Value = "No" Then
Rows("89:122").EntireRow.Hidden = True
Range("E88").Select
End If
End Sub

Sub HideRows2()
Dim Rng As Range
Set Rng = Sheets("input-assumptions").Range("E89")
If Rng.Value = "Yes" Then
Rows("90:122").EntireRow.Hidden = False
Range("E89").Select
ElseIf Rng.Value = "No" Then
Rows("90:121").EntireRow.Hidden = True
Range("E89").Select
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 88 Then
If Target.Row <> 89 Then
Exit Sub
ElseIf Target.Column <> 5 Then
Exit Sub
End If
HideRows2
ElseIf Target.Column <> 5 Then
Exit Sub
End If

HideRows1
End Sub
 
thanks

but i am unable to understand the logic. Is there any way by whcih i
can have different Private Sub Worksheet_Change(ByVal Target As Range)
for different macros.
 
No.
You can have only one Worksheet_Change sub per worksheet.
The logic is just trying to do the following steps.
1. Is the row number 88, if Yes then is it Column number 5, if Yes
then Hiderows1 else just exit.
2. If the row number is not 88 then is it 89, if Yes then is Column
number 5, if Yes then Hiderows2 else just exit
 
Back
Top