Adding Color and Changing Font of a Button made using VBA

[aryaden]

I currently have code to create a button that goes into A1 and I would like to change the color and font of the button. How can I add to my existing code to do so?

My Current Code:

Sub CreateButton()
Dim btn As Button
Dim t As Range

Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete

Set t = ActiveSheet.Range(Cells(1, 1), Cells(1, 1))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)

With btn
.OnAction = "Button" 'Runs Macro Button 
.Caption = "Run Macros"
.Name = "Button"

Application.ScreenUpdating = True

End Sub

Thanks for any help in advance!

 

[Fluff]

You cannot change the colour of a forms control button. Would you be happy using a shape instead?

 

[aryaden]

You cannot change the colour of a forms control button. Would you be happy using a shape instead?

Yes that would be great as long as I can incorporate it into my VBA code. Do you have any ideas to implement that?

 

[Fluff]

What colour & font do you want?

 

[aryaden]

What colour & font do you want?

I wanted the button to be dark blue and the font color to be white. Thanks!

 

[Fluff]

Ok, how about

Sub CreateButton()
Dim btn As Shape

Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Shapes("Button").Delete
On Error GoTo 0

With ActiveSheet.Range("A1")
   Set btn = .Worksheet.Shapes.AddShape(1, .Left, .Top, .Width, .Height)
End With
With btn
   .OnAction = "Button" 'Runs Macro Button
   .TextFrame.Characters.Caption = "Run Macros"
   .Fill.ForeColor.RGB = rgbDarkBlue
   .TextFrame.Characters.Font.Color = vbWhite
   .Name = "Button"
End With
Application.ScreenUpdating = True

End Sub

 

[aryaden]

Ok, how about

Sub CreateButton()
Dim btn As Shape

Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Shapes("Button").Delete
On Error GoTo 0

With ActiveSheet.Range("A1")
   Set btn = .Worksheet.Shapes.AddShape(1, .Left, .Top, .Width, .Height)
End With
With btn
   .OnAction = "Button" 'Runs Macro Button
   .TextFrame.Characters.Caption = "Run Macros"
   .Fill.ForeColor.RGB = rgbDarkBlue
   .TextFrame.Characters.Font.Color = vbWhite
   .Name = "Button"
End With
Application.ScreenUpdating = True

End Sub

Thanks! The button color and font have changed but it no longer seems to be running the macros correctly like my previous code did. Also if I only want to apply this code on one worksheet in my workbook and I know the name of that worksheet how would I modify the code?

 

[Fluff]

What is the name of the sheet?

 

[aryaden]

What is the name of the sheet?

Data_1

Thanks for all the help!

 

[Fluff]

In that case you can use

Sub CreateButton()
Dim btn As Shape

Application.ScreenUpdating = False

On Error Resume Next
Sheets("Data_1").Shapes("Button").Delete
On Error GoTo 0

With Sheets("Data_1").Range("A1")
   Set btn = .Worksheet.Shapes.AddShape(1, .Left, .Top, .Width, .Height)
End With
With btn
   .OnAction = "Button" 'Runs Macro Button
   .TextFrame.Characters.Caption = "Run Macros"
   .Fill.ForeColor.RGB = rgbDarkBlue
   .TextFrame.Characters.Font.Color = vbWhite
   .Name = "Button"
End With
Application.ScreenUpdating = True

End Sub

Is the macro you want to run simply called Button?