Sleeper: Reset CommandButton Caption after creating PDF

[kaedinsh]

Hello!

I'm looking for help with this code - hopefully you can help me. I'm a complete beginner in Excel so if there's any information missing I'm happy to provide!

I have an Excel spreadsheet that I want to turn into a sort of checklist. Once all the items in the checklist have been checked, I want to turn the checklist into a pdf and send it to a specific email address. I already have a code for this that works.
However, the checkmarks should not be done via a checkbox (too small) but via a command button. When triggered, the caption in the command button changes from "empty" to "ok" and when triggered again back to empty.

I have codes for both these things. However, I am trying to find a solution to reset the command buttons back to "empty" after the pdf has been created. I already have implemented this for some cell values but I don't know if this is possible for command buttons as well. Any help is much appreciated.

Here is my code:

for pdf creation:


Private Sub CommandButton1_Click()
Dim xSht As Worksheet
Dim xFileDlg As FileDialog
Dim xFolder As String
Dim xYesorNo As Integer
Dim xOutlookObj As Object
Dim xEmailObj As Object
Dim xUsedRng As Range
Dim xPath As String
Dim xMemberName As String
Dim xMemberName1 As String
Dim xFileDate As String
Dim wFolder As String
Dim wDynamic As String
Set xSht = ActiveSheet
xPath = Range("X8")
xMemberName = Range("E9").Value
xMemberName1 = Range("E13").Value
xFileDate = Format(Now, "mm-dd-yyyy-hh-mm")
xFolder = xPath + xFolder + "" + xSht.Name + "-" + xMemberName + "-" + xMemberName1 + "-" + xFileDate + ".pdf"
'Check if file already exist
If Len(Dir(xFolder)) > 0 Then
xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next
If xYesorNo = vbYes Then
Kill xFolder
Else
MsgBox "if you don't overwrite the existing PDF, I can't continue." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
Exit Sub
End If
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file. Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If
Set xUsedRng = xSht.UsedRange
If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
'Save as PDF file
xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
'Create Outlook email
Set xOutlookObj = CreateObject("Outlook.Application")
Set xEmailObj = xOutlookObj.CreateItem(0)
With xEmailObj
.Display
.To = "EMAIL"
.CC = ""
.Subject = xSht.Name + "-" + xMemberName + "-" + xMemberName1 + "-" + xFileDate + ".pdf"
.Attachments.Add xFolder
If DisplayEmail = False Then
'.Send
End If
End With
Else
MsgBox "The active worksheet cannot be blank"
Exit Sub
End If
Range("E9:f9").ClearContents
Range("E13:f13").ClearContents
Range("S9:t9").ClearContents
Range("D17:D18").ClearContents
Range("D23:D26").ClearContents
Range("D31:D64").ClearContents
Range("D69:D92").ClearContents
Range("D97:D118").ClearContents
Range("D121:S129").ClearContents
End Sub


This is for the buttons to change the caption:


Private Sub CommandButton10_Click()
With CommandButton10
If .Caption = "" Then
.Caption = "OK"
ElseIf .Caption = "OK" Then
.Caption = ""
Else
.Caption = ""
End If
End With
End Sub


Any ideas?

[Aussiebear]

Welcome to VBAX kaedinsh. Please wrap your supplied code with code tags, as this improves the readability. I've taken the opportunity to do this for you this time.

[georgiboy]

If you are looking for a way to reset all of the commandbuttons in one go then maybe the below will help:

Sub test()
Dim btn As Object

For Each btn In Sheet1.Shapes
If Left(btn.Name, 13) = "CommandButton" Then
Set btn = Sheet1.OLEObjects(btn.Name)
With btn.Object
If .Caption = "" Then
.Caption = "OK"
ElseIf .Caption = "OK" Then
.Caption = ""
Else
.Caption = ""
End If
End With
End If
Next btn
End Sub