The code below was created to be used in Excel 2003, and worked fine to create two command buttons, one to cancel the automation, one to resume it. Now I'm on 2010, and when I run it I get an error code saying the font size has to be between 1 and 409 points. However, the highlighted code is the
.Color = vbBlue (or any other color)
I'd appreciate some direction in how to revamp just that part of the code. The rest of the functionality still works great. Full code is pasted below. Thanks in advance.
Sub AddButtons()
Dim strBname As String
ActiveSheet.Buttons.Add(200, 40, 81, 50).Select
strBname = Selection.Name
Selection.OnAction = "ResumeAuto" 'Macro to run
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 46
Selection.Characters.Text = "Click here to Resume the Automation" 'Text of button
With Selection.Characters(Start:=1, Length:=14).Font '
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 9
.Color = vbBlue
End With
With Selection.Characters(Start:=15, Length:=6).Font 'cut out (start...) if doing whole range
.Name = "Verdana"
.FontStyle = "Bold"
.Size = 9
.Color = vbRed
End With
With Selection.Characters(Start:=22, Length:=14).Font '
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 9
.Color = vbBlue
End With
ActiveSheet.Buttons.Add(325, 40, 81, 50).Select
strBname = Selection.Name
Selection.OnAction = "KillButts" 'Macro to run
Selection.Characters.Text = "Click here to Cancel the Automation" 'Text of button
With Selection.Characters(Start:=1, Length:=14).Font '
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 9
.Color = vbBlue
End With
With Selection.Characters(Start:=15, Length:=6).Font 'cut out (start...) if doing whole range
.Name = "Verdana"
.FontStyle = "Bold"
.Size = 9
.Color = vbRed
End With
With Selection.Characters(Start:=22, Length:=14).Font '
.Name = "Verdana"
.FontStyle = "Regular"
.Size = 9
.Color = vbBlue
End With
Range("A1").Select
End Sub
Sub ResumeAuto()
Run "Killbutts"
MsgBox "Now we're resuming our process"
End Sub
Sub Killbutts()
'Activate sheet to delete autoshapes.
Dim GetShape As Shape
For Each GetShape In ActiveSheet.Shapes
GetShape.Delete
Next
End Sub
Bookmarks