Previous Topic Next Topic

I need to run multiple automatic macros in one sheet

Closed
akts Posts 1 Registration date Thursday July 4, 2013 Status Member Last seen July 4, 2013 - Jul 4, 2013 at 11:42 PM
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 - Jul 9, 2013 at 08:15 AM
I am developing a worksheet where there are multiple occasions down a list where users will have to select an option from a drop-down box (Y, N & N/A), and the result will mean the rows directly below this selection will either need to appear or disappear, dependant upon the selection.

I have set up the macros for each of the sections in the worksheet - one for "Y" & one for "N or N/A" at each option.

I have used the following to get one to work;

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C10").Value = "Y" Then Call ABC_Y
If Range("C10").Value = "N" Then Call ABC_N
End Sub

However, when I wish to add the next one;

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C15").Value = "Y" Then Call XYZ_Y
If Range("C15").Value = "N" Then Call XYZ_N
End Sub

I get the following error;

Compile Error: Ambiguous name detected: Worksheet_Change


Can anyone suggest how to get this to work, or another option which may be easier.
Related:

4 responses

Answer 1 / 4
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Jul 5, 2013 at 01:52 AM
@akts

Hi

as a blind shot I think your code should be like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C10").Value = "Y" Then
Call ABC_Y
Else
If Range("C10").Value = "N" Then
Call ABC_N
Else
If Range("C15").Value = "Y" Then
Call XYZ_Y
Else
If Range("C15").Value = "N" Then
Call XYZ_N
End If
End If
End If
End If
End Sub

for your better knowledge please download the below file

https://dl.dropboxusercontent.com/u/75654703/For_akts.xlsm

Hope your problem solve with this other wise please inform us

Thanks

Patnaik
0
Answer 2 / 4
akts
Jul 8, 2013 at 10:54 PM
Thanks, using that coding allowed me to enter multiple selections throughout the worksheet.

However, there are 21 different points where within the worksheet where I need to be able to select "Y, N or N/A" and then run the relevant macro dependant upon the answer. All the 21 questions must be answered every time the worksheet is used. I am thinking it would be something like;

If Range("C10").Value = "Y" Then
Call ABC_Y
Else
If Range("C10").Value = "N" Then
Call ABC_N
Then / Next ????<----------------------------*******
If Range("C15").Value = "Y" Then
Call XYZ_Y
Else
If Range("C15").Value = "N" Then
Call XYZ_N
End If
End If
End If
End If
End Sub

but this doesn't seem to work
0
Answer 3 / 4
sgmpatnaik Posts 52 Registration date Tuesday April 2, 2013 Status Member Last seen November 27, 2013 45
Jul 9, 2013 at 06:58 AM
HI

is it possible to upload a sample workbook with your macros

Thanks

Patnaik
0
Answer 4 / 4
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 9, 2013 at 08:15 AM
hi,

I have gone through your problem. I think you should use Target.Address instead of normal code of range that you have used.

Please use below code and change as per your requirement of calling different macros with the different conditions.

Code:

Public Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$C$10"
If Target = "Y" Then
MsgBox "Y"
Else
MsgBox "N"
End If
Exit Sub

Case "$C$15"
If Target = "Y" Then
MsgBox "Y"
Else
MsgBox "N"
End If
Exit Sub

Case Else
Exit Sub

End Select

End Sub
0