Clear Checkboxes macro

Hi there,

Try inserting:

    Dim chk As CheckBox

    For Each chk In Activesheet.CheckBoxes
        chk.Value = False
    Next chk

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks! I will make sure to do this correctly in the future.

I made a macro with the code you gave me in it and tested it on the worksheet MilesEnter and it works great. When I insert the code like this though, it doesn't work.

Worksheets("MilesEnter").Range("ClearMiles").ClearContents

Dim chk As CheckBox

    For Each chk In Activesheet.CheckBoxes
        chk.Value = False
    Next chk
    
    Worksheets("TimeEnter").Range("ClearTime").ClearContents
    Worksheets("ExpenseEnter").Range("ClearExpense").ClearContents

Hi again,

I didn't realise that more than one worksheet was involved - try using:

Option Explicit


Sub ClearWorksheets()

    Dim vSheetName  As Variant
    Dim sSheetName  As String
    Dim chk         As CheckBox

    With ThisWorkbook
        .Worksheets("ExpenseEnter").Range("ClearExpense").ClearContents
        .Worksheets("MilesEnter").Range("ClearMiles").ClearContents
        .Worksheets("TimeEnter").Range("ClearTime").ClearContents
    End With

    For Each vSheetName In Array("ExpenseEnter", "MilesEnter", "TimeEnter")

        sSheetName = CStr(vSheetName)

        For Each chk In ThisWorkbook.Worksheets(sSheetName).CheckBoxes
            chk.Value = False
        Next chk

    Next vSheetName

End Sub

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi again,

I didn't realise that more than one worksheet was involved - try using:

Option Explicit


Sub ClearWorksheets()

    Dim vSheetName  As Variant
    Dim sSheetName  As String
    Dim chk         As CheckBox

    With ThisWorkbook
        .Worksheets("ExpenseEnter").Range("ClearExpense").ClearContents
        .Worksheets("MilesEnter").Range("ClearMiles").ClearContents
        .Worksheets("TimeEnter").Range("ClearTime").ClearContents
    End With

    For Each vSheetName In Array("ExpenseEnter", "MilesEnter", "TimeEnter")

        sSheetName = CStr(vSheetName)

        For Each chk In ThisWorkbook.Worksheets(sSheetName).CheckBoxes
            chk.Value = False
        Next chk

    Next vSheetName

End Sub

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thank you for being so patient with me. I almost sent the whole code for this, but was trying to not give more info then necessary. Now I've probably muddled things to much. Yes I am working with several worksheets. I have a main form which I'm using to automate tasks, like opening worksheets for entering etc. One macro on a button is used at the end of each month to save several worksheets as a pdf file, and then clear (reset) the worksheets used for data entry. So that I am getting everything in the right spot and not messing up what I already have working, would you be so kind as to look at the whole code and help me set this bit in where it belongs. Thank you so very much.
This is the code with no changes made:

Public Sub SaveSheetsAsPDF()
    Dim wksAllSheets As Variant
    Dim wksSheet1 As Worksheet
    Dim strFileName As String, strFilepath As String
    Dim dlgFolder As FileDialog

    'Set references
    Set wksSheet1 = ThisWorkbook.Sheets("TimeEnter")
    wksAllSheets = Array("Timesheet", "ExpenseSheet", "MileageSheet")

    'Set path
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With dlgFolder
        .Title = "Select Target Folder Containing Mandate Files"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Sub
        strFilepath = .SelectedItems(1) & "\"
    End With

    'Create the full Filename using indicated cells
    With wksSheet1
        strFileName = strFilepath & .Range("A3").Value & " " & .Range("B3").Value & ".pdf"
    End With

    ' Make the sheets visible
    ThisWorkbook.Sheets("Timesheet").Visible = xlSheetVisible
    ThisWorkbook.Sheets("ExpenseSheet").Visible = xlSheetVisible
    ThisWorkbook.Sheets("MileageSheet").Visible = xlSheetVisible
    ' Select the sheets
    ThisWorkbook.Sheets(wksAllSheets).Select
    'Save the array of worksheets as a PDF
    ActiveSheet.ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=strFileName, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=False, _
              OpenAfterPublish:=True
    Worksheets("MilesEnter").Range("ClearMiles").ClearContents
    Worksheets("TimeEnter").Range("ClearTime").ClearContents
    Worksheets("ExpenseEnter").Range("ClearExpense").ClearContents
    'Deselect all the exported worksheets
    ' Hide the exported sheets
    ThisWorkbook.Sheets(wksAllSheets).Visible = xlSheetHidden
End Sub

Hi again,

I'm a great believer in breaking code down into bite-sized pieces - it's easier to maintain and it's usually easier to understand.

See if the following version of your code does what you need:

Option Explicit


'=========================================================================================
'=========================================================================================


Public Sub PerformEndOfMonthRoutine()

    Dim sExportFileName As String
    Dim wksActive       As Worksheet

'   Use a function to retrieve the FullName of the file to be exported
    sExportFileName = msExportFileName()

'   Continue only if the FullName has been retrieved
    If sExportFileName <> vbNullString Then

'       Store the currently-active worksheet so that it can be activated on completion
        Set wksActive = ActiveSheet

            Call ExportSheetsToPdf(sExportFileName:=sExportFileName)

            Call ResetSheets

'       Return to the original active worksheet
        wksActive.Activate

    End If

End Sub


'=========================================================================================
'=========================================================================================


Private Function msExportFileName() As String

    Dim sExportFilePath     As String
    Dim sExportFileName     As String
    Dim dlgFolder           As FileDialog

'   Set the path for the Export file
    Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)

    With dlgFolder

        .Title = "Select Target Folder Containing Mandate Files"
        .AllowMultiSelect = False

        If .Show = -1 Then

              sExportFilePath = .SelectedItems(1) & "\"

'             Create the full Filename using indicated cells
              With ThisWorkbook.Worksheets("TimeEnter")
                  msExportFileName = sExportFilePath & _
                                     .Range("A3").Value & " " & .Range("B3").Value & _
                                     ".pdf"
              End With

        Else: msExportFileName = vbNullString

        End If

    End With

End Function


'=========================================================================================
'=========================================================================================


Private Sub ExportSheetsToPdf(sExportFileName As String)

    Dim vaExportSheetNames  As Variant
    Dim vSheetName          As Variant
    Dim sSheetName          As String

    vaExportSheetNames = Array("Timesheet", "ExpenseSheet", "MileageSheet")

'   Make the Export sheets visible
    For Each vSheetName In vaExportSheetNames
        sSheetName = CStr(vSheetName)
        ThisWorkbook.Worksheets(sSheetName).Visible = xlSheetVisible
    Next vSheetName

'       Select the Export sheets and then save the array of worksheets as a PDF file
        ThisWorkbook.Worksheets(vaExportSheetNames).Select

        ActiveSheet.ExportAsFixedFormat Quality:=xlQualityStandard, _
                                        Filename:=sExportFileName, _
                                        IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=True, _
                                        Type:=xlTypePDF

'   Hide the Export sheets now that the export operation has been completed
    For Each vSheetName In vaExportSheetNames
        sSheetName = CStr(vSheetName)
        ThisWorkbook.Worksheets(sSheetName).Visible = xlSheetHidden
    Next vSheetName

End Sub


'=========================================================================================
'=========================================================================================


Private Sub ResetSheets()

    Dim vSheetName  As Variant
    Dim sSheetName  As String
    Dim chk         As CheckBox
    
    With ThisWorkbook

'       Clear data values
        .Worksheets("ExpenseEnter").Range("ClearExpense").ClearContents
        .Worksheets("MilesEnter").Range("ClearMiles").ClearContents
        .Worksheets("TimeEnter").Range("ClearTime").ClearContents

'       Reset CheckBoxes
        For Each vSheetName In Array("ExpenseEnter", "MilesEnter", "TimeEnter")

            sSheetName = CStr(vSheetName)

            For Each chk In .Worksheets(sSheetName).CheckBoxes
                chk.Value = False
            Next chk

        Next vSheetName

    End With

End Sub

Highlighted values can be altered to suit any future changes/requirements.


The trailing slash in the following statement isn't needed on my installation - you could experiment with omitting it:

              sExportFilePath = .SelectedItems(1) & "\"

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Hi again,

Instead of having three workBOOK-level Defined Names "ClearExpense", "ClearMiles" and "ClearTime", it would be better to have three workSHEET-level Defined Names (each called "tblRangeToClear") - one for each of the worksheets "ExpenseEnter", "MilesEnter" and "TimeEnter".

Once you've created these names (and deleted the original ones) you can then use the slightly neater version of the following routine instead of the one I posted previously:

Private Sub ResetSheets()

    Dim vSheetName  As Variant
    Dim sSheetName  As String
    Dim chk         As CheckBox
    Dim wks         As Worksheet

    With ThisWorkbook

        For Each vSheetName In Array("ExpenseEnter", "MilesEnter", "TimeEnter")

            sSheetName = CStr(vSheetName)

            Set wks = ThisWorkbook.Worksheets(sSheetName)

'           Clear data values
            wks.Range("tblRangeToClear").ClearContents

'           Reset CheckBoxes
            For Each chk In wks.CheckBoxes
                chk.Value = False
            Next chk

        Next vSheetName

    End With

End Sub

Hope this helps - as always, please keep me informed.

Regards,

Greg M

AWESOME!!! Replaced the last section as you suggested and also removed the trailing slash. Everything works beautifully!!! Thank you, thank you, thank you

Hi again,

Many thanks for your feedback.

You're very welcome - glad I was able to help.

Regards,

Greg M