validating macro

[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.

 

[Bob Flanagan]

Alistair, it is very likely that you will not get a response to your
request. Typically one posts a specific problem for solution versus a
request for a complete macro.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Tom Ogilvy]

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.

 

[Guest]

Thank you Rob will keep this in mind. My problem has been
sorted thank you very much.