Workbook_BeforeSave function - how to work on mulitple sheets

[JoakimBugge]

Hi all,

I have this code to prevent users from closing a workbook before certain cells are filled:

Please Login or Register  to view this content.

Is there any way of getting this to work on the different sheets in the workbook?

Say if the active sheet is sheet 1, then cells A6 & A7 needs to be filled - if the active sheet is Sheet3, Cells C3 & C4 needs to be filled.

Hope you can help.

Br,
Joakim

[6StringJazzer]

Compiled but not tested. If you have trouble with this, please provide a sample workbook.

Please Login or Register  to view this content.

[JoakimBugge]

Compiled but not tested. If you have trouble with this, please provide a sample workbook.

Please Login or Register  to view this content.

When i enter this code i get an "compile error; Else without if".

I have attached my sheet here.

In Sheet 1 i want the cells K408:K409 to be filled before closing and on sheet 2 i want cells K206:207.

Thanks!

[6StringJazzer]

Sorry, not sure how this got by. The "Else" should be "Case Else". I corrected this, tested it, and it works.

Please Login or Register  to view this content.

[JoakimBugge]

Thanks!! Works great!

But now i wanted to add two additional sheets to do the same:

Please Login or Register  to view this content.

Works great and the message with the unfilled cells pop up - BUT when I press OK i get this error message in VBA:
Attachment 642824

debuggeing to this:

Attachment 642825

Can you help with this - then i will be one happy guy!

Thanks a lot!

[6StringJazzer]

Unfortunately we have some hiccups in image attachments. Yours are invalid. I have found that it will always work if you first Go Advance and Preview your post before you start to add the image attachments.

OTOH you could just describe what the error says, error code and line of VBA where it stops.

[JoakimBugge]

Ah okay!

Errer says 'Select method of range class failed' error '1004'.

It stops at line 3 from the bottom 'Rng2.Select'

[JoakimBugge]

Sorry - i was the fool here.. Did a typo - it's working perfectly now!
Thanks a bunch!!