Home/ Questions/Q 6916
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-22T03:12:53+00:00 In:

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