How to activate a code only on certain sheets

[mastero87]

Hi,
I am totally new to the forum and to VBA as well. I am preparing an Excel file and for certain functions I need macros, so I looked up one on the forums modified it a bit, but I still need to do something that exceeds my knowledge (converging to zero anyway). I have decided to learn a bit of VBA later on but as this matter is urgent I don't have enough time to do it all by myself.
Here's the thing.

It's basically a form with fields to fill in. Some of these are mandatory ones, so I entered a macro for that (see below). What I need is a little addition. There are 4 sheets in the form and the client will only fill in one of them in most of the cases. However, the macro entered, which pops up upon saving (saying that all mandatory cells need to be filled in) watches the whole file with all 4 sheets. I would like something, with which this macro with the pop-up is only activated on the sheets that are being used/filled in. Something like "if at least one cell is filled in on sheet x, activate macro). Or something that fits the purpose. Here's the code so far. I appreciate all the help.

Please Login or Register  to view this content.

[ragulduy]

probably something like

Please Login or Register  to view this content.

[mastero87]

Hi yudlugar,

Thanks for the help. I tried what you suggested but it doesn't seem to work. A dialogue window pops up saying "Runtime error 438, object doesn't support this property or method." What might I have done wrong?

[ragulduy]

No idea, I just double checked the code and I don't get an error, can you upload your workbook and/or post the code you tried?

To add an attachment, when replying click go advanced and then attachments.

[mastero87]

The file is confidential but here is the code for the first sheet. As I am a total noob to this, I tried the whole line you suggested and the two conditions separately. Really need to learn this stuff later...

Please Login or Register  to view this content.

edited: I have deleted an extra "End If" inserted here by accident, it is not like that in VBA.

[ragulduy]

You need UsedRange.Cells not UsedRangeCells

Please Login or Register  to view this content.

[ragulduy]

Note, I don't think what the code you are using will do what you want. Cellcontents will be equal to the value of B13 only, the values of the other cells are irrelevant

[mastero87]

Ouch, forgot that. Thanks, I'll try again.

[:) Sixthsense :)]

May be try like this... (Untested Code...)

Please Login or Register  to view this content.

[mastero87]

Hi Sixthsense

Thanks for the suggestion. I've copy-pasted your code but it doesn't seem to work, upon trying to save the file, it says "Run-time error 13: Type mismatch" and it highlights the line Set vMyRng(1)... as wrong.

[:) Sixthsense :)]

Try this revised and tested one....

Please Login or Register  to view this content.