CODEWITHSUNDEEP
vbaexcelexcel-2010
CAgrippa
CAgrippa

Reputation: 99

Find complete list of members for Excel VBA forms button objects

In Excel 2010, using VBA code to create a macro-calling push-button.

NOTE: This is really a question about where to find documentary support for a type of object, not a coding thing as such.

So... Working from the macro recorder, I get this (supposing I want to turn cell M8 into a button):

Sub MakeMyButton()

    ' Some basic definitions
    StarterRow = 8
    MyLeft = Cells(StarterRow, "M").Left
    MyTop = Cells(StarterRow, "M").Top
    MyHeight = Cells(StarterRow, "M").Height
    MyWidth = Cells(StarterRow, "M").Width

    ' Now comes the creation bit        
    ActiveSheet.Buttons.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
    With Selection
        .AltText = "View"
        .OnAction = "afRoomText"
        ' .Value = xlOff
        ' .LinkedCell = "M" & StarterRow
        ' .Display3DShading = True
    End With
End Sub

The problem is, of course, that .Value and .LinkedCell and .Display3DShading, here marked as remarks, are not valid for Buttons.Add -- fair enough. But I cannot seem to find a comprehensive list of what IS valid for this object! Anyone know where to look?

(Incidentally, how come the record-macro thing uses things like "CheckBoxes.Add" that aren't documented anywhere obvious in Excel VBA? If you go hunting for "CheckBoxes" in the documentation, you find a lot of tangential remarks but no actual information, as though this were deprecated code that just happens to be used automatically....)

Upvotes: 0

Views: 1949

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38540

Always declare all your variables using Dim. For objects (such as Button) this will give you access to autocompletion. Example:

Dim b As Button ' Aha!
Set b = ActiveSheet.Buttons.Add(324, 102, 108, 51)

Autocompletion for Button object

Another way to create a button:

Dim but As Shape
Dim sh As Worksheet

Set sh = ActiveSheet
Set but = sh.Shapes.AddFormControl(xlButtonControl, 100, 100, 100, 100)

With but
    .AlternativeText = "View"
    .OnAction = "afRoomText"
    With .TextFrame
        .Characters.Text = "Press Me"
        .Characters.Font.Color = RGB(255, 50, 50)
    End With
End With

Upvotes: 2

Related Questions