Unselect/Deselect ALL cells with vba

robert phillips · May 20, 2010

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

Tom Urtis · May 20, 2010

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

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

http://www.mrexcel.com/forum/showthread.php?t=469606

robert phillips · May 21, 2010

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?

robert phillips · May 21, 2010

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

AChrisA · Nov 12, 2011

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!

Jerry Sullivan · Nov 12, 2011

AChrisA said:
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

drsarao · Nov 12, 2011

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:

[B] TakeFocus*******[/B]

Set to False.

The asterix above are On Click (without space).
Somehow the Forum sftware is converting On Click without space to ******!
Moderator please

Jerry Sullivan · Nov 12, 2011

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.

AChrisA · Nov 12, 2011

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

Jerry Sullivan · Nov 12, 2011

AChrisA said:
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.

Unselect/Deselect ALL cells with vba

robert phillips

New Member

Excel Facts

Tom Urtis

MrExcel MVP

robert phillips

New Member

robert phillips

New Member

AChrisA

New Member

Jerry Sullivan

MrExcel MVP

drsarao

Well-known Member

Jerry Sullivan

MrExcel MVP

AChrisA

New Member

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

Unselect/Deselect ALL cells with vba

New Member

Excel Facts

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock