validating a sheet before moving onto the next.

[Alistair]

Hi

I am sending a spread sheet out to branchs I need them to
complete a sheet (common info) before they do anything
else on the workbook. I am looking for a macro or code for
VBA which who check (common info) see if the cells are
complete if not bring up a error screen informing the user
to complete the form and then take them straight to the
(common info) sheet. and if possible let them go to the
tab which they want to go to.

 

[BrianB]

This shows the basic idea. There are all sorts of improvements possible.
It keeps taking the user back to the sheet if cells A1 or B1 are empty.
The code goes into the module for the worksheet - right click the tab
and select View code.


Code:
--------------------

Private Sub Worksheet_Deactivate()
If Worksheets("common info").Range("A1").Value = "" Then
MsgBox ("Cell A1 must be completed.")
Application.Goto Worksheets("common info").Range("A1")
ElseIf Worksheets("common info").Range("B1").Value = "" Then
MsgBox ("Cell B1 must be completed.")
Application.Goto Worksheets("common info").Range("B1")
Else ' data OK

End If
End Sub

--------------------

 

[Alistair]

Brian

Thanks a million, I really appreicate it it was just what
i was looking for I just extended it to check a few more
cells but else it is short and sweet and to the point.

cheers.

P.s do you mind if I credit you for it??

 

[hgrove]

If you only use excelforum to check newsgroups you may not be aware o
this, but omitting the original subject line screws up threading in th
Google Groups archives. Even though excelforum makes it harder than i
should be to include the original subject line, you should (even i
it's claimed to be optional).

BrianB wrote...

This shows the basic idea.

...

And it's easily broken whenever users disable macros. Better to buil
downstream formulas to check for valid inputs, and add formula-base
cell error messages. It may seem old fashioned, but it works even whe
macros are disabled