Solved

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

Posted on 2014-01-08
12
1,085 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

777 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