?
Solved

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

Posted on 2014-01-08
12
Medium Priority
?
1,207 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
12 Comments
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 400 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 1600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

718 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