Status Bar Display

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!

Code:

---

'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

---

Didn't we go over this before?

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
   
    If Target.Address = "$C$2:$M$3" Then
        Application.StatusBar = "Created by:" & Target.Cells(1)
        Exit Sub
    End If
   
    If Intersect(Target, Rows("9:28")) Is Nothing Then Exit Sub
   
    Select Case Target.Column
    Case 2: SubB Target
    Case 5, 6: SubEF Target
    Case 9 To 12: SubItoL Target
    End Select
   
End Sub

Private Sub SubB(Target As Range)
'Code to work on Coumn B
End Sub

Private Sub SubEF(Target As Range)
'Same As Above For columns F & E
'repeat As needed
End Sub

---

This

Code:

---

  sr = Selection.Row
    sc = Selection.Column
    Inquiry = Cells(sr, sc)

---

Is the same as This

Code:

---

Inquiry = Target

---

And This

Code:

---

VLookup(Inquiry, [PN_STATUS], 2, 0)

---

Is the Same As This

Code:

---

VLookup(Target, [PN_STATUS], 2, 0)

---

And Finally, unless you have changed your VLookUp Tables, you can't use the same criteria for different Tables

Code:

---

Private Sub SubB(Target As Range)      'Code to work on Coumn B End Sub    Private Sub SubEF(Target As Range)      'Same As Above For columns F & E      'repeat As needed End Sub

---

I don't understand what the "Code to work on Column B" is. I tried the code below but that doesn't work. It just doesn't do anything. No error or result.

Code:

---

Private Sub SubB(Target As Range)
Dim MyDesc1 As Range
MyDesc1 = Application.WorksheetFunction.VLookup(Target, [PN_STATUS], 2, 0)
Application.StatusBar = Target & " - " & MyDesc1
End Sub

---

If you use the VBA Menu >> Debug >> Compile, it will tell you that there is a type mismatch in the VLookup Line.



Because you have declared MyDesc1 as a Range, but Vlookup returns a Value.



Debug>> Compile is the first step in troubleshooting. I use it many time while writing a single sub.

So how do I fix it? What should the code be for my sub?

This is what I ended up doing...

Code:

---

'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,D9:D28,I9:L28,E9:F28")) Is Nothing Then Exit Sub 'Entire Range for Status Bar Display
    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("$E$9:$E$28")) Is Nothing Then Exit Sub
        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
       
If Intersect(Target, Range("$D$9:$D$28")) Is Nothing Then Exit Sub
        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."

Exit Sub
End Sub

---

I guess you know best.

It works...

Quote:

---

Originally Posted by SamT

I guess you know best.

---

Select a Cell in D

Works Every time... Because I know best.

Code:

---

'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,D9:D28,I9:L28,E9:E28")) Is Nothing Then Exit Sub 'Entire Range for Status Bar Display
    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)
    MyDesc5 = Application.WorksheetFunction.VLookup(inquiry, [PP], 2, 0)
   
    Application.StatusBar = inquiry & " - " & MyDesc1 & MyDesc2 & MyDesc3 & MyDesc4 & MyDesc5
   
If Intersect(Target, Range("$E$9:$E$28")) Is Nothing Then Exit Sub
        sr = Selection.Row
        sc = Selection.Column
        inquiry = Cells(sr, sc)
        MyDesc6 = Application.WorksheetFunction.VLookup(inquiry, [OIB], 8, 0)
   
        Application.StatusBar = inquiry & ": Quantity On-Hand= " & MyDesc6
Exit Sub
End Sub

---