Worksheet data entry form by Dave Peterson

[kev]

Hi Dave,

First of all, a great thanks to you for helping me numerous times in
making my project a successful one.
I am about to wrap it already. Just one final favour, this is something
that i realized only after the presentation,
The code ensures that we must fill in all cells before submitting add
to database. But in my input sheet, there are several cells that can be
left blank. How do i modify the code to suit my needs?


'cells to copy from Input sheet - some contain formulas
myCopy =
"D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33,D35,D37"

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

I am sure the answer lies in these two codings. The cells that can be
left blank are: D13,D15,D17,D19,D21,D23
I tried several ways but it only returned error in a way that all the
values entered after D23 is placed at D13 which was supposed to be
blank.

Pls help. Thanks a lot.
Really wish to settle this and close the project to a great weekend.

 

[Dave Peterson]

Declare another range variable that has to be filled in.

Dim AddrThatMustBeFilledIn as String
Dim RngThatMustBeFilledIn as range
....
AddrThatmustbefilledin = "d5,d7,whatevermoreyouneedhere"
....
then later...

set rngthatmustbefilledin = .range(addrthatmustbefilledin)

and then change that existing check with

If Application.CountA(Rngthatmustbefilledin) _
<> rngthatmustbefilledin.Cells.Count Then

 

[kev]

Thanks, it worked wonderfully.
Have a nice weekend.