ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Want to run two macros without errors (https://www.excelbanter.com/excel-discussion-misc-queries/91780-want-run-two-macros-without-errors.html)

daidipya
Want to run two macros without errors
 
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

[email protected]
Want to run two macros without errors
 
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

daidipya wrote:
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

daidipya
Want to run two macros without errors
 
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.

wrote:
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

daidipya wrote:
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

[email protected]
Want to run two macros without errors
 
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


daidipya wrote:
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.

wrote:
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

daidipya wrote:
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


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com