Code to ensure all cells are completed before running MACRO

manmah

Board Regular
Joined
May 11, 2009
Messages
70
Hi There

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

Cheers
Mark
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Hi Mark

Here's a function that you can call in your routines:
Code:
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
Code:
Sub Example()
    If FormComplete(Range("A1:B4")) Then
        MsgBox "OK - run macro"
        'run macro
    Else
        MsgBox "Emplty cells, halt!"
    End If
End Sub
 

manmah

Board Regular
Joined
May 11, 2009
Messages
70
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

Board Regular
Joined
May 11, 2009
Messages
70
Just read it all again, sorry that was a daft question.. works brilliant thanks very much....
Mark
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,322
Messages
5,675,077
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top