Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Unselect/Deselect ALL cells with vba

This is a discussion on Unselect/Deselect ALL cells with vba within the Excel Questions forums, part of the Question Forums category; IF this is a duplicate post I apologize. I thought I just posted it but it seems it didn't take. ...

  1. #1
    New Member
    Join Date
    Sep 2007
    Location
    Westlake, Ohio
    Posts
    43

    Default Unselect/Deselect ALL cells with vba

    IF this is a duplicate post I apologize. I thought I just posted it but it seems it didn't take.

    I may have asked this question a few years ago but I lost the answer.

    When choosing a sheet tab activating the sheet, I don't want any cell to be selected/activated as would be evidenced by a cell 'selection cursor' being visible on the last cell the user picked during the last visit to that particular tab (sheet). So, nothing is hilighted/selected until a user actually picks a cell. (This is how a new sheet appears before a user has picked a cell for the first time.)

    VBA is no problem.

    I can't seem to find a method that more or less deselects/unselects all cells in a sheet.

    EXCEL 2000

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: Unselect/Deselect ALL cells with vba

    Why do you think "it didn't take"?

    It shows up here just fine for me, where you've had a response:

    Unselect/Deselect ALL cells with vba

  3. #3
    New Member
    Join Date
    Sep 2007
    Location
    Westlake, Ohio
    Posts
    43

    Default Re: Unselect/Deselect ALL cells with vba

    It appeared that the first post didn't take because I stopped midstream when I realized I needed to update my profile for a new email address. After updating address, I submitted the post. When I returned to the forum, the post wasn't there. This post is a differently worded version of the first post which didn't seem to appear in the forum. Clear?

  4. #4
    New Member
    Join Date
    Sep 2007
    Location
    Westlake, Ohio
    Posts
    43

    Default Re: Unselect/Deselect ALL cells with vba

    Tom
    BTW, thank you for your reply. It also seems that I confused the issue about the topic of the post. I believe I was thinking about the process of unhiliting after a copy/paste selection and confusing that with wanting to hide the current cell position in a sheet.

    Now that you straightened me out, this post topic is solved.

    However, regarding posting, I thought the first post was applied to this forum area. I don't see which forum area that first 'missing' post was submitted to although you have it. Where did I post it?

    rlp

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    3

    Default Re: Unselect/Deselect ALL cells with vba

    I am a beginner macro/VBA programmer.

    I have created a macro (associated with a button) that forces a formula to generate a new random number. It does this by invoking the “calculate” command (or whatever you call it VBA speak – does not seem like an object or a method). Here is the macro:
    ---------------
    Private Sub CommandButton1_Click()

    Calculate

    End Sub
    --------------

    Once this macro is done running, there are no cells selected.



    I have another macro (also associated with a button) in the same sheet that hides and unhides a row. Once the macro has been run, I don’t want the row or any cell to be selected (as is the case after the first macro above runs). I can assure that the row is not selected by adding a line to actively select a cell or range, but I haven’t been able to find a way to code it so no cells are selected. Here is the macro in its current form:

    -------
    Sub Button4_Click()
    Application.Calculation = xlManual
    With Rows("9")
    .Select
    .EntireRow.Hidden = Not .EntireRow.Hidden
    End With
    Range("E9:P9").Select
    End Sub
    --------

    Please let me know if you have a suggestion for how to code the macro so no cells are selected after a row is hidden or unhidden.

    Thanks!

  6. #6
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,952

    Default Re: Unselect/Deselect ALL cells with vba

    Quote Originally Posted by AChrisA View Post
    Please let me know if you have a suggestion for how to code the macro so no cells are selected after a row is hidden or unhidden.
    Welcome to the Board AChrisA,

    The reason that your Calculate macro leaves no cells selected is that CommandButton1 stays selected after it is clicked,
    since the macro doesn't have any statements that select cells on the worksheet.

    You would get the same effect with a procedure with no statements, like...
    Code:
    Private Sub CommandButton1_Click()
    End Sub
    If you wanted to apply that concept to your macro, you add a last step that selects a shape in your worksheet.
    The shape could be a rectangle for example that you could place in a hidden row or column.

    Code:
    Sub Button4_Click()
        Application.Calculation = xlManual
        With ActiveSheet.Rows("9")
            .EntireRow.Hidden = Not .EntireRow.Hidden
            .Parent.Shapes("Rectangle 1").Select
        End With
    End Sub
    A simpler approach might be to have your code select a Cell that isn't currently displayed on the screen,
    then scroll back to the saved display.
    Code:
    Sub SelectCellOutsideDisplay()
        Dim lScrollRow As Long, lScrollCol As Long
        Application.ScreenUpdating = False
        On Error Resume Next
        With ActiveWindow
            lScrollRow = .ScrollRow
            lScrollCol = .ScrollColumn
            With .VisibleRange
                .Offset(.Rows.Count + 10, _
                    .Columns.Count + 10).Select
            End With
            .ScrollRow = lScrollRow
            .ScrollColumn = lScrollCol
        End With
        Application.ScreenUpdating = True
    End Sub
    Using Excel 2013

  7. #7
    Board Regular drsarao's Avatar
    Join Date
    Sep 2009
    Location
    Delhi, India
    Posts
    1,107

    Default Re: Unselect/Deselect ALL cells with vba

    Another way is to make the command button NOT become selected.
    Go to Properties window of the Command Button. (Design mode->Right click button-->pProperties)
    Code:
     TakeFocus*******
    Set to False.

    The asterix above are On Click (without space).
    Somehow the Forum sftware is converting On Click without space to ******!
    Moderator please
    Last edited by drsarao; Nov 12th, 2011 at 04:17 AM.
    Excel 2007 Windows 7

  8. #8
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,952

    Default Re: Unselect/Deselect ALL cells with vba

    Hi drsarao,

    I believe AChrisA's question is how to make it so there are no worksheet cells selected after running the hide/show row 9 macro.

    I only explained about the selection of the button, because AChrisA was looking for a way to repeat the effect observed when running the Calculate macro, as a way to have no worksheet cells selected.
    Using Excel 2013

  9. #9
    New Member
    Join Date
    Nov 2011
    Posts
    3

    Default Re: Unselect/Deselect ALL cells with vba

    Thanks JS411 !
    What do I need to do to find existing macro-button shape names?
    Thx again for your help!

  10. #10
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,952

    Default Re: Unselect/Deselect ALL cells with vba

    Quote Originally Posted by AChrisA View Post
    What do I need to do to find existing macro-button shape names?
    If your button is a form control (and not an ActiveX control), you can find its name by right-clicking on the button. The name will appear in the Name Box that normally displays the active cell.

    If you select the form button shape throught the macro, it highlights it as if it were in edit mode. This doesn't look very good, which is why I suggested selecting a rectangle shape in a hidden cell.

    Would the SelectCellOutsideDisplay macro work for your situation?
    That seems the better approach to me.
    Using Excel 2013

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com