+ Reply to Thread
Results 1 to 4 of 4

Macro to lock all the Non Blank cells after saving the workbook.

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    65

    Exclamation Macro to lock all the Non Blank cells after saving the workbook.

    Gentlemen,

    I have been searching for a macro that would lock all the non blank cells (cells that have any value/text) after the workbook is saved.

    When the user clicks on save a message box should prompt stating that "The data you entered has been locked successfully"

    Thank you for your support.


    Regards
    Adi

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Macro to lock all the Non Blank cells after saving the workbook.

    try the following macro:
    Please Login or Register  to view this content.
    This is a Workbook_BeforeSave event macro that will run every time the user saves the workbook (it runs just before the workbook is actually saved, hence the name). It must be written in "ThisWorkbook" object to function properly.

    HTH!

  3. #3
    Registered User
    Join Date
    06-07-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Macro to lock all the Non Blank cells after saving the workbook.

    Hello Jewel,

    I have 2 questions:
    1.) While saving the workbook I get a popup message to save it in .xlsm format. Is there any way where the macro can work in .xlsx format?
    2.) When running the macro I am getting a 'Run-time error "438": Object doesn't support this property or method' and the following code gets highlighted yellow in macro "For Each ws In Me"
    3.) Is this macro compatible with MS Excel 2010.

    Appreciate your time and response. Thank you.

    Adi.

  4. #4
    Registered User
    Join Date
    06-07-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Macro to lock all the Non Blank cells after saving the workbook.

    Hello Jewel,

    I have 2 questions:
    1.) While saving the workbook I get a popup message to save it in .xlsm format. Is there any way where the macro can work in .xlsx format?
    2.) When running the macro I am getting a 'Run-time error "438": Object doesn't support this property or method' and the following code gets highlighted yellow in macro "For Each ws In Me"
    3.) Is this macro compatible with MS Excel 2010.

    Appreciate your time and response. Thank you.

    Adi.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Macro to lock all the Non Blank cells after saving the workbook.

    1) You cannot have macros in .xlsx format; it has to be in .xlsm format. As soon as you save a file with .xlsx extension all code/macros are lost.
    2) replace the highlighted line with the following
    Please Login or Register  to view this content.
    HTH!

    PS: Sorry for the late reply. I was away for a few days.
    Last edited by jewelsharma; 08-17-2014 at 08:35 PM.

+ 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. Vba code to lock a range of cells before saving
    By imanonymous in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2013, 07:12 AM
  2. Replies: 0
    Last Post: 09-07-2012, 10:48 AM
  3. Replies: 2
    Last Post: 02-22-2012, 10:10 AM
  4. Renaming Active.Workbook and saving it without saving Macro
    By Djwill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2012, 03:09 AM
  5. Lock a row with an entry in Col B on saving or closing workbook
    By glenin in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-17-2009, 04:15 AM

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