+ Reply to Thread
Results 1 to 8 of 8

Workbook_BeforeSave function - how to work on mulitple sheets

  1. #1
    Registered User
    Join Date
    10-15-2018
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    14

    Workbook_BeforeSave function - how to work on mulitple sheets

    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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,138

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

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

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-15-2018
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    14

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

    Quote Originally Posted by 6StringJazzer View Post
    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!
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,138

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

    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.

  5. #5
    Registered User
    Join Date
    10-15-2018
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    14

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

    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!

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,138

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

    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.

  7. #7
    Registered User
    Join Date
    10-15-2018
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    14

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

    Ah okay!

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

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

  8. #8
    Registered User
    Join Date
    10-15-2018
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    14

    Re: Workbook_BeforeSave function - how to work on mulitple sheets

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. List from Mulitple sheets
    By DayofDreamz in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-09-2017, 07:38 AM
  2. Macro does not function properly when I run through all work sheets
    By Effat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2014, 12:05 PM
  3. [SOLVED] Can't get Workbook_BeforeSave () to work properly
    By duytt910 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2014, 06:03 AM
  4. Search function across multiple work sheets
    By evgn12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2014, 11:46 AM
  5. Urgent need help for work - Calucating Median wth Mulitple Parts and Prices
    By spuskas220 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-27-2012, 04:28 PM
  6. using count function from two different work sheets
    By abdulla saeed in forum Excel General
    Replies: 1
    Last Post: 01-16-2011, 04:16 PM
  7. Workbook.Save triggers event Workbook_BeforeSave and things don't work!
    By Dean Meyer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2006, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1