+ Reply to Thread
Results 1 to 6 of 6

Deselect selection if it's not within a defined range

  1. #1
    Registered User
    Join Date
    02-09-2013
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Deselect selection if it's not within a defined range

    I have a defined range of cells (C1:C10) that I would like the user to select. But, if the user selects any other cell outside the defined range, I would like to de-select that cell and select cell C1. If the user's selection is within C1:C10, then I should leave the user's selection as it is. Here is what I have so far, but I am getting an error:

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Deselect selection if it's not within a defined range

    Please Login or Register  to view this content.
    Why is this code in the "ThisWorkbook" module instead of the sheet where you have defined the range? As it is, if your workbook have multiple sheets, the code will be triggered when changing from sheet to sheet and whenever a new cell is selected on any sheet. Is that what you want? or maybe:
    Private Sub Worksheet_Change(ByVal Target As Range)
    instead of Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Last edited by protonLeah; 03-29-2013 at 02:21 AM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    02-09-2013
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Deselect selection if it's not within a defined range

    Thank you so much for the solution. It exactly what I wanted.

    I do have multiple sheets in my workbook, and I do want this rule to apply to all the worksheets, except one. So, I used a For...Next loop to exclude the worksheet to which I don't want this rule to apply.

    I have Private Sub Workbook_SheetSelectionChange (ByVal Sh As Object, ByVal Target As Range) to catch changes to cell values. When a cell value is changed or deleted, it triggers a user for to apply a cell comment.

    Here is what I have:
    Please Login or Register  to view this content.
    1. Do you have any suggestion on how else I can approach this?
    2. Regarding my earlier question, rather than having C1 automatically selected when the user clicks outside C1:C10, is it possible to have no cells selected at all when the user clicks outside C1:C10? I am tracking the changes being made to each cell using the code I provided above (I didn't give you the entire thing above, because it's messy). When C1 is automatically selected, it's tracking that as a change.

  4. #4
    Registered User
    Join Date
    02-09-2013
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Deselect selection if it's not within a defined range

    Accidently posted it twice somehow. See above.
    Last edited by Shanthan; 03-30-2013 at 05:42 AM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Deselect selection if it's not within a defined range

    I don't think it's possible for a worksheet to have no cells selected, there will always be at least one.

  6. #6
    Registered User
    Join Date
    02-09-2013
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Deselect selection if it's not within a defined range

    I got around the second issue I mentioned above by re-arranging how the code is executed.

    I noticed another issue:

    (3) I can also double-click on a column and row to select the entire column or row. When I close the workbook, while having this selected, I get Runtime error 13 Type Mismatch at
    Please Login or Register  to view this content.
    most likely because
    Please Login or Register  to view this content.
    does not have a value. How can I get around this? I don't want to stop the user from being able to click and highlight a column or row.
    Last edited by Shanthan; 03-30-2013 at 05:50 AM.

  7. #7
    Registered User
    Join Date
    02-09-2013
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Deselect selection if it's not within a defined range

    I figured out how to get around issue #3:

    Please Login or Register  to view this content.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1