I need to run multiple automatic macros in one sheet Closed

Question

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.

sgmpatnaik · Answer

@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

akts · Answer

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

sgmpatnaik · Answer

HI

is it possible to upload a sample workbook with your macros 

Thanks

Patnaik

zs1986 · Answer

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

I need to run multiple automatic macros in one sheet

4 responses

Similar discussions

Subscribe To Our Newsletter!