Solved

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

Posted on 2014-01-08
12
1,126 Views
Last Modified: 2014-01-16
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

0
Comment
Question by:askolits
  • 5
  • 5
12 Comments
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 100 total points
ID: 39765699
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
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 400 total points
ID: 39765761
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
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39765776
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:askolits
ID: 39765970
Thanks for the help. That worked for me!
0
 

Author Comment

by:askolits
ID: 39766182
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.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39766460
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

0
 

Author Comment

by:askolits
ID: 39766606
Perfect. Thanks!
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39773844
I thought Rgonzo1971 deserved some points too :)
0
 

Author Comment

by:askolits
ID: 39785753
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.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39786153
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.
0
 

Author Comment

by:askolits
ID: 39786161
Either way. I got it to work. Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question