I have created a form in Excel that is to be filled in by end users.
This form spans several worksheets. On each worksheet there are several
input fields that must be populated with data. (I've labelled these as
required, but you know how people listen). The data that will go into
these fields can be freeform alphanumeric data. I wanted to set a
validation control that would prompt the end user with an error message
should they try to save the workbook or navigate to another worksheet
without populating all of the required fields.
Can someone please help with this validation control?
Thanks


--
cboyko
------------------------------------------------------------------------
cboyko's Profile: http://www.excelforum.com/member.php...o&userid=33415
View this thread: http://www.excelforum.com/showthread...hreadid=532350

If you give the cells that should be filled in on each sheet the same range
name, it would make the code easier to implement.

For each sheet, I selected the range that needed to be filled in.
Then Insert|Name|Define
I gave it a worksheet level name of:
Sheet1!RequiredFields
(Include the sheet name, then an exclamation point, then RequiredFields)

You may need single quotes around the sheet name:
'Sheet 99'!requiredfields

Now you can use the workbook_beforesave event to look at those cells. This goes
under the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim testRng As Range
Dim wks As Worksheet
Dim myMsg As String

For Each wks In Me.Worksheets

Set testRng = Nothing
On Error Resume Next
Set testRng = wks.Range("RequiredFields")
On Error GoTo 0

If testRng Is Nothing Then
'skip that sheet
Else
If testRng.Cells.Count = Application.CountA(testRng) Then
'it's all filled in
Else
myMsg = myMsg & vbLf & wks.Name
End If
End If
Next wks

If myMsg = "" Then
'ok, all filled in
Else
myMsg = "Please fill in cells in" & myMsg & vbLf & "workbook not saved!"
Cancel = True
MsgBox myMsg
End If

End Sub

============
When you're setting up the workbook (as a developer), you'll want to save the
workbook without filling those cells.

You can do this before you save the file.
Hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)
type this and hit enter
application.enableevents = false

Now back to excel and save the file
Then back to the VBE (alt-f11)
and type this into the immediate window:
application.enableevents = true
and hit enter



cboyko wrote:

I have created a form in Excel that is to be filled in by end users.
This form spans several worksheets. On each worksheet there are several
input fields that must be populated with data. (I've labelled these as
required, but you know how people listen). The data that will go into
these fields can be freeform alphanumeric data. I wanted to set a
validation control that would prompt the end user with an error message
should they try to save the workbook or navigate to another worksheet
without populating all of the required fields.
Can someone please help with this validation control?
Thanks

--
cboyko
------------------------------------------------------------------------
cboyko's Profile: http://www.excelforum.com/member.php...o&userid=33415
View this thread: http://www.excelforum.com/showthread...hreadid=532350

--

Dave Peterson

Ps. I don't think I'd do something like this.

But I may put a nice big warning in an adjacent cell (bold red letters):

=if(a12<"","","<---Please fill in this cell!")



Dave Peterson wrote:

If you give the cells that should be filled in on each sheet the same range
name, it would make the code easier to implement.

For each sheet, I selected the range that needed to be filled in.
Then Insert|Name|Define
I gave it a worksheet level name of:
Sheet1!RequiredFields
(Include the sheet name, then an exclamation point, then RequiredFields)

You may need single quotes around the sheet name:
'Sheet 99'!requiredfields

Now you can use the workbook_beforesave event to look at those cells. This goes
under the ThisWorkbook module.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim testRng As Range
Dim wks As Worksheet
Dim myMsg As String

For Each wks In Me.Worksheets

Set testRng = Nothing
On Error Resume Next
Set testRng = wks.Range("RequiredFields")
On Error GoTo 0

If testRng Is Nothing Then
'skip that sheet
Else
If testRng.Cells.Count = Application.CountA(testRng) Then
'it's all filled in
Else
myMsg = myMsg & vbLf & wks.Name
End If
End If
Next wks

If myMsg = "" Then
'ok, all filled in
Else
myMsg = "Please fill in cells in" & myMsg & vbLf & "workbook not saved!"
Cancel = True
MsgBox myMsg
End If

End Sub

============
When you're setting up the workbook (as a developer), you'll want to save the
workbook without filling those cells.

You can do this before you save the file.
Hit alt-f11 (to get to the VBE)
hit ctrl-g (to see the immediate window)
type this and hit enter
application.enableevents = false

Now back to excel and save the file
Then back to the VBE (alt-f11)
and type this into the immediate window:
application.enableevents = true
and hit enter

cboyko wrote:

I have created a form in Excel that is to be filled in by end users.
This form spans several worksheets. On each worksheet there are several
input fields that must be populated with data. (I've labelled these as
required, but you know how people listen). The data that will go into
these fields can be freeform alphanumeric data. I wanted to set a
validation control that would prompt the end user with an error message
should they try to save the workbook or navigate to another worksheet
without populating all of the required fields.
Can someone please help with this validation control?
Thanks

--
cboyko
------------------------------------------------------------------------
cboyko's Profile: http://www.excelforum.com/member.php...o&userid=33415
View this thread: http://www.excelforum.com/showthread...hreadid=532350

--

Dave Peterson

--

Dave Peterson