+ Reply to Thread
Results 1 to 16 of 16

Clear contents of all unlocked cells (many are merged cells)

  1. 05-28-2009, 12:22 PM #1
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Clear contents of all unlocked cells (many are merged cells)

    I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.

    Thanks
    Last edited by 2funny4words; 06-06-2009 at 10:17 AM.
    Reply With QuoteRegister To Reply
  2. 05-28-2009, 01:41 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Clear contents of all unlocked cells (many are merged cells)

    Hello 2funny4words,

    This macro will clear all unlocked cells, merged or not, on the active worksheet.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 05-28-2009, 05:30 PM #3
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    Leith,

    Thanks for your response. I am not familiar with union and am checking into it right now, but the code that you gave me throws an error that says argument not optional and union is highlighted. Do you have any ideas on what to change? I will post it if I figure it out before someone responeds.

    THANKS AGAIN
    Reply With QuoteRegister To Reply
  4. 05-28-2009, 05:33 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Clear contents of all unlocked cells (many are merged cells)

    The dot should be a comma:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 05-28-2009, 05:36 PM #5
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Clear contents of all unlocked cells (many are merged cells)

    Hello shg,

    Thanks for catching that.
    Reply With QuoteRegister To Reply
  6. 05-29-2009, 12:39 AM #6
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    Thanks for your help. I got rid of the error but nothing seems to happen. I will post the code to make sure that I did not change anything around. All I added was a message box and a specific cell to go to when complete with the clear content part. It goes to that cell but never clears all of the cells.
    Last edited by 2funny4words; 05-29-2009 at 12:45 AM.
    Reply With QuoteRegister To Reply
  7. 05-29-2009, 12:42 AM #7
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Clear contents of all unlocked cells (many are merged cells)

    This will clear any unlocked cells in the ActiveSheet
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  8. 05-29-2009, 11:26 AM #8
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    The last code worked, but only after I removed the message box.
    Please Login or Register  to view this content.
    This is the code with the message box and the message comes up but then never clears the contents. Evidently I have something messed up with the validation after the message but not sure what. Thanks again
    Reply With QuoteRegister To Reply
  9. 05-29-2009, 01:21 PM #9
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Clear contents of all unlocked cells (many are merged cells)

    Hello 2funny4words,

    To resolve this problem, you should post your workbook. All the code examples function correctly and if the code is still not working for you then the problem lies elsewhere.
    Reply With QuoteRegister To Reply
  10. 05-29-2009, 03:06 PM #10
    Cyclops
    Cyclops is offline
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    113

    Re: Clear contents of all unlocked cells (many are merged cells)

    Try changing the first part of the code to 


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 05-30-2009, 04:44 AM #11
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    Here is the Workbook
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 05-30-2009, 12:30 PM #12
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Clear contents of all unlocked cells (many are merged cells)

    Hello 2funny4words,

    Thanks for posting the workbook. It would have been very difficult to have to have written the macro without it. Here is the code. You can attach this macro to your button at the bottom of the form. It has been added to the attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 06-05-2009, 05:39 PM #13
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    Leith,

    This code works great as long as I don't have the message box coming up. Do I have something wrong with my message box? When the message box is in the code, it skips the clear contents part and just selects the first unlocked cell.

    Thanks for you input so far.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 06-06-2009, 02:57 AM #14
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Clear contents of all unlocked cells (many are merged cells)

    Hello 2funny4words,

    Simply call the macro to clear the fields when the proper "IF" condition is met.

    Button Code
    Please Login or Register  to view this content.
    Clear Form Macro
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 06-06-2009, 10:16 AM #15
    2funny4words
    2funny4words is offline
    Forum Contributor
    Join Date
    01-05-2009
    Location
    South Dakota, USA
    MS-Off Ver
    2003, and 2007
    Posts
    150

    Re: Clear contents of all unlocked cells (many are merged cells)

    Thanks Leith,

    I used the message box to call the clear form sub and it still did not do anything, so I played around with it and change the if statement from 
    Please Login or Register  to view this content.
    to 
    Please Login or Register  to view this content.
    and everything works great. Thanks for all of your help
    Reply With QuoteRegister To Reply
  16. 06-08-2009, 09:21 AM #16
    Cyclops
    Cyclops is offline
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    113

    Re: Clear contents of all unlocked cells (many are merged cells)

    Unless I'm missing something, it looks like the code will trigger regardless of whether the user clicks cancel or ok. 


    Please Login or Register  to view this content.
    will always trigger


    Please Login or Register  to view this content.

    will only trigger if the ok button is clicked.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1