Code to ensure all cells are completed before running MACRO

[manmah]

Hi There

Does anyone know a code I can use which will ensure ALL unlocked cells are completed before running a MACRO???

Cheers
Mark

 

[Jon von der Heyden]

Hi Mark

Here's a function that you can call in your routines:

Public Function FormComplete(ByVal rngCells As Range) As Boolean
    Dim rngCell As Range
    
    FormComplete = True
    
    For Each rngCell In Intersect(rngCells, rngCells.Parent.UsedRange)
        If Not rngCell.Locked Then
            FormComplete = CBool(Len(rngCell.Value))
            If Not FormComplete Then Exit Function
        End If
    Next rngCell
End Function

How to use

Sub Example()
    If FormComplete(Range("A1:B4")) Then
        MsgBox "OK - run macro"
        'run macro
    Else
        MsgBox "Emplty cells, halt!"
    End If
End Sub

 

[manmah]

Thanks for that but one question...Am I correct in thinking that these are for 2 different scenarios, first one for unlocked cells and the other for set cells???

Cheers
Mark

 

[manmah]

Just read it all again, sorry that was a daft question.. works brilliant thanks very much....
Mark

 

[Jon von der Heyden]

No. The first code is a function that takes a range argument. If any cell within that range is unlocked AND empty then the function returns False. If there are no unlocked cells, or if all of the unlocked cells contain something then it returns True.

The second code demonstrates how to use the function. So in this routine you pass the range that you want to evaluate to the FormComplete function. If FormComplete returns False then you instruct it not to run the main code, otherwise allow it to run the main code.

 

[Jon von der Heyden]

Just read it all again, sorry that was a daft question.. works brilliant thanks very much....
Mark

Haha, bit of cross-comunication.
Glad you got it; good luck!