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]

This version of your original code works fine for me:

Dim Width As Integer, Height As Integer, HzPos As Integer, VtPos As Integer
'Dimensions
Width = 144
Height = 20
VtPos = 400
HzPos = 275
With ActiveSheet.Buttons.Add(HzPos, VtPos, Width, Height)
   .OnAction = "AdvertiserByIssueDateWeekday"
   .ShapeRange.ScaleWidth 1, msoFalse, msoScaleFromTopLeft
   .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

 

[RiaM]

I read on other site that this might be a limitation with form controls in Excel 2007 and that one should try using ActiveX Controls

Any idea how to do this? I'm so lost