Excel Macros

Hello Spike,

 

The best technique is to create a class module in VBA, put the two macros in it, and then create an instance of this class in a worksheet code space.

From that instance, you can get access to the macros and call any of one them based on a condition!

 

The class module's content is as follows (for example):

Public Sub MAC01()
MsgBox "The value of cell A3 is X."
End Sub

Public Sub MAC02()
MsgBox "The value of cell A3 is Y."
End Sub

 

The worksheet code space's content is as follows:

Sub Worksheet_Change(ByVal target As Range)

    Dim c1 As New Class1
    
    On Error Resume Next
    
    If target = Range("A3") Then
    
        If target.Value = "x" Or target.Value = "X" Then
            c1.MAC01
        ElseIf target.Value = "y" Or target.Value = "Y" Then
            c1.MAC02
        Else
        'Do nothing
        End If
        
    End If
    
    On Error GoTo 0
    
End Sub

This solution is based on some object-oriented programming concepts, and I'm happy to try that for the first time in VBA!

 

Please find the attached file.

 

Hope that helps

Haytham