Sizing problem with adding buttons

[RiaM]

Hi guys, I've written a macro to add buttons to a sheet of mine. The buttons call up various other macros. The problem I'm having seems to be a screen update. I've set the button size to be greater than the number of characters it contains. However when I run the macro, it first adds the button at its default size (width which is too small), and bombs out. Any ideas how to get away from this? Below is the code I'm using:

Dim Width As Integer, Height As Integer, HzSpace As Integer, VtPos as integer

'Dimensions
Width = 144
Height = 20
VtPos = 400
HzPos = 275

ActiveSheet.Buttons.Add(HzPos, VtPos, Width, Height).Select
Selection.OnAction = "AdvertiserByIssueDateWeekday"
Selection.ShapeRange.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
Selection.HorizontalAlignment = xlLeft
Selection.Characters.Text = " Advertiser by IssueDate: Weekday"
With Selection.Characters(Start:=1, Length:=40).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
End With

Thanks,
Ria

 

[RoryA]

Can you clarify "bombs out"?

 

[RiaM]

Runtime error '1004'
Unable to set the Text propoerty of the Characters class

When I choose to debug the following line is highlighted:
Selection.Characters.Text = " Advertiser by IssueDate: Weekday"

I think it has to do with the number of characters because if I make the text "xxx", then it works fine.

I've got several of buttons and noticed that when I run the macro, the buttons get built at the default width, and then after all are built do they increase in width to what I set it to be

Ideas?

 

[RiaM]

bump

 

[RiaM]

bump

 

[mikerickson]

With ActiveSheet.Buttons.Add(HzPos, VtPos, Width, Height).Select
    .OnAction = "AdvertiserByIssueDateWeekday"
    .ScaleWidth 1, msoFalse, msoScaleFromTopLeft
    With .TextFrame
    .HorizontalAlignment = xlLeft
    .Characters.Text = " Advertiser by IssueDate: Weekday"
        With .Characters(Start:=1, Length:=40).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11
        End With
    End With
End With

 

[RiaM]

Hi Mike

Thanks for this. But that's caused an error
Runtime error '424', Object Required

The line highlighted on debug is
.OnAction = "AdvertiserByIssueDateWeekday"

thanks

 

[mikerickson]

Try removing the .Select from the first line (With ...)

 

[RiaM]

New error then appears on the ffg line
Error '438'
Object doesn't support this property or method

Debug highlights .ScaleWidth 1, msoFalse, msoScaleFromTopLeft

mmm

 

[RiaM]

Is there a maximum number of characters I can have in a Button? Using my original code if I reduce the number of characters in the input text i.e. " Advertiser by IssueDate: Weekday" by one character, the sub works.

If there isn't a maximum, and its just the default settings that is causing the error, how do I change this prior to entering the text?

Any help most appreciated