PDA

View Full Version : [SOLVED] Worksheet_SelectionChange(ByVal Target As Range)



Nick72310
04-14-2016, 02:22 PM
I need help with defining my ranges. When I use the code shown below, it will not follow my if statement based on the selected cell.


'Status Bar Display
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

If Intersect(Target, Range(Cells(4, 2), Cells(Rows.Count, 3))) Then
sr = Selection.Row
sc = Selection.Column
inquiry = Cells(sr, sc)

Select Case Sheets("Hide2").Cells(sr, sc)
Case False
Application.StatusBar = inquiry & " : does not exist."
Case ""
Application.StatusBar = ""
Case Else
MyDesc1 = Sheets("Hide2").Cells(sr, sc)
Application.StatusBar = inquiry & " : " & MyDesc1
End Select

ElseIf Intersect(Target, Range(Cells(3, 4), Cells(Rows.Count, Columns.Count))) Then
Application.StatusBar = "Testing"
End If

End Sub

Paul_Hossler
04-14-2016, 02:25 PM
I usually do something like this since Intersect returns a Range or Nothing



If Not Intersect(Target, Range(Cells(4, 2), Cells(Rows.Count, 3))) Is Nothing Then

SamT
04-14-2016, 08:34 PM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Turn offSstatusBar on every selection change
Application.StatusBar = ""

'Almost always ignore multiple cell selections
If Target.Count > 1 Then Exit Sub

'Call Inquiry?
If Target.Row >= 4 And (Target.Column = 2 Or Target.Column = 3) Then Inquire Target

'Call TestStatusBar?
If Target.Row >= 3 And Target.Column >= 4 Then TestStatusBar

End Sub


Private Sub Inquire(Target As Range)

With Sheets("Hide2")
Select Case .Range(Target.Address)
Case False
Application.StatusBar = Target.Value & ": does not exist."
Case Else
With .Range(Target.Address)
MyDesc1 = .Value
Application.StatusBar = Target.Value & ": " & .Value
End With
End Select
End With

End Sub


Private Sub TestStatusBar()
Application.StatusBar = "Testing"
End Sub

Paul_Hossler
04-15-2016, 06:51 AM
And get rid of that



On Error Resume Next



at the beginning which just hides any errors

If I have to use it (and there are good reasons to), then I have it in effect for a brief a time as possible, before I have On Error Goto 0, typically only 1-3 lines

You could have found your error right away

15927

Nick72310
04-15-2016, 07:00 AM
Thanks for the help! I went with SamT's code. Seems to be doing the trick, and I can add more cases if needed.