Solved How to display message box triggered on click on cell

February 21, 2018 at 00:56:30
Specs: Windows 10
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.


See More: How to display message box triggered on click on cell

Reply ↓  Report •

#1
February 21, 2018 at 06:38:30
You shouldn't have to clear the n variable since variables don't retain their values once the macro ends.

I would say that the cause is that n is set to Nothing because your Target is not being found. When n is set to Nothing, n.Row is not valid.

In the VBA editor, click in the grey bar next to your With Bio.Range("$A$2:$B$5") instruction. (or highlight that instruction and press F9) This will create a Breakpoint and the code will stop at that instruction the next time you trigger the code.

You can then press F8 to single step through the code. Once the Set n instruction has been executed, hover your mouse over the n and I'll wager that you'll see n = Nothing. You can also highlight the n and drag it down into the Watch window to see what it gets set to as you single step through the code.

Review this tutorial to get some more debugging tips which should help with your VBA coding skills.

https://www.computing.net/howtos/sh...

message edited by DerbyDad03


Reply ↓  Report •

#2
February 21, 2018 at 07:22:13
Of course you're right. And after following your steps I understand why it works until I change the skill selected in the drop down.

I select a skill, then initially click on a name which becomes the Target and the code works fine. Then I click on subsequent names which take over as Target, all the while using the name clicked on to go and find on the Bios worksheet which sets to n. However, as soon as I click on a new skill in the drop down (outside of the list of names), I assume that the skill then becomes Target and it can't find a skill in a list of peoples' names and therefore n is left empty. Does that sound feasible?

If so, does that suggest I need to find a different way of assigning the name to look for, rather than using Target?


Reply ↓  Report •

#3
February 21, 2018 at 08:04:47
✔ Best Answer
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


Reply ↓  Report •

Related Solutions

#4
February 21, 2018 at 08:11:33
One other point:

When you select a range of cells, e.g. B5:F6, VBA considers the first cell in the range as the Target, sort of.

Target.Column = 2
Target.Row = 5

However....

Target.Address = $B$5:$F$6

Remember: Each cell in a range has it's own "number" within that range (and even outside of the Selected range.)

Put this code in a new worksheet and select F6:H9:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  MsgBox "The First Cell In The Target Range is:" & _
          vbCrLf & vbCrLf & Target.Cells(1).Address
  MsgBox "The Third Cell In The Target Range is:" & _
          vbCrLf & vbCrLf & Target.Cells(3).Address
  MsgBox "The Eighth Cell In The Target Range is:" & _
          vbCrLf & vbCrLf & Target.Cells(8).Address
  MsgBox "The One Hundreth Cell In The Target Range is:" & _
          vbCrLf & vbCrLf & Target.Cells(100).Address
End Sub

message edited by DerbyDad03


Reply ↓  Report •

#5
February 23, 2018 at 07:11:56
A thousand thanks once again Derby (can I call you that?).

I went with your intersect solution which worked a treat. I then had lots of fun creating message boxes
that create emails (based on some of your previous help with a few tweaks). Getting the HTML to work
on the body text and the signature was a headache but fun to solve.

Here's the code I ended up with in case it's useful to someone else:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim SearchScr As Worksheet
Dim Bio As Worksheet
Dim SendMail As String

'This controls the error caused by clicking within an empty cell within the target range
If Target = "" Then Exit Sub

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

'Find mentor name from clicked on name in "Mentor Search"
If Not Intersect(Target, Range("$C$14:$C$59")) Is Nothing Then
    With Bio.Range("$A$2:$A$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)
    SendMail = MsgBox(b & vbNewLine & vbNewLine & "Do you want to email your potential mentor to discuss this?", _
    vbInformation + vbYesNo, "Mentor's Biography")
    
    'Message if No is clicked
    If SendMail = vbNo Then
        MsgBox ("That's fine. Please feel free to come back at any time.")
    Else
            'Message and actions if Yes is clicked
            With Bio.Range("$A$2:$A$5")
            Set n = .Find(Target, lookat:=xlWhole)
                With Bio.Rows(n.Row)
                Set MyRecipient = Bio.Cells(n.Row, 4)
                Set FirstName = Bio.Cells(n.Row, 3)
                End With
                
            End With
 
'Setup and generate an email message addressed to the mentor
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
With OutlookMail
.Display
End With
        
            With OutlookMail
            
            'This line sets the recipient
            .To = MyRecipient
            .CC = ""
            .BCC = ""
            .Subject = "Mentoring discussion regarding " & Worksheets("Mentor Search").Range("C10")
            .HTMLBody = "<font face="">Dear " & FirstName & "
" & "
" & "I've just used the BM Skills Log Mentor Search and saw that you are able to mentor in " _
            & SearchScr.Range("C10") & "." & "
" & "
" _
            & "Please could we get together to talk about how you might be able to help me with this topic?" & "
" & "
" _
            & "Kind Regards" & .HTMLBody & "</font>"
            End With
            
        Set OutlookMail = Nothing
        Set OutlookApp = Nothing

        End If
    
End With
End If

End Sub

Note that in my code for the email message I've used the HTML code for the line breaks (which is br enclosed within left and right pointing arrow heads), even though they don't show here. This is because I've formatted it for HTML and vbNewLine wasn't working as I wanted it to.

message edited by ScottV


Reply ↓  Report •

#6
February 23, 2018 at 07:36:26
Glad I could help.

Thanks for posting your final code. It's always useful to have examples in the archives that others can find
or that we can point them to instead of reinventing the proverbial wheel.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
February 26, 2018 at 08:13:54
Hi DerbyDad03. Could you help me with a bit of error handling as I've not really done any of that until now.

With my code above, I've added a simple error handler at the start of the procedure to return the user to the drop down list in cell C10:

On Error GoTo eh

and then just before the end:

eh:
Range("C10").Select

because I noticed that if someone clicks within the range C14:C59, but beneath the last name returned, i.e. in an empty cell within the range, you get the error Run Time Error 91: Object variable or With block variable not set.

I basically didn't want to alert the user to an error occurring when they try to click on a cell within the range that has no value. Is there a better way of dealing with this?

Many thanks.


Reply ↓  Report •

#8
February 26, 2018 at 08:59:41
What do you want to happen if the user clicks in an empty cell within that range? If nothing, then just test for an empty cell.

 
If Not Intersect(Target, Range("$C$14:$C$59")) Is Nothing Then
  If Target <> "" Then '<<<<<<<<<<<<<<<<<
    With Bio.Range("$A$2:$A$5")
        Set n = .Find(Target, lookat:=xlWhole)
    End With
'.....
'The rest of your code
'......
  End If
 End If

In fact, you could even put...

 If Target = "" Then Exit Sub 

...as the very first line (even before your Set instructions) so that nothing will happen when any empty cell is selected. The code won't even check to see if the Selection is within the Target range.

Further to that, why bother executing those 2 Set instructions if the selection isn't within the Target range? It's a waste of resources - admittedly minor, but still a waste - if the code isn't going to actually do anything if the Selection is outside of the Target range. Consider moving the Set instructions so that they only execute once the code has determined that it will actually need those 2 objects.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#9
February 27, 2018 at 01:14:35
Hi Derby. I went with the latter because it's simpler and I like simple when I can get away with it. Thank you. I've edited my code above to include this error handler.

Reply ↓  Report •

Ask Question