Use the Workbook Level SheetActivate Event

Put code similar to this into the ThisWorkbook.Module of your workbook


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
set cInfo = Worksheets("Common_Info")
if sh.Name = cInfo.Name then exit sub
if Application.countA(cinfo.Range("A1,B9,C11,D12,E3") ) < 5 then
Application.Goto cInfo.Range("A1"),true
msgbox "Please complete the common info"
End if

End Sub

Of course, if the user disables macros, then the above will not run. So you
might want to explore setting up the workbook so sheets other than common
info are hidden and unhide these using code (so if macros are disabled, the
sheets are not unhidden). You would also need to have code that rehides the
sheets when the workbook is closed and saves the workbook after the sheets
are hidden.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"alistair" wrote in message
...
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.