VBA Runtime Error when Selecting Dropdown List

[Tigron]

Hello community,

first of all I'd like to note, that I am a complete noob when it comes to VBA. Unlike other programming languages such as HTML, CSS or even Python, VBA doesn't really "speak with me", which makes finding a solution - or just even playing around with the code - pretty difficult. I feel "a tiny bit" overwhelmed.

I am working on a trading journal in Excel (I trade cryptocurrencies) and found a solution to an issue I had. I wanted to be able to put long notes on my individual trades in a very small cell and, when reviewing my trades in retrospect, be able to "hover over" that cell and have a pop-up appear with the complete text. I found a great solution here: Link

The problem with this solution is that, whenever I select a cell that contains a dropdown list, I get a VBA runtime error. Please view screen shots for the exact message.

The code I am using looks like this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const SHAPENAME As String = "MessageShape"
    Dim ws As Worksheet
    Dim oshp As Shape
    Dim icount As Integer
    Dim iX As Integer
    Dim iY As Integer
    Dim iHeight As Integer
    Dim iWidth As Integer
    
    Set ws = ActiveSheet
    
    'Only run if one cell is selected
    If Target.Count > 1 Then Exit Sub
    
    'Delete any existing shapes. We run though all shapes backwards
    For icount = ws.Shapes.Count To 1 Step -1
        Set oshp = ws.Shapes(icount)
        If (oshp.Name = SHAPENAME) Then
            oshp.Delete
        End If
    Next icount
    
    'Lets grab the position of the selected cell, we'll use this to place the message box just to the right of it.
    'We add 5 to the position to give it a pleasant offset
    iX = Target.Cells(1, 2).Left + 5
    iY = Target.Top + 5
    iHeight = 200 'Adjust the box height by changing this
    iWidth = 450 'Adjust the box widthby changing this
    
    'Show the message box - Amend this if you want to change the position
    Select Case Target.Column
            Case 21, 32, 37 'Add column numbers here
            Set oshp = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, iX, iY, iWidth, iHeight)
            oshp.TextFrame2.TextRange.Characters.Text = Target.Value
            oshp.Name = SHAPENAME
    End Select

End Sub

Can someone explain what is going wrong here and how to solve this issue. You would make me a immensely happy camper.
One last thing: I would also like to change the background color and the font color of the pop-up, but I have no idea as to where I have to paste the code.

Thanks in advance for any help you can provide!

 

[Tigron]

Can you try to contact me via private message? I see an option where I can look at my conversations, but I cannot start a new conversation. I'm guessing because I am new to the forum I can't access this feature...??
I would like to send you the xlsm file via email (or PM, if that is possible).

 

[Tigron]

Message sent

To all new members: after 5 forum messages the option to send PMs is enabled.

 

[Tigron]

Column D and E for example contain dropdowns. Also M and N.

 

[Tigron]

I added new screenshots. From what I can tell it is the same error.

Could this be because I am using Excel for macOS?

 

[Tigron]

With the new code I instantly get an error message, no matter which cell I select.

 

[Tigron]

No matter what the result may be, I want to thank you for your help! Very much appreciated! Maybe you'll have an epiphany over night or so. If you do, I'll be here waiting for you

 

[Tigron]

I tried creating a new spreadsheet with dropdowns and a longer text to see if I can replicate the results. Unfortunately the results were the same. Same error code and same place in the code itself.
I really wonder what the problem is.

 

[Tigron]

From which code version should I start? Your first reply plus this snippit?

For icount = ws.Shapes.Count -1 To 0 Step -1

Btw I get a compile error on your last suggestion.

I will have to get back to you tomorrow. It's almost 11p.m. here in Germany. Have a great evening!

 

[Tigron]

YES!!! It works!! Thank you, danke, muchas gracias, merci beaucoup!

And what's equally cool: the popup only appears if the cell actually contains text. That's perfect!

If I wanted to change the background color to black, the font color to white and font size to 12, would I have to do it here?

If Not IsEmpty(Target.Value) Then
    Select Case Target.Column
            Case 21, 32, 37
            With ws.Shapes.AddTextbox(msoTextOrientationHorizontal, iX, iY, iWidth, iHeight)
                .TextFrame2.TextRange.Characters.Text = Target.Value
                .Name = SHAPENAME
                .BackgroundStyle = msoBackgroundStylePreset10
            End With

 

[Tigron]

Yes I saw that option and am playing around with it right now.
I'll try to implement vbwhite. Awesome! Thank you

Edit:
I get an error because of .TextFrame2.Characters.Font.Color = vbwhite
If I understand the debug message correctly, its because of this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

It's not important to reply any time soon. Whenever you have time. I just wanted to give you a quick feedback.