MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Make macro run only if data in a particular cell


Posted by Lewis on November 09, 2001 9:41 AM

I have a macro which saves data from one worksheet to another. I want to set it up so that it will not run unless data is present in certain cells.

Any suggetstions gratefully received.

Lewis


Posted by Barrie Davidson on November 09, 2001 9:46 AM

You can try something like

If Range("A1").Value = "" Then Exit Sub
'rest of your macro


Which checks the value of cell A1 and, if it is blank (="") then it stops the macro.

Does this help you out?

BarrieBarrie Davidson

Posted by Lewis on November 09, 2001 9:58 AM

Thanks
Is it possible to get an error message like "please enter data in date feild" before it shuts down?


Lewis

Posted by Barrie Davidson on November 09, 2001 11:10 AM

Sure, in fact let's make it better by placing the user in that cell. This code will do that.

If Range("A1").Value = "" Then
MsgBox prompt:="Please enter data in date feild", Buttons:=vbOKOnly + vbCritical
Range("A1").Select
Exit Sub
End If

Regards,
BarrieBarrie Davidson

Posted by Lewis on November 09, 2001 12:43 PM

Thanks works a treat