VBA Runtime Error when Selecting Dropdown List

Tigron

New Member
Joined
Jun 2, 2023
Messages
21
Office Version
  1. 2021
Platform
  1. MacOS
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:
VBA Code:
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!
 

Attachments

  • Debug_Error.png
    Debug_Error.png
    51.8 KB · Views: 13
  • 2023-06-04_22-27-17.jpg
    2023-06-04_22-27-17.jpg
    254.7 KB · Views: 14
Message sent 😁

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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Where are you clicking that it is giving you the error? I am on the 'Trading Journal' sheet and I don't see any dropdowns.
 
Upvote 1
Column D and E for example contain dropdowns. Also M and N.
 
Upvote 0
I have no idea what might be going on on your end.

I opened the file and everything is working as expected. I can select different values from the dropdowns and when I click in columns 21, 32, and 37, the code fires and shows the shape with the text.

This is a different error than the images you posted in the original post, correct?

If so, what error are you getting now?
 
Upvote 1
I added new screenshots. From what I can tell it is the same error.

Could this be because I am using Excel for macOS?
 

Attachments

  • Debug_Error_2.png
    Debug_Error_2.png
    51.2 KB · Views: 7
  • VBA Highlight 3.jpg
    VBA Highlight 3.jpg
    225.7 KB · Views: 6
Upvote 0
Maybe it is. I've never used a Mac so, I am not sure what could be causing this.

The only thing I can think is that somehow Mac is using 0 based indexing whereas it's 1 based on PC.

Back to that first answer I gave changing

Excel Formula:
For icount = ws.Shapes.Count To 1 Step -1

to

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

But outside of that, I am kind of stumped.
 
Upvote 1
Actually, looking at it, the code I just posted is slightly different than the first time I posted it.

Give that last one a go. 🤞
 
Upvote 1
With the new code I instantly get an error message, no matter which cell I select. 😭
 
Upvote 0
Sorry, buddy. Don't know what to tell you. Maybe someone else on here can help out.

I'll keep digging and keep you posted, but it doesn't make sense to me why it's doing this.
 
Upvote 1
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top