+ Reply to Thread
Results 1 to 11 of 11

How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

  1. 12-20-2014, 11:22 AM #1
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Hi All and Happy Holidays,

    I have problem that I've tried various code to solve, but can't seem to get anything to work.
    The copy of code, in attached Word Doc (sorry if this is a problem) is from one of my module in VB Editor.

    All the different ranges are selected on each of the budget sheets ("JAN BUDGET", etc.), then the code clears all data from each range on each sheet.

    However, after the code runs, only the ranges on the "JAN BUDGET" sheet is deselected.
    I suppose because I select A14 on this sheet, as the last line

    But how do I get ranges on all the other budget sheets to also deselect,
    but at the end have cell A14 on JAN BUDGET was the active cell?

    Thanks for your help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-20-2014, 11:35 AM #2
    berlan
    berlan is offline
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Hi catnam, see if this works (untested):
    Please Login or Register  to view this content.
    Kind regards,
    berlan
    Reply With QuoteRegister To Reply
  3. 12-20-2014, 11:50 AM #3
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Thanks Berlan,

    I tried the code, but the application is asking me for a password. The sheets are password protected.
    So some how I need unprotect, apply your code and then re protect.

    If you will, please add code line(s) that will unlock the sheets (they all will have same password), then re-protect after code run.

    You can just put "XXXXX" where password go and I'll enter real password to see if it works.
    Last edited by catnam; 12-20-2014 at 11:53 AM.
    Reply With QuoteRegister To Reply
  4. 12-20-2014, 12:02 PM #4
    berlan
    berlan is offline
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Okay, add the line in red and it should work.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 12-20-2014, 12:26 PM #5
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    It appears the code is infact deselecting all the ranges on all the sheets as I need. However, after it run, all cells on all sheets are locked.
    I think somehow, the line "ws.Protect UserInterfaceOnly:=True", when it protect, it protects and lock all cells, even unlocked cells.

    Can we somehow remove that line and use the following: ws.Unprotect "XXXX" to unprotect and allow macro to run, then somewhere use: ws.protect "XXXX" to protect after macro run.

    I see where to put the ws.unprotect, so if think it will work, where can I put the ws.protect "XXXX" to reprotect sheets after macro run.
    Reply With QuoteRegister To Reply
  6. 12-20-2014, 12:42 PM #6
    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: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    I'm not sure what you mean by "deselect"
    There is always a selection. Selection is never Nothing.
    All of the cells of Selection are on the same sheet.

    And, I'm not sure why one would bother "deselecting".
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  7. 12-20-2014, 12:50 PM #7
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Mikerickson, Check out my original post and attachment, I explain what I'm trying to do, then perhaps you can help. I appreciate your effort.
    Last edited by catnam; 12-20-2014 at 02:41 PM.
    Reply With QuoteRegister To Reply
  8. 12-20-2014, 01:22 PM #8
    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: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    What do you mean by deselect?
    How does one deselect a cell.

    I know of nothing related to excel that is called "de selection"?

    What do you mean by the term?

    Attaching a workbook would be much more informative than attaching a text file of code.
    Reply With QuoteRegister To Reply
  9. 12-20-2014, 01:55 PM #9
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Mikerickson, Since you failed to read my original post and attachment, to understand the problem, you are in no position to help. Perhaps you should read the whole thread. Take a look at what berlan had to say. Otherwise, you are not helping and it is best you simply skip this post. Thanks you.
    Last edited by catnam; 12-20-2014 at 02:40 PM.
    Reply With QuoteRegister To Reply
  10. 12-20-2014, 02:03 PM #10
    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: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    I've read your post, I've read your code.

    Your OP asks "But how do I get ranges on all the other budget sheets to also deselect"
    What does "deselect" mean?
    Nowhere in any Excel documentation from MS, can I find the word "deselect" in relation to cells.
    Reply With QuoteRegister To Reply
  11. 12-20-2014, 02:12 PM #11
    catnam
    catnam is offline
    Registered User
    Join Date
    03-18-2010
    Location
    Layton, UT
    MS-Off Ver
    Excel 2013
    Posts
    73

    Re: How to Deselect Multiple Ranges on Multiple Sheets After Select & Clear

    Berlan,

    I change the line in red to: ActiveSheet.Unprotect Password:="XXXXX"

    Changed: ws.EnableSelection = xlNone to ws.EnableSelection = xlUnlockedCells

    And I deleted: ws.Protect UserInterfaceOnly:=True

    I'm using Excel 2013

    Everything working perfect now. Thanks for your help.
    Last edited by catnam; 12-20-2014 at 02:41 PM.
    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)

Similar Threads

  1. Clear multiple ranges value
    By sourgreen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2014, 06:53 PM
  2. VBA to clear multiple ranges from list of sheets
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 10:12 AM
  3. Compiling multiple ranges of cells from multiple sheets into one grand table ?
    By teacher_rob in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 11:42 AM
  4. Clear Multiple sheets
    By kithing in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2010, 01:48 PM
  5. [SOLVED] in charting, how do i select data ranges from multiple sheets, sa.
    By michael in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-06-2005, 11:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1