Deselecting cells

[AJS]

Hi!

Is there any way of deselecting the currently selected cells, i.e. setting a workbook such that no cells are selected? I've got my Sheet1 set up as protected, such that no cell may be selected by the user. When I run some of my macros that change some of the cells in sheet1, they leave a cursor after they have finished running. Whenever such a macro runs, I include the code:

Sheet1.Protect UserInterfaceOnly:=True
Sheet1.EnableSelection = xlNone

It's a fairly minor point, more for aesthetic reasons than anything...

[johnske]

?? Do you mean when they select a sheet they can "look at it" only and there is no cells selected while they're viewing it ??

You could put your own code in the sheet(s) you want to appear that way if that's what you want. e.g.

Private Sub Worksheet_Activate()
Sheet1.Protect UserInterfaceOnly:=True
Sheet1.EnableSelection = xlNone
End Sub

[AJS]

Yep, but any cells selected by macros still appear as selected. Like I wrote earlier, it's a query more for aesthetic reasons than anything...

[Zack Barresse]

Hello AJS,

Another option, although I don't know how your worksheet is setup and what your routine is like, would be to make column A hidden and have your routine select A1 (or any row) when done. I realize how speculative this is also.

[Ken Puls]

I could be corrected on this, but I thought that there must always be at least one cell selected when you activate a sheet. Even with a new sheet, cell A1 is always selected.

Could you just select a single cell as the last line of your macro?

[johnske]

I still don't understand AJS, I inserted your code as the "last line" in a macro of my own and it did what it should i.e. protected the sheet and 'deselected' all cells.

If you meant to not have cells selected while the macro is actually running - I don't think it's possible to do this...

[AJS]

Hi,

I think you may be right kpuls. If any cells are selected when I load up the workbook, such a selection must be hidden or similar. My sheet is always saved in its protected mode, which I guess has something to do with this. However, the cell E4 appears as selected after running some macros which don't select this or any cells, which I guess means that E4 was selected on startup but that this selection had been hidden from view until then. Does anyone know how I might re-hide this selection?

[AJS]

Hi johnske,

I've tried using the aforementioned code as the last line in my macros, but it doesn't deselect/hide the selection for me!

[Jacob Hilderbrand]

I just tried the code here:

Sheet1.Protect UserInterfaceOnly:=True 
    Sheet1.EnableSelection = xlNone

And after it ran there were no cells selected.

[johnske]

Perhaps you're inadvertently selecting the wrong sheet then, try this as your last line instead

ActiveSheet.Protect UserInterfaceOnly:=True
ActiveSheet.EnableSelection = xlNone

[AJS]

Hey Johnske, DRJ,

Both work, sorta. E4 is still selected, but when I change sheets and change back again, the selection disappears, a la:

Sheet1.Protect UserInterfaceOnly:=True 
Sheet1.EnableSelection = xlNo
Sheet1.Select

This works also... a little inelegant but I'll take it. Any ideas why I'm having this problem?

[Anne Troy]

AJS: As a project manager, I consistently had the guys code worksheet activation events to put the selection at cell a1, and I consistently had them hide column A. firefytr has already suggested this. Unless there's something new in newer versions, and you're using the newer versions, then I'd go that route and save yourself the heartache of trying to figure out another way.

[AJS]

Yep, probably a good idea dreamboat, firefytr. I've just realised that the cell F4 is completely hidden by an oversized button, so I might park the cursor there...

[Anne Troy]

There ya go.