Sheet specific macroing

[Bennettc18]

Hello I've been having trouble with one of the macros I have set up through VBA. There are 3 macros (all which are practically the same) for 3 different sheets on the same excel file. I was wondering what I should add to my code of what I should do to make my code work. It works if I put it in the workbook but I can't do that because the first sheet needs to input in more cells than the others.

This is my code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Application.EnableEvents = True

'Row 1

If Trim(Range("A2")) <> "" Then
    If Trim(Range("C2")) = "" Then
        Cancel = True
        Message = Message & "Cell C2 must be filled in" & Chr$(13)
    End If
    If Trim(Range("D2")) = "" Then
        Cancel = True
        Message = Message & "Cell D2 must be filled in" & Chr$(13)
    End If
End If

'Row 2

If Trim(Range("A3")) <> "" Then
    If Trim(Range("C3")) = "" Then
        Cancel = True
        Message = Message & "Cell C3 must be filled in" & Chr$(13)
    End If
    If Trim(Range("D3")) = "" Then
        Cancel = True
        Message = Message & "Cell D3 must be filled in" & Chr$(13)
    End If
End If

'Row 3

If Trim(Range("A4")) <> "" Then
    If Trim(Range("C4")) = "" Then
        Cancel = True
        Message = Message & "Cell C4 must be filled in" & Chr$(13)
    End If
    If Trim(Range("D4")) = "" Then
        Cancel = True
        Message = Message & "Cell D4 must be filled in" & Chr$(13)
    End If
End If

'Row 4

If Trim(Range("A5")) <> "" Then
    If Trim(Range("C5")) = "" Then
        Cancel = True
        Message = Message & "Cell C5 must be filled in" & Chr$(13)
    End If
    If Trim(Range("D5")) = "" Then
        Cancel = True
        Message = Message & "Cell D5 must be filled in" & Chr$(13)
    End If
End If

'Row 5

If Trim(Range("A6")) <> "" Then
    If Trim(Range("C6")) = "" Then
        Cancel = True
        Message = Message & "Cell C6 must be filled in" & Chr$(13)
    End If
    If Trim(Range("D6")) = "" Then
        Cancel = True
        Message = Message & "Cell D6 must be filled in" & Chr$(13)
    End If

End If

'Add more if needed

If Cancel = True Then MsgBox Message

End Sub

I can edit anything to help make this more clear. Please and Thank you!

 

[mole999]

Roughly

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   [COLOR="#FF0000"] Dim ws As Worksheet[/COLOR]
    Cancel = False
    Application.EnableEvents = True

[COLOR="#FF0000"]For Each ws In Sheets(Array("sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6"))[/COLOR]

    'Row 1

    If Trim(Range("A2")) <> "" Then
        If Trim(Range("C2")) = "" Then
            Cancel = True
            Message = Message & "Cell C2 must be filled in" & Chr$(13)
        End If
        If Trim(Range("D2")) = "" Then
            Cancel = True
            Message = Message & "Cell D2 must be filled in" & Chr$(13)
        End If
    End If

    'Row 2

    If Trim(Range("A3")) <> "" Then
        If Trim(Range("C3")) = "" Then
            Cancel = True
            Message = Message & "Cell C3 must be filled in" & Chr$(13)
        End If
        If Trim(Range("D3")) = "" Then
            Cancel = True
            Message = Message & "Cell D3 must be filled in" & Chr$(13)
        End If
    End If

    'Row 3

    If Trim(Range("A4")) <> "" Then
        If Trim(Range("C4")) = "" Then
            Cancel = True
            Message = Message & "Cell C4 must be filled in" & Chr$(13)
        End If
        If Trim(Range("D4")) = "" Then
            Cancel = True
            Message = Message & "Cell D4 must be filled in" & Chr$(13)
        End If
    End If

    'Row 4

    If Trim(Range("A5")) <> "" Then
        If Trim(Range("C5")) = "" Then
            Cancel = True
            Message = Message & "Cell C5 must be filled in" & Chr$(13)
        End If
        If Trim(Range("D5")) = "" Then
            Cancel = True
            Message = Message & "Cell D5 must be filled in" & Chr$(13)
        End If
    End If

    'Row 5

    If Trim(Range("A6")) <> "" Then
        If Trim(Range("C6")) = "" Then
            Cancel = True
            Message = Message & "Cell C6 must be filled in" & Chr$(13)
        End If
        If Trim(Range("D6")) = "" Then
            Cancel = True
            Message = Message & "Cell D6 must be filled in" & Chr$(13)
        End If

    End If

    'Add more if needed

    If Cancel = True Then MsgBox Message
    
[COLOR="#FF0000"]    Next[/COLOR]

End Sub

It could be something like this, though its untested

 

[Bennettc18]

Do you mind explaining the array?

The other two macros are practically the same just more ranges. So the one with the most range must work with Sheet1 only, macro 2 works for sheet2 only and same with macro 3 with sheet3.

Also where would I put this? In the sheet specific or ThisWorkBook?

Sorry I just started to learn VB.

 

[mole999]

The array is a set of objects, in this case the idea is to list all your sheets that have identical actions, and loop through each to the sheets one after another, so 1 to 6.

Because it addresses more than a single sheet you put it in a workbook module. I can make these work, but not great at explaining, your ranges should need to be ws.range to ensure that they refer to the correct sheet in the loop

If as I now suspect each macro is specific to a different sheet then don't do this, you can call each macro in sequence

sub updateworkbook
Macro1
Macro2
Macro3
End sub

 

[Bennettc18]

Ok I believe that calling each macro in sequence would probably work but how would I manage to link each macro to each sheet?

Also I know this is going to sound dumb but how do I even declare the code as a macro because currently it is a "Private Sub Workbook_BeforeSave"

So I don't entirely know what to do for this situation.

 

[mole999]

in a sheet you would use

Sub NameofMacro
End Sub

certain names have actions associated with the name "Private Sub Workbook_BeforeSave" is the workbook, Private makes it not visible to the list of macros that can be used

you can have worksheet_change events, setable from worksheets

maybe have a look at some basic YouTube sessions which can teach far more than a few typed words