Ask a new question

Excel: VBA Multiple Subs on one sheet

Good morning! I am attempting to run multiple subs on the same sheet to automate my workbook. Is there a way to create a reference sheet for each module, like the way you do for CSS and HTML? I'm extremely new to this language of code. My subs work independently, just not when I make them modules.

The main piece of code I'm working with:

Public Sub Worksheet_Change(MasterModule)

Call Module2
Call Module1

End Sub

Module1:      

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim Cchanged As Range, c As Range

    Const myVal As String = "FP" '<-- Change to suit

    Set Cchanged = Intersect(Target, Columns("A"))
    If Not Cchanged Is Nothing Then
        Application.EnableEvents = False
        For Each c In Cchanged
            If UCase(c.Value) = UCase(myVal) Then
                c.Offset(, 1).Value = myVal
            Else
                c.Offset(, 1).ClearContents
            End If
        Next c
        Application.EnableEvents = True
    End If
End Sub

Module2:

Public Sub Worksheet_Change(ByVal Target As Range)

    Dim Cchanged As Range, c As Range

    Const myVal As String = "GTSD" '<-- Change to suit

    Set Cchanged = Intersect(Target, Columns("C"))

    If Not Cchanged Is Nothing Then

        Application.EnableEvents = False

        For Each c In Cchanged

            If UCase(c.Value) = UCase(myVal) Then

                c.Offset(, 1).Value = "N/A"

            Else

                c.Offset(, 1).ClearContents

            End If

        Next c

        Application.EnableEvents = True

    End If

End Sub

Moved from Office / Excel / Windows 10 / Office 2010

Just to clarify, the subs are already separated out into there modules.  

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I prefer to keep mine in same worksheet module.

Example of 2 change events for one sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Macro1 Target      'event runs when cell in Column M is changed
    Macro2 Target      'event runs when cell in Column B is changed
End Sub

Private Sub Macro1(ByVal Target As Range)
'enter a number in column M and get number * 1.03  in Col N
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    Dim cRow As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    If Not Intersect(Range(Target(1).Address), _
            Range("M:M")) Is Nothing Then
        cRow = Target.Row
        With ws
            Target.Offset(, 1).Value = Range("M" & cRow).Value * 1.03
        End With
    End If
ErrorHandler:
    Application.EnableEvents = True
End Sub


Private Sub Macro2(ByVal Target As Range)
'enter text in column B and get sequential number in Col A
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    On Error GoTo endit
        For Each cel In Target
            If IsEmpty(cel.Offset(0, -1)) Then ' only when no number exists
                newNumber = Application.WorksheetFunction.Max(Range("A:A")) + 1
                cel.Offset(0, -1) = newNumber
            End If
        Next cel
    End If
endit:
End Sub

Gord

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated September 21, 2023 Views 9,191 Applies to: