Excel VBA "Unselect" wanted

9

1

I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything that was selected). Pupose: to present the client with a neat final appearance.

I've searched and cannot find how to do this. There is an Unselect according to MS, but it doesn't seem to do anything.

Gary

Posted 2012-09-03T22:07:34.943

Reputation: 91

Why don't you select cell A1 when you save to present a clean look. If you want to strip cell formatting that is a different thing. – wbeard52 – 2012-09-04T00:09:40.393

The cursor has to be somewhere. Why not place it bottom right with Worksheets("xxxx").Cells(Rows.Count,Columns.Count).Select or anywhere that is at least a screen away from the used area. – Tony Dallimore – 2012-09-04T08:16:50.670

could you just place it back where it was when the macro was called? – SeanC – 2012-09-04T15:45:32.357

Answers

10

Select any cell and turn off CutCopy:

  Range("A1").Select
  Application.CutCopyMode = False

user276802

Posted 2012-09-03T22:07:34.943

Reputation: 101

Yup, perfect for me. Thanks. I had a VBA macro that copied some cells from one workbook to another and then closed the source workbook, but because some cells were still selected for copy in the source it wouldn't close right away. Application.CutCopyMode = False fixed that for me. Thanks bud! – TKoL – 2016-12-29T10:10:41.050

Side effect: clears the clipboard. https://stackoverflow.com/a/17608260/733092

– Noumenon – 2019-03-05T03:38:55.387

2

Excel always has something selected. A work around is needed. Selecting a cell off screen will set focus there, so that won't work in and of itself. This code places the cursor off screen and then scrolls the sheet back up to view A1.

Sub NoSelect()    
  Range("BB100").Select
  ActiveWindow.SmallScroll up:=100
  ActiveWindow.SmallScroll ToLeft:=44
End Sub

If you are really wanting 'nothing selected`, you can use VBA to protect the sheet at the end of your code execution, which will cause nothing to be selected. You can either add this to a macro or put it into your VBA directly.

Sub NoSelect()
   With ActiveSheet
   .EnableSelection = xlNoSelection
   .Protect
   End With
End Sub

As soon as the sheet is unprotected, the cursor will activate a cell.

CharlieRB

Posted 2012-09-03T22:07:34.943

Reputation: 21 303

2

There is a tricky way to do it.

Create an object such as a button. Select this button, then hide it, and no cell will be selected.

ActiveSheet.Shapes("Button 1").Visible = True

ActiveSheet.Shapes("Button 1").Select

ActiveSheet.Shapes("Button 1").Visible = False

That's it.

user364941

Posted 2012-09-03T22:07:34.943

Reputation: 21

1

By turning off screen updating before selecting a cell, scroll to selected cells is temporarily disabled.

This code checks which cells are currently visible and selects the first cell below the visible range which is not in view. Eg, when i try it: Visble range is A1:BC79, so this code selects A80 and scrolling down and to the right and running it again reveals the excel chooses the first cell NOT visible below the first visible column.

Dim r As Range
Application.ScreenUpdating = False
Set r = Application.ActiveWindow.VisibleRange
r(r.Cells.Count + 1).Select
Application.ScreenUpdating = True

Tobias Carlén

Posted 2012-09-03T22:07:34.943

Reputation: 11

1

Very old question, but my answer for reference:

You can use

With ActiveSheet
    .EnableSelection = xlNoSelection
    .Protect
End With

Joost

Posted 2012-09-03T22:07:34.943

Reputation: 120

1

The only answer is to cheat.

Hide column A and row 1

Put cursor in A1. There will be a tiny dot in the corner of B2

SeanC

Posted 2012-09-03T22:07:34.943

Reputation: 3 439

or you could select a cell outside of the viewable range. – James Mertz – 2012-09-05T18:21:45.160

@KronoS, which at 10% zoom, and a 1280x1024 screen is ~HA375. perhaps XFD1048576 ? :) – SeanC – 2012-09-05T18:25:49.493

1

Depending on the size of your selection, to get rid of any artifacting (I don't know if this is also an issue in 2013 Excel, but on Mac it was a constant pain for me) you can just loop through cell by cell and select each.

Jason Pevitt

Posted 2012-09-03T22:07:34.943

Reputation: 11

0

Use a trick: Add a shape, then select it and hide it.

Source code for this is in answer of different question on this site.

miroxlav

Posted 2012-09-03T22:07:34.943

Reputation: 9 376