I want my worksheet to have ZERO cells selected

[phantom1975]

What I am trying to do is set up a Selection_Change event that will make the selection thingy disappear. The only thing I can think of is something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Application.EnableEvents = False
Me.Range("A10").Activate
'Rows 10 and below are hidden
ActiveWindow.LargeScroll Up:=1
Application.EnableEvents = True

End Sub

I'm sure there is a better way to do this. Any suggestions?

 

[phantom1975]

Out of curiosity, if you need to select cells, but have no "cell selector" how would you tell what cell you on. Im interested in what your after here, for what purpose, seems crazy, but interesting...

Let's say the user clicks cell A1 to run the macro for that cell. What if the user wants to run the macro again? The user would have to click out of cell A1 and then click it again. Too cumbersome. The other reason for this is for aesthetics. I could have the macro simply select another cell after the macro has run (in my example, cell A10) so that the user can easily click A1 again. But then there is the matter of that ugly highlighted cell there.

 

[Tom Urtis]

If that's the effect you want to achieve, then you can try this, following these steps in order:

(1)
Comment out any existing Selection event code in your worksheet module.

(2)
Draw a small ActiveX Label control on a cell whose column or row you can hide, for this example put the label within the confines of cell J2. Note, "small" means small, and "within" means within. Make the label tiny in size so it easily does not touch any of the cell's borders.

(3)
Go into design mode, right click on the Label control, left click on Format Control, and on the Properties tab select "Move and size with cells". Also, deselect "Print object".

(4)
Click onto any cell to deselect the Label. Exit design mode. Hide column J.

(5)
Amend and uncomment your existing Sheet selection code per the following syntax:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox "Your code"

Application.EnableEvents = False
Range("J1").Select
Application.EnableEvents = True
ActiveSheet.Label1.Select
End Sub

(6)
You will notice the formula bar will contain the script
=EMBED("Forms.Label.1","")
while the label is selected. You can ameliorate that by either
- hiding the formula bar
- protecting the sheet, making sure to adjust the codde accordingly
- ignoring the formula bar

 

[Justinlabenne]

Where do you come up this stuff? ray:

 

[phantom1975]

Pretty much what I am doing, but with a form instead. I like it!

 

[erik.van.geit]

phantom,

Glad you've got a solution by now;
This post just to say your expression sounds "lovely"

ugly highlighted cell

kind regards,
Erik