Get macro to work before moving sheets?

[danbates]

Hi,

I the following code that checks for any blank cells within the ranges and it works fine when calling it manually.

Sub CheckCells()

    Dim cell As Range
    Dim str As String
    Dim bIsEmpty As Boolean
    Dim blank As Range
    
Dim Daily, Daily1, Daily2, MultiDaily As Range

Set Daily = Range(Range("C" & Selection.Row), Range("F" & Selection.Row))
Set Daily1 = Range("H" & Selection.Row)
Set Daily2 = Range(Range("J" & Selection.Row), Range("L" & Selection.Row))

Set MultiDaily = Union(Daily, Daily1, Daily2)

For Each cell In MultiDaily
    If IsEmpty(cell) = True Then
    
    'THE 2 BELOW IS FOR THE HEADERS TO APPEAR IN THE MESSAGE BOX FROM ROW 2
            str = str & Cells(2, cell.Column).Value & vbCrLf
        bIsEmpty = True
    'FINDS THE FIRST BLANK CELL AND SELECTS
        For Each blank In MultiDaily
        If blank = "" Then
            blank.Select
            Exit For
        End If
    Next
    End If

Next cell
    
If bIsEmpty = True Then
MsgBox "PLEASE COMPLETE THE FOLLOWING BEFORE SIGNING OFF:  " & vbNewLine & vbNewLine & str, vbInformation, "Palletiser Operator"
    End If

End Sub

I would like to use it when I move to a different sheet so I added it to the Worksheet Deactivate sub but the code works on the sheet that I moved to and not the sheet I left.

What can I do to make the code work before leaving the sheet?

Thanks

Dan

 

[ajetrumpet]

that's happening because when you do this:

RANGE(cell, cell)

you're pointing to the active sheet only. in that case, you're using the RANGE function without a qualifying statement, such as:

activesheet.

if you want to run the code in a routine behind one sheet, but actually make the code manipulate the data in another sheet, you have to do this:

Set Daily = worksheets("sheet name here").Range(cell, cell)

but per your words:

What can I do to make the code work before leaving the sheet?

if you want to run the code on the current sheet before clicking on the next sheet name and putting the focus on that new sheet, you will need a button on the activesheet to run the code first, then move the focus to the new sheet.

 

[Fluff]

If you put the code in that Deactivate event, rather than just calling it from the event, then you should be ok.