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?

 

[NdNoviceHlp]

I'm not following... Why do you want to do this? The disappearance seems like an event which would retrigger that selection change thing again which is circular and XL doesn't seem to like this even if you try to avoid it. I've had a few run ins with the selection change event and personally I'm never going to use it again because of them. If you could add your purpose to this adventure perhaps there's an alternate route. Dave

 

[phantom1975]

The code I have works just fine without any circle problems (note the EnableEvents.) I have a worksheet that uses cells like CommandButtons to launch macros (except the Selection_Change event fires the macro.) I need to have zero cells selected after the user clicks into one of these cells so that the cell can be clicked again (if needed) without triggering a macro. Here's the whole macro that I have that does the job. I am just looking for a more efficient way to "hide" the active cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Application.EnableEvents = False
Me.Range("A10").Activate
ActiveWindow.LargeScroll Up:=1
Application.EnableEvents = True
If Target.Address = "$B$2" Then
Call Create_Logs
End If
If Target.Address = "$D$2" Then
Call Load_Logs
End If
If Target.Address = "$F$2" Then
Call Compile_Data
End If

End Sub

 

[NdNoviceHlp]

It doesn't seem like you're having much luck with this one. I know you were looking for simplification but what if your macro were to fire just by hovering the cursor over the cell rather than selecting the cell? If you make the cell a named range (range1 in this example) the following code will fire the test macro1. I don't know if this will be any use to you but at least it will give this thread a bump. Tom Urtis is to thank for most of this code here:
http://www.mrexcel.com/board2/viewtopic.php?t=146341&highlight=
HTH. Dave
Place this code in a module:

Option Explicit

Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Type POINTAPI
x As Long
Y As Long
End Type

Dim lngCurPos As POINTAPI
Dim TimerOn As Boolean
Dim TimerId As Long
Public oldColor As Long
Dim newRange As Range
Dim oldRange As Range

Sub StartTimer()

If Not TimerOn Then
TimerId = SetTimer(0, 0, 0.01, AddressOf TimerProc)
TimerOn = True
Else
MsgBox "Timer already On !", vbInformation
End If
End Sub

Sub TimerProc()
On Error Resume Next
GetCursorPos lngCurPos
Set newRange = ActiveWindow.RangeFromPoint(lngCurPos.x, lngCurPos.Y)
If newRange.Address <> oldRange.Address Then
Range("A1").Value = newRange.Address
End If
If Range("A1").Value = Range("range1").Address Then
Range("A1").Value = vbNullString
Call StopTimer
Call Macro1
Call StartTimer
End If
Set oldRange = newRange
End Sub

Sub StopTimer()
If TimerOn Then
KillTimer 0, TimerId
TimerOn = False
Else
MsgBox "Timer already Off", vbInformation
End If
End Sub

Sub Macro1()
MsgBox "This is a test"
End Sub

Place this in the sheet code:

Option Explicit

Dim TrgtColor As Long
Dim oldTarget As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = TrgtColor
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set oldTarget = Target
TrgtColor = oldColor

End Sub

Call Starttimer to begin and Stoptimer to end.

 

[phantom1975]

Yeah, that's a bit too much for my needs.

 

[erik.van.geit]

phantom,

and this?
whatever range is selected the window will stay unchanged

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim R As Long, C As Integer

R = ActiveWindow.VisibleRange().Row
C = ActiveWindow.VisibleRange().Column
With Application
.EnableEvents = False
.ScreenUpdating = False

Range("HZ1111").Select
ActiveWindow.ScrollRow = R
ActiveWindow.ScrollColumn = C

.ScreenUpdating = True
.EnableEvents = True
End With

Select Case Target.Address
Case "$B$2"
Call Create_Logs
Case "$D$2"
Call Load_Logs
Case "$F$2"
Call Compile_Data
End Select

End Sub

remarque: see "select case" instead of multiple ifs

kind regards,
Erik

 

[Justinlabenne]

Do you still want to be able to select cells? I would assume not because how would you know which was selected? Curious question. This locks down the sheet scroll and disables selection. The window stat needs toggled to make the outline selector disappear. Maybe?

Try this:

Option Explicit

Sub StopSheet()
'   Kill Selection
    With Sheet1
        .Range("A1").Select
        .ScrollArea = Range("A1").Address
        .EnableSelection = xlNoSelection
        .Protect , , , , True
    End With
    With ActiveWindow
        .WindowState = xlNormal
        .WindowState = xlMaximized
    End With
End Sub


Sub StartSheet()
'   Reset
    With Sheet1
        .Unprotect
        .Range("A1").Select
        .ScrollArea = Empty
        .EnableSelection = xlNoRestrictions
    End With
End Sub

 

[Nimrod]

Hello phantom1975 ... does this help ....

DISABLE CELL SELECTION..
To disable cell selections but still be able click command buttons ...
Application.DataEntryMode = xlStrict

ENABLE CELL SELECTION ..
To turn pointer system back to normal..
Application.DataEntryMode = xlOff

 

[phantom1975]

I gave up on CommandButtons years ago. Hee hee. I'm afraid that cell selection is a must in this case.

 

[Justinlabenne]

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...