Solved

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

Posted on 2014-01-08
12
1,061 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 49

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now