Macro Data Entry Validation

[Sandy]

Hi



I have a worksheet which is used solely for entering data, that data is then
moved to a list via a macro attached to a button. The data entry cell ranges
are as follows, (there are no formulas in any of these cells and the cells
are merged) "D2:I2,J2:O2,C3:F3,L3:M3,S3:T3,.etc..".



What I would like to do is:- when the button is clicked; if all the cells
have data entered then have my existing "Macro1" run, if however any of
these cells have no entries then have a message box pop-up saying, say,
"Incomplete Data Entry". So to me it would look something like this:-



Sub SaveRecord()



If



all cells in the ranges "D2:I2,J2:O2,C3:F3,L3:M3,S3:T3,.etc.." have data
entered - (This is the bit I don't know how to code)



Then



Macro1



Else



'Message'



End Sub



Your assistance is valued.



Sandy

 

[Guest]

Hi Sandy,

Try this solution:

Sub SaveRecord()

If emptyrng(Range("D2:I2,J2:O2, etc.")) Then

all cells in the ranges "D2:I2,J2:O2,C3:F3,L3:M3,S3:T3,.etc.." have data
entered - (This is the bit I don't know how to code)



Then



Macro1



Else



'Message'



End Sub

Function emptyrng(myrng As Range)
Dim cella As Range
emptyrng = False
For Each cella In myrng
If IsEmpty(cella) Then
emptyrng = True
Exit Function
End If
Next cella
End Function

Regards,
Stefi

 

[Sandy]

Hi Stefi

Works fine thank you, - only one thing though - I had to change my merged
cell references in all cases from (as an example) D2:I2 to simply D2 and
J2:O2 to just J2. Don't know why! Putting C6:M6 was ok however as all of the
cells in that range are un merged.

Apart from that - perfect - thanks again

Sandy

 

[Guest]

Sorry, I forgot that your cells are merged. That is quite normal, merged
cells are to be referenced with the first member cell reference!
Stefi


„Sandy†ezt írta:

 

[vezerid]

Sandy,

The following code snippets should help you:

Dim cell as Range, checked as Range

Set checked = Range("D2:I2,J2:O2,C3:F3,L3:M3,S3:T3")
For Each cell in checked
If cell.value = "" Then
msgbox "You need to enter a value in cell "&cell.Address
Exit Sub
Else
......run macro
End If
Next cell

If all your cell ranges are merged then only use the first cell in
each range, i.e. your Range construct should be:

Set checked = Range("D2,J2,C3,L3,S3,...")

HTH
Kostis Vezerides