Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Two worksheet change events within same sheet

0

Hello All

I have a worksheet change event already in place but now also want to add another change event that is totally independant to the first but is also in the same worksheet. I've tried just inserting the 2nd one after the 1st but it doesn't like it and comes up with a message box that says 'Compile error: ambiguous name detected: Worksheet_Change'

Please could someone help with how to do this - both macros are listed below:

1st (original) Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 01 Aug 2018

    With Target
        If .Address = "$D$1" Then RenameTab .Value
    End With
End Sub

2nd (New) macro:

Private Sub Worksheet_Change(ByVal Target As Range)
'return different value from dropdown list
    selectedNa = Target.Value
    If Target.Column = 3 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("ContractList"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Many thanks in advance

excelmacros-vba
MS Office Version: Windows 11 - Office 365
posted by: JonP (rep: 37) on Thu Aug 2, 2018 at 5:47 pm
Answer
Discuss

Answers

0

I've found the answer to my own question - simply remove the last line from the 1st one and the 1st line from the 2nd.  However i've now discovered that my 2nd macro doesn't work anyway so am posting a new question about that :D

posted by: JonP (rep: 37) on Thu Aug 2, 2018 at 7:00 pm
Discuss
0

You can only have one Change event procedure for any object (in this case the worksheet). The way to take different action is to differentiate btween the targets or even the Value of targets. For example, if Target.Address = "$D$1" you take one kind of action and if it is somewhere else you take another. Or, if Target.Value = "" you take one kind of action and if there is a Value you take another. You can also pile these concepts on top of each other, such as taking any of several actions if Target = D1, depending upon the Value found in D1, and similarly for if other cells were changed.

Observe that your code sheets have Option Explicit at the top. That is very useful because it helps detect typos nearly as you type. With Option Explicit as variables must be declared before use. That's how VBA knows which one is a typo. In your code you have a lot of undeclared variables. If you try to run it VBA will complain.

posted by: Variatus (rep: 4889) on Fri Aug 3, 2018 at 5:32 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login