Command Button Magic On Worksheets
There are two types of buttons: Forms Control Buttons which can be linked to a Macro, and Active-X (OLE Objects) Buttons. This article concerns only Active-X Commandl buttons. There are many times when it is valuable to dynamically insert a button onto a worksheet, and then delete, hide or unhide it. The button is created and VBA code is assigned to it. The following code shows how to create and delete buttons, and how to change their characteristics.
Program Code
Option Explicit
' ***************************************************************
' Hide Existing Active-X Button
' ***************************************************************
Public Sub UpdateMainPageButtons()
Dim wkbQuotingApplication As Workbook
Dim wksMainPage As Worksheet
Dim wksQuote As Worksheet
Dim intColumnNumberOfQuote As Integer
Dim btnMainPage As OLEObject
Dim strName As String
Set wkbQuotingApplication = ThisWorkbook
Set wksMainPage = wkbQuotingApplication.Sheets("CreateQuote")
Set wksQuote = wkbQuotingApplication.Sheets("Quote")
' ************************************************************
' Find Leftmost Column Of Quote
' ************************************************************
intColumnNumberOfQuote = wksQuote.Cells(5, Columns.Count).End(xlToLeft).Column
wksMainPage.Unprotect
For Each btnMainPage In wksMainPage.OLEObjects
strName = btnMainPage.Name
If btnMainPage.OLEType = xlButtonOnly And strName = "cmdReviewQuoteWorksheet" Then
If intColumnNumberOfQuote = 1 Then
btnMainPage.Visible = False
Else
btnMainPage.Visible = True
End If
End If
Next
wksMainPage.Protect
End Sub
Option Explicit
Sub CreateCommandButton()
' *********************************************************************************
' Create Command Button, Add VBA Code And Set Attributes
' *********************************************************************************
Dim wkbButtonExample As Workbook
Dim wksButtonSheet As Worksheet
Dim Obj As Object
Dim Code As String
Set wkbButtonExample = ThisWorkbook
Set wksButtonSheet = wkbButtonExample.Sheets("Sheet1")
wksButtonSheet.Select
' *********************************************************************************
' Create The Button
' *********************************************************************************
Set Obj = wksButtonSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=200, Top:=100, Width:=100, Height:=35)
Obj.Name = "TestButton"
' *********************************************************************************
' Add Button Text
' For Colors See This Site: http://www.endprod.com/colors/ (Use Access Colors)
' *********************************************************************************
With wksButtonSheet.OLEObjects("TestButton").Object
.Caption = "Test Button"
.Font.Bold = True
.ForeColor = RGB(0, 0, 255)
.Font.Name = "Calibri"
.Font.Size = 14
.BackColor = RGB(173, 255, 47)
.WordWrap = True
End With
' *********************************************************************************
' Change Button's Visibility And Other Properties
' *********************************************************************************
With wksButtonSheet.OLEObjects("TestButton")
.Visible = False
.Visible = True
.Shadow = True
End With
' *********************************************************************************
' Create String Containg All Macro Code
' Use Adjoining "" To Enclose Quotes within Quotes
' *********************************************************************************
Code = "Sub TestButton_Click()" & vbCrLf
Code = Code & "MsgBox(""Sheet1 Code Called"")" & vbCrLf
Code = Code & "Call Tester" & vbCrLf
Code = Code & "End Sub"
' *********************************************************************************
' Add The Macro At The End Of Sheet1's Modules
' *********************************************************************************
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
.insertlines .CountOfLines + 1, Code
End With
End Sub
Sub Tester()
MsgBox ("You Clicked The Test Button")
End Sub
Sub ModifyExistingCommandButton()
' *********************************************************************************
' Modify Attributes of an Existing Command (ActiveX) Button
' *********************************************************************************
Dim wkbButtonExample As Workbook
Dim wksButtonSheet As Worksheet
Set wkbButtonExample = ThisWorkbook
Set wksButtonSheet = wkbButtonExample.Sheets("Sheet1")
With wksButtonSheet.OLEObjects("TestButton").Object
.Caption = "Updated Caption"
End With
With wksButtonSheet.OLEObjects("TestButton")
.Height = 70
End With
End Sub
Sub DeleteSheetCodeAndButton()
' *********************************************************************************
' Remove Existing ActiveX Button And Associated Code
' *********************************************************************************
Dim wkbButtonExample As Workbook
Dim wksButtonSheet As Worksheet
Dim wksSheet As Object, strName As String
Set wkbButtonExample = ThisWorkbook
Set wksButtonSheet = wkbButtonExample.Sheets("Sheet1")
For Each wksSheet In wkbButtonExample.Sheets
Select Case UCase(wksSheet.Name)
Case "SHEET1", "SHEET2", "SHEET3"
strName = wksSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
.DeleteLines 1, .CountOfLines
End With
Case Else
MsgBox ("Undefined Sheet Name " & wksSheet.Name)
End Select
Next wksSheet
wksButtonSheet.OLEObjects("TestButton").Delete
End Sub
