Conbining Two worksheet_change macros in one sheet

brijeshkeni · Jun 1, 2012

Dear All,

I was trying to use two worksheet_change macros in one sheet further to which i was getting error. Can you please advise what could be wrong? the macros are defined below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Target.Parent.Range("E48")

If Target.Count > 1 Then Exit Sub

If Intersect(Target, rng) Is Nothing Then Exit Sub

Select Case Target

Case ""

Rows("49:54").Hidden = True

Case "1"

Rows("50:50").Hidden = False

Rows("51:54").Hidden = True

Rows("55:55").Hidden = False

Case "2"

Rows("50:51").Rows.Hidden = False

Rows("52:54").Rows.Hidden = True

Rows("55:55").Hidden = False

Case "3"

Rows("50:52").Rows.Hidden = False

Rows("53:54").Rows.Hidden = True

Rows("55:55").Hidden = False

Case "4"

Rows("50:53").Rows.Hidden = False

Rows("54:54").Rows.Hidden = True

Rows("55:55").Hidden = False

Case "5"

Rows("50:54").Rows.Hidden = False

Rows("55:55").Hidden = False

End Select

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Target.Parent.Range("D27")

If Target.Count > 1 Then Exit Sub

If Intersect(Target, rng) Is Nothing Then Exit Sub

Select Case Target

Case ""

Rows("28:33").Hidden = True

Case "1"

Rows("28:29").Hidden = False

Rows("30:33").Hidden = True

Case "2"

Rows("28:30").Rows.Hidden = False

Rows("31:33").Rows.Hidden = True

Case "3"

Rows("28:31").Rows.Hidden = False

Rows("32:33").Rows.Hidden = True

Case "4"

Rows("28:32").Rows.Hidden = False

Rows("33:33").Rows.Hidden = True

Case "5"

Rows("28:33").Rows.Hidden = False

End Select

End Sub

NARAYANK991 · Jun 1, 2012

Hi Brijesh ,

The problem is you cannot have two macros with the same name in one sheet.

Try the following :

[pre]

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count &#62; 1 Then Exit Sub
If Not (Application.Intersect(Target, Range(&#34;E48&#34;)) Is Nothing) Then
Select Case Target
Case &#34;&#34;
Rows(&#34;49:54&#34;).Hidden = True
Case &#34;1&#34;
Rows(&#34;50:50&#34;).Hidden = False
Rows(&#34;51:54&#34;).Hidden = True
Rows(&#34;55:55&#34;).Hidden = False
Case &#34;2&#34;
Rows(&#34;50:51&#34;).Rows.Hidden = False
Rows(&#34;52:54&#34;).Rows.Hidden = True
Rows(&#34;55:55&#34;).Hidden = False
Case &#34;3&#34;
Rows(&#34;50:52&#34;).Rows.Hidden = False
Rows(&#34;53:54&#34;).Rows.Hidden = True
Rows(&#34;55:55&#34;).Hidden = False
Case &#34;4&#34;
Rows(&#34;50:53&#34;).Rows.Hidden = False
Rows(&#34;54:54&#34;).Rows.Hidden = True
Rows(&#34;55:55&#34;).Hidden = False
Case &#34;5&#34;
Rows(&#34;50:54&#34;).Rows.Hidden = False
Rows(&#34;55:55&#34;).Hidden = False
End Select

Else
If Application.Intersect(Target, Range(&#34;D27&#34;)) Is Nothing Then Exit Sub
Select Case Target
Case &#34;&#34;
Rows(&#34;28:33&#34;).Hidden = True
Case &#34;1&#34;
Rows(&#34;28:29&#34;).Hidden = False
Rows(&#34;30:33&#34;).Hidden = True
Case &#34;2&#34;
Rows(&#34;28:30&#34;).Rows.Hidden = False
Rows(&#34;31:33&#34;).Rows.Hidden = True
Case &#34;3&#34;
Rows(&#34;28:31&#34;).Rows.Hidden = False
Rows(&#34;32:33&#34;).Rows.Hidden = True
Case &#34;4&#34;
Rows(&#34;28:32&#34;).Rows.Hidden = False
Rows(&#34;33:33&#34;).Rows.Hidden = True
Case &#34;5&#34;
Rows(&#34;28:33&#34;).Rows.Hidden = False
End Select
End If
End Sub

[/pre]
Narayan

brijeshkeni · Jun 2, 2012

Hi Narayan,

But what if i need to add multiple worksheet_change macros. Can you please advise?

If there any tutorial you can suggest for beginner level to learn this.

Best Regards,

Brijesh

NARAYANK991 · Jun 2, 2012

Hi Brijesh ,

Use something like the following :

[pre]

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address
Case &#34;$A$1&#34;
&#39; Code here
MsgBox Target & &#34;   - 1&#34;
Case &#34;$D$7&#34;
&#39; Code here
MsgBox Target & &#34;   - 2&#34;
Case &#34;$H$3&#34;
&#39; Code here
MsgBox Target & &#34;   - 3&#34;
End Select
Application.EnableEvents = True
End Sub

[/pre]
The target cells which will trigger the change event code are A1 , D7 and H3 ; you can have many more according to your requirement.

Narayan

brijeshkeni · Jun 3, 2012

Hi Narayan,

As advised i tried the following but the target cells did not respond.Can you please advise what could be the reason.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Select Case Target.Address

Case "$E$49"

Case ""

Rows("50:56").Hidden = True

Case "1"

Rows("50:52").Hidden = False

Rows("53:56").Hidden = True

Rows("57:57").Hidden = False

Case "2"

Rows("50:53").Rows.Hidden = False

Rows("54:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case "3"

Rows("50:54").Rows.Hidden = False

Rows("55:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case "4"

Rows("50:55").Rows.Hidden = False

Rows("56:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case "5"

Rows("50:56").Rows.Hidden = False

Rows("57:57").Hidden = False

MsgBox Target & " - 1"

Case "$E$56"

Case ""

Rows("28:34").Hidden = True

Case "1"

Rows("28:35").Hidden = False

Rows("31:34").Hidden = True

Case "2"

Rows("28:35").Rows.Hidden = False

Rows("32:34").Rows.Hidden = True

Case "3"

Rows("28:35").Rows.Hidden = False

Rows("33:34").Rows.Hidden = True

Case "4"

Rows("28:35").Rows.Hidden = False

Rows("34:34").Rows.Hidden = True

Case "5"

Rows("28:35").Rows.Hidden = False

MsgBox Target & " - 2"

End Select

Application.EnableEvents = True

End Sub

Hui · Jun 3, 2012

Try the following:

[pre]

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = &#34;$E$49&#34; Then
Select Case Range(&#34;$E$49&#34;).Value
Case 0
Rows(&#34;50:56&#34;).Hidden = True
Case 1
Rows(&#34;50:52&#34;).Hidden = False
Rows(&#34;53:56&#34;).Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 2
Rows(&#34;50:53&#34;).Rows.Hidden = False
Rows(&#34;54:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 3
Rows(&#34;50:54&#34;).Rows.Hidden = False
Rows(&#34;55:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 4
Rows(&#34;50:55&#34;).Rows.Hidden = False
Rows(&#34;56:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 5
Rows(&#34;50:56&#34;).Rows.Hidden = False
Rows(&#34;57:57&#34;).Hidden = False
MsgBox Target & &#34; - 1&#34;
End Select
ElseIf Target.Address = &#34;$E$56&#34; Then
Select Case Range(&#34;$E$56&#34;).Value
Case 0
Rows(&#34;28:34&#34;).Hidden = True
Case 1
Rows(&#34;28:35&#34;).Hidden = False
Rows(&#34;31:34&#34;).Hidden = True
Case 2
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;32:34&#34;).Rows.Hidden = True
Case 3
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;33:34&#34;).Rows.Hidden = True
Case 4
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;34:34&#34;).Rows.Hidden = True
Case 5
Rows(&#34;28:35&#34;).Rows.Hidden = False
MsgBox Target & &#34; - 2&#34;
End Select
End If

End Sub

[/pre]

brijeshkeni · Jun 3, 2012

Hi Hui,

Thank you for your valued information. I did try it but it does not work for "$E$56". it does work well for "$E$49". Actually i am trying to add multiple events like as follows. can you advise if this could be possible.

Best Regards,

Brijesh

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$49" Then

Select Case Range("$E$49").Value

Case 0

Rows("50:56").Hidden = True

Case 1

Rows("50:52").Hidden = False

Rows("53:56").Hidden = True

Rows("57:57").Hidden = False

Case 2

Rows("50:53").Rows.Hidden = False

Rows("54:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case 3

Rows("50:54").Rows.Hidden = False

Rows("55:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case 4

Rows("50:55").Rows.Hidden = False

Rows("56:56").Rows.Hidden = True

Rows("57:57").Hidden = False

Case 5

Rows("50:56").Rows.Hidden = False

Rows("57:57").Hidden = False

MsgBox Target & " - 1"

End Select

ElseIf Target.Address = "$E$56" Then

Select Case Range("$E$56").Value

Case 0

Rows("28:34").Hidden = True

Case 1

Rows("28:35").Hidden = False

Rows("31:34").Hidden = True

Case 2

Rows("28:35").Rows.Hidden = False

Rows("32:34").Rows.Hidden = True

Case 3

Rows("28:35").Rows.Hidden = False

Rows("33:34").Rows.Hidden = True

Case 4

Rows("28:35").Rows.Hidden = False

Rows("34:34").Rows.Hidden = True

Case 5

Rows("28:35").Rows.Hidden = False

MsgBox Target & " - 2"

End Select

ElseIf Target.Address = "$F$74" Then

Select Case Range("$F$74").Value

Case Yes

Rows("81:83").Hidden = False

Case No

Rows("81:83").Hidden = True

MsgBox Target & " - 3"

End Select

End If

End Sub

Hui · Jun 4, 2012

In VBA numbers in quotes like "5" are text

No is a variant variable unless you put it in quotes, "No" is Text

Try the following:

[pre]

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = &#34;$E$49&#34; Then
Select Case Range(&#34;$E$49&#34;).Value
Case 0
Rows(&#34;50:56&#34;).Hidden = True
Case 1
Rows(&#34;50:52&#34;).Hidden = False
Rows(&#34;53:56&#34;).Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 2
Rows(&#34;50:53&#34;).Rows.Hidden = False
Rows(&#34;54:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 3
Rows(&#34;50:54&#34;).Rows.Hidden = False
Rows(&#34;55:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 4
Rows(&#34;50:55&#34;).Rows.Hidden = False
Rows(&#34;56:56&#34;).Rows.Hidden = True
Rows(&#34;57:57&#34;).Hidden = False
Case 5
Rows(&#34;50:56&#34;).Rows.Hidden = False
Rows(&#34;57:57&#34;).Hidden = False
MsgBox Target & &#34; - 1&#34;
End Select
ElseIf Target.Address = &#34;$E$56&#34; Then
Select Case Range(&#34;$E$56&#34;).Value
Case 0
Rows(&#34;28:34&#34;).Hidden = True
Case 1
Rows(&#34;28:35&#34;).Hidden = False
Rows(&#34;31:34&#34;).Hidden = True
Case 2
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;32:34&#34;).Rows.Hidden = True
Case 3
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;33:34&#34;).Rows.Hidden = True
Case 4
Rows(&#34;28:35&#34;).Rows.Hidden = False
Rows(&#34;34:34&#34;).Rows.Hidden = True
Case 5
Rows(&#34;28:35&#34;).Rows.Hidden = False
MsgBox Target & &#34; - 2&#34;
End Select
ElseIf Target.Address = &#34;$F$74&#34; Then
Select Case Range(&#34;$F$74&#34;).Value
Case &#34;Yes&#34;
Rows(&#34;81:83&#34;).Hidden = False
Case &#34;No&#34;
Rows(&#34;81:83&#34;).Hidden = True
MsgBox Target & &#34; - 3&#34;
End Select
End If

End Sub

[/pre]

brijeshkeni · Jun 4, 2012

Dear Hui,

Thank you very much for your valued expertise. Really appreciate your help. It is working fine now.

Best Regards,

Brijesh

brijeshkeni · Jun 25, 2012

Hi Hui,

Just wanted to check again on the same topic...I am actually having an error when i protect the above sheet. it gives me a run-time error '1004'.

it also mentions 'Unable to set the hidden property of the range class'

Can you please advise.

Best Regards,

Brijesh

Hui · Jun 25, 2012

Brijesh

When it stops hit Debug, not stop

Can you tell us what line is highlighted when it stops?

brijeshkeni · Jun 25, 2012

Hi Hui,

when i change the value of the E56 to 2 then it highlights the following row.

Rows("28:35").Rows.Hidden = False

brijeshkeni · Jun 25, 2012

Hi Hui,

when i change the value of the E49 to 2 then it highlights the following row.

Rows("50:53").Rows.Hidden = False

when i select 4 it then highlights

Rows("50:55").Rows.Hidden = False

Can you please advise how can this be achieved.

Best Regards,

Brijesh

brijeshkeni · Jun 25, 2012

Hi Hui,

when i change the value of the E49 to 2 then it highlights the following row.

Rows("50:53").Rows.Hidden = False

when i select 4 it then highlights

Rows("50:55").Rows.Hidden = False

Can you please advise how can this be achieved.

Best Regards,

Brijesh

brijeshkeni · Jun 25, 2012

Hi Hui,

when i change the value of the E49 to 2 then it highlights the following row.

Rows("50:53").Rows.Hidden = False

when i select 4 it then highlights

Rows("50:55").Rows.Hidden = False

Can you please advise how can this be achieved.

Best Regards,

Brijesh

Conbining Two worksheet_change macros in one sheet

brijeshkeni

New Member

NARAYANK991

Excel Ninja

brijeshkeni

New Member

NARAYANK991

Excel Ninja

brijeshkeni

New Member

Hui

Excel Ninja

brijeshkeni

New Member

Hui

Excel Ninja

brijeshkeni

New Member

brijeshkeni

New Member

Hui

Excel Ninja

brijeshkeni

New Member

brijeshkeni

New Member

brijeshkeni

New Member

brijeshkeni

New Member