Computing Staff
  • 0

How To Display Message Box Triggered On Click On Cell

  • 0

I have a worksheet “Mentor Search” which DerbyDad03 has helped me with previously. It performs a search for mentors with a particular skill and returns all names that hold that skill.

I’m trying to develop it further so that when you click on any name from the returned list a message box opens with their short biography that I have stored on a separate worksheet “Bios”.

I’ve managed to make it work for the skill chosen, where if you choose Excel from a drop down list, and Fred and Bob are returned as the skill holders, when you click on each of their names the message box is successfully displayed. However, as soon as I choose a different skill from the drop down list and then click on any of the returned skill holders, I get a Run Time Error 91: Object variable or With block variable not set.

I’ve tried debugging and think I understand what the problem is, but don’t know how to fix it. The code is triggered on a selection change and is as follows:

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim SearchScr As Worksheet
Dim Bio As Worksheet

Set SearchScr = Worksheets("Mentor Search")
Set Bio = Worksheets("Bios")

'Find mentor name from clicked on name in "Mentor Search"
With Bio.Range("$A$2:$B$5")
    Set n = .Find(Target, lookat:=xlWhole)
End With

'Look for relevant mentor's bio and display it in a message box
With Bio.Rows(n.Row)
    Set b = Bio.Cells(n.Row, 2)
    MsgBox (b)
End With

End Sub

When I click Debug, it takes me to the line

With Bio.Rows(n.Row)

With my very limited knowledge I suppose that I have to clear the variable ‘n’ when a new skill is chosen in the drop down but I’m not sure?

If you need more information please let me know.

Thank you.

Share

1 Answer

  1. I am assuming that the Skills and Names are in different columns or rows. If that is correct, you can tell the code to only act on a specific column, row or range. The code will still run on every change in selection, but only do something in the specified cases, based on where the Target is located. This allows you to better control the actions of the macro. In reality, the code will do something every time it runs (i.e. evaluate the If instructions) but the user will only see something when an If is True.

    In the following code, you will only see a message box(es) if the selected cell is in Column 2 or Row 5 or within B4:F6.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Display Column Message
      If Target.Column = 2 Then
        MsgBox "Cell In Column 2 Selected:" & _
                vbCrLf & vbCrLf & Target.Address
      End If
    'Display Row Message
      If Target.Row = 5 Then
        MsgBox "Cell In Row 5 Selected:" & _
                vbCrLf & vbCrLf & Target.Address
      End If
    'Display Range Message
      If Not Intersect(Target, Range("$B$4:$F$6")) Is Nothing Then
         MsgBox "Cell In Range $B$4:$F$6 Selected:" & _
                 vbCrLf & vbCrLf & Target.Address
      End If
    End Sub

    message edited by DerbyDad03

    • 0