I am trying to customize my status bar using "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". Each column has a separate message I would like to convey to the user. My code is shown below, but it is not coded very well and it's obviously not working correctly. I'm a little confused what to do with all the different "ifs" for the columns. I would appreciate some help. Thank you!
Some cells may be blank, so if there was a way to exclude those, that would be great!
'Status Bar Display
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$C$2:$M$3" Then
Application.StatusBar = "Created by: ..."
Exit Sub
End If
If Intersect(Target, Range("B9:B28,E9:F28,I9:L28")) Is Nothing Then Exit Sub
sr = Selection.Row
sc = Selection.Column
Inquiry = Cells(sr, sc)
MyDesc1 = Application.WorksheetFunction.VLookup(Inquiry, [PN_STATUS], 2, 0)
MyDesc2 = Application.WorksheetFunction.VLookup(Inquiry, [SUPPLY_TYPE], 2, 0)
MyDesc3 = Application.WorksheetFunction.VLookup(Inquiry, [UOM], 2, 0)
MyDesc4 = Application.WorksheetFunction.VLookup(Inquiry, [MAKE_BUY], 2, 0)
Application.StatusBar = Inquiry & " - " & MyDesc1 & MyDesc2 & MyDesc3 & MyDesc4
If Intersect(Target, Range("E9:F28")) Is Nothing Then Exit Sub
If IsEmpty(Range("$E$9:$F$28").Value) = False Then
sr = Selection.Row
sc = Selection.Column
Inquiry = Cells(sr, sc)
MyDesc5 = Application.WorksheetFunction.VLookup(Inquiry, [OIB], 8, 0)
Application.StatusBar = Inquiry & ": Quantity On-Hand=" & MyDesc5
End If
If Intersect(Target, Range("D9:D28")) Is Nothing Then Exit Sub
If Range("D9:D28").Value = PP Then
sr = Selection.Row
sc = Selection.Column
Inquiry = Cells(sr, sc)
Application.StatusBar = Inquiry & ": New Piece Part Setup needs to take place for this Part Number."
End If
Exit Sub
End Sub