Ccannot Assign 'AlternateText' value in an Excel Checkbox using VBA

All the lines in this code work except for the  "AlternateText" line.
I receive an error "Object doesn't support this property or method"

    
ActiveSheet.checkboxes.Add(490, 20, 1, 1).Select
        With Selection
            .Caption = "Hello"              
            .LinkedCell = "G4"
            .Display3DShading = False
            .AlternativeText = "chkBox1"     'RECIEVE ERROR ON THIS LINE
        End With

Open in new window

askolitsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

maybe

        With Selection
            .Caption = "Hello"
            .LinkedCell = "G4"
            .Display3DShading = False
            .ShapeRange.AlternativeText = "chkBox1"   
        End With

Open in new window


because your selection has no AlternativeText Member

Regards
Angelp1ayCommented:
This is Excel VBA being ridiculous.

AlternativeText isn't a property of the VBA CheckBox object - see below picture showing IntelliSense when the CheckBox is strongly typed:
IntelliSense for CheckBox
It is in fact a property of the VBA Shape object.
IntelliSense for Shape
This rather ridiculous code works:
Public Sub Hack()

    ActiveSheet.CheckBoxes.Add(490, 20, 1, 1).Select
    With Selection
        .Name = "myCheckBox"
        .Caption = "Hello"
        .LinkedCell = "G4"
        .Display3DShading = False
    End With
    
    Dim s As Shape
    Set s = ActiveSheet.Shapes("myCheckBox")
    
    s.AlternativeText = "chkBox1"
    
    ' ...or if you want to set several things use another with
    'With s
    '    .AlternativeText = "chkBox1"
    'End With
        
End Sub

Open in new window


The End Product

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angelp1ayCommented:
Ahh ok, Rgonzo1971's method is a much easier way to access the CheckBox as a Shape.

Hopefully the explanation helped explain why the problem occurs though :)

I imagine if you wanted to set several Shape properties you could bind the box to a Shape variable:
    Dim s As Shape
    Set s = Selection.ShapeRange

    With s
        ...
    End With

Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

askolitsAuthor Commented:
Thanks for the help. That worked for me!
askolitsAuthor Commented:
Follow up.

You guys would probably know the answer to this one as well.

I also set the onAction property to a macro. I created a few check boxes.
I can't figure out how to get the name of the calling object from the called macro.

Tried things like Screen.ActiveControl.name but it fails.
Angelp1ayCommented:
Edit: If you just want the name you need "Application.Caller"

This gets you the checkbox itself:
Dim cBox As CheckBox
Set cBox = ActiveSheet.CheckBoxes(Application.Caller)

Open in new window

askolitsAuthor Commented:
Perfect. Thanks!
Angelp1ayCommented:
I thought Rgonzo1971 deserved some points too :)
askolitsAuthor Commented:
Actually, I think I had initially tried Rgonzo1971 's solution and it didn't work. Rather than try to figure out what I might have done wrong, I tried Angelp1ay's solution and it worked without me having to really do any testing.  It's possible Rgonzo1971 's  actually does work, but I didn't really spend too much time on it.
Angelp1ayCommented:
Hmmm... I thought I'd updated mine with his code and tested that it worked, but maybe I didn't actually get to re-running it.
askolitsAuthor Commented:
Either way. I got it to work. Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.