CODEWITHSUNDEEP
vbaexcel
Guaca24
Guaca24

Reputation: 25

Using a clear button to clear cells, checkboxes, and comboboxes in a separate workbook

I am working with about 10 separate excel workbooks and one tracking workbook. I have clear buttons on all 10 workbooks but would like a clear all button on the tracking one so my employees don't have to open each individual one to clear its contents. The VBA code in each workbook is shown below:

Private Sub CommandButton1_Click()
    Sheet1.ComboBox172.Text = "-"
    Sheet1.ComboBox174.Text = "-"
    Sheet1.ComboBox175.Text = "-"

    Sheet1.CheckBox1.Value = False
    Sheet1.CheckBox2.Value = False
    Sheet1.CheckBox3.Value = False

    Sheet1.TextBox1.Text = ""
    Sheet1.TextBox2.Text = ""
    Sheet1.TextBox3.Text = ""
    Sheet1.TextBox4.Text = ""
    Sheet1.TextBox5.Text = ""
    Sheet1.TextBox6.Text = ""
    Sheet1.TextBox7.Text = ""
    Sheet1.TextBox9.Text = ""
    Sheet1.TextBox10.Text = ""
    Sheet1.TextBox11.Text = ""
    Sheet1.TextBox12.Text = ""

    Sheet1.Range("D9:D21").Value = "-"
    Sheet1.Range("D25:D36").Value = "-"
    Sheet1.Range("D42:D52").Value = "-"
    Sheet1.Range("D56:D60").Value = "-"
    Sheet1.Range("D65:D67").Value = "-"
    Sheet1.Range("D71:D76").Value = "-"
    Sheet1.Range("D80:D83").Value = "-"
    Sheet1.Range("D87:D91").Value = "-"
    Sheet1.Range("D95:D100").Value = "-"
    Sheet1.Range("D104:D106").Value = "-"
    Sheet1.Range("L9:L20").Value = "-"
    Sheet1.Range("L25:L35").Value = "-"
    Sheet1.Range("L42:L52").Value = "-"
    Sheet1.Range("L56:L61").Value = "-"
    Sheet1.Range("L65:L67").Value = "-"
    Sheet1.Range("L71:L75").Value = "-"
    Sheet1.Range("L80:L82").Value = "-"
    Sheet1.Range("L87:L90").Value = "-"
    Sheet1.Range("L95:L100").Value = "-"

    Dim Shp As Shape

    For Each Shp In Sheet1.Shapes
        If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl Or Shp.Type = msoPicture) Then Shp.Delete
    Next Shp

    For Each Shp In Sheet2.Shapes
        If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl Or Shp.Type = msoPicture) Then Shp.Delete
    Next Shp

    Sheet2.ComboBox2.Text = "-"
    Sheet2.ComboBox3.Text = "-"
    Sheet2.ComboBox4.Text = "-"

    Sheet2.CheckBox1.Value = False
    Sheet2.CheckBox2.Value = False
    Sheet2.CheckBox3.Value = False
    Sheet2.CheckBox4.Value = False
    Sheet2.CheckBox5.Value = False
    Sheet2.CheckBox8.Value = False
    Sheet2.CheckBox9.Value = False
    Sheet2.CheckBox10.Value = False
    Sheet2.CheckBox11.Value = False

    Sheet2.Range("F9:F9").Value = 0
    Sheet2.Range("F11:F11").Value = 0
    Sheet2.Range("F15:F15").Value = 0
    Sheet2.Range("F17:F17").Value = 0
    Sheet2.Range("F21:F21").Value = 0
    Sheet2.Range("F23:F23").Value = 0
    Sheet2.Range("F27:F27").Value = 0
    Sheet2.Range("F29:F29").Value = 0
    Sheet2.Range("F35:F35").Value = 0
    Sheet2.Range("F37:F37").Value = 0
    Sheet2.Range("F39:F39").Value = 0
    Sheet2.Range("F45:F45").Value = 0
    Sheet2.Range("F47:F47").Value = 0
    Sheet2.Range("F53:F53").Value = 0
    Sheet2.Range("F55:F55").Value = 0
    Sheet2.Range("K35:K35").Value = 0
    Sheet2.Range("J9:M9").ClearContents
    Sheet2.Range("J15:M15").ClearContents
    Sheet2.Range("J21:M21").ClearContents
    Sheet2.Range("J27:M27").ClearContents

    Sheet3.ComboBox1.Text = "-"
    Sheet3.ComboBox2.Text = "-"
    Sheet3.ComboBox3.Text = "-"
    Sheet3.ComboBox4.Text = "-"
    Sheet3.ComboBox6.Text = "-"

    Sheet3.TextBox1.Text = ""
    Sheet3.TextBox2.Text = ""
    Sheet3.TextBox3.Text = ""
    Sheet3.TextBox5.Text = ""
    Sheet3.TextBox6.Text = ""

    Sheet3.CheckBox1.Value = False
    Sheet3.CheckBox2.Value = False
    Sheet3.CheckBox3.Value = False
    Sheet3.CheckBox4.Value = False
    Sheet3.CheckBox5.Value = False
    Sheet3.CheckBox6.Value = False
    Sheet3.CheckBox7.Value = False
    Sheet3.CheckBox8.Value = False
    Sheet3.CheckBox9.Value = False
    Sheet3.CheckBox10.Value = False
    Sheet3.CheckBox11.Value = False
    Sheet3.CheckBox12.Value = False
    Sheet3.CheckBox13.Value = False
    Sheet3.CheckBox14.Value = False
    Sheet3.CheckBox15.Value = False
    Sheet3.CheckBox16.Value = False
    Sheet3.CheckBox17.Value = False
    Sheet3.CheckBox18.Value = False
    Sheet3.CheckBox19.Value = False
    Sheet3.CheckBox20.Value = False
    Sheet3.CheckBox21.Value = False
    Sheet3.CheckBox22.Value = False
    Sheet3.CheckBox23.Value = False
    Sheet3.CheckBox24.Value = False
    Sheet3.CheckBox25.Value = False
    Sheet3.CheckBox26.Value = False
    Sheet3.CheckBox27.Value = False
    Sheet3.CheckBox28.Value = False
    Sheet3.CheckBox29.Value = False
    Sheet3.CheckBox30.Value = False
    Sheet3.CheckBox31.Value = False
    Sheet3.CheckBox32.Value = False
    Sheet3.CheckBox33.Value = False
    Sheet3.CheckBox34.Value = False
    Sheet3.CheckBox35.Value = False
    Sheet3.CheckBox36.Value = False
    Sheet3.CheckBox37.Value = False
    Sheet3.CheckBox38.Value = False
    Sheet3.CheckBox39.Value = False
    Sheet3.CheckBox41.Value = False
    Sheet3.CheckBox43.Value = False

    Sheet3.Range("C9:C9").Value = ""
    Sheet3.Range("C11:C11").Value = ""
    Sheet3.Range("H9:H9").Value = ""
    Sheet3.Range("H11:H11").Value = ""
    Sheet3.Range("L9:L9").Value = ""
    Sheet3.Range("L11:L11").Value = ""
    Sheet3.Range("L17:N17").Value = 0
    Sheet3.Range("L18:N18").Value = 0
    Sheet3.Range("L19:N19").Value = 0
    Sheet3.Range("L20:N20").Value = 0
End Sub

I am having an issue with the clear button on the tracking sheet referencing any of the other workbooks.

Upvotes: 1

Views: 156

Answers (1)

John Coleman
John Coleman

Reputation: 52008

What you could do is to create a sub with a workbook parameter that clears the passed workbook. Something like:

Sub ClearWorkBook(wb As Workbook)
    Dim Shp As Shape

    wb.Sheets("Sheet1").ComboBox172.Text = "-"
    wb.Sheets("Sheet1").ComboBox174.Text = "-"
    wb.Sheets("Sheet1").ComboBox175.Text = "-"

    wb.Sheets("Sheet1").CheckBox1.Value = False
    wb.Sheets("Sheet1").CheckBox2.Value = False
    wb.Sheets("Sheet1").CheckBox3.Value = False

    wb.Sheets("Sheet1").TextBox1.Text = ""
    wb.Sheets("Sheet1").TextBox2.Text = ""
    wb.Sheets("Sheet1").TextBox3.Text = ""
    wb.Sheets("Sheet1").TextBox4.Text = ""
    wb.Sheets("Sheet1").TextBox5.Text = ""
    wb.Sheets("Sheet1").TextBox6.Text = ""
    wb.Sheets("Sheet1").TextBox7.Text = ""
    wb.Sheets("Sheet1").TextBox9.Text = ""
    wb.Sheets("Sheet1").TextBox10.Text = ""
    wb.Sheets("Sheet1").TextBox11.Text = ""
    wb.Sheets("Sheet1").TextBox12.Text = ""

    wb.Sheets("Sheet1").Range("D9:D21,D25:D36,D42:D52,D56:D60,D65:D67,D71:D76,D80:D83,D87:D91,D95:D100,D104:D106,L9:L20,L25:L35,L42:L52,L56:L61,L65:L67,L71:L75,L80:L82,L87:L90,L95:L100").Value = "-"

    For Each Shp In wb.Sheets("Sheet1").Shapes
        If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl Or Shp.Type = msoPicture) Then Shp.Delete
    Next Shp

    For Each Shp In wb.Sheets("Sheet2").Shapes
        If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl Or Shp.Type = msoPicture) Then Shp.Delete
    Next Shp

    wb.Sheets("Sheet2").ComboBox2.Text = "-"
    wb.Sheets("Sheet2").ComboBox3.Text = "-"
    wb.Sheets("Sheet2").ComboBox4.Text = "-"

    wb.Sheets("Sheet2").CheckBox1.Value = False
    wb.Sheets("Sheet2").CheckBox2.Value = False
    wb.Sheets("Sheet2").CheckBox3.Value = False
    wb.Sheets("Sheet2").CheckBox4.Value = False
    wb.Sheets("Sheet2").CheckBox5.Value = False
    wb.Sheets("Sheet2").CheckBox8.Value = False
    wb.Sheets("Sheet2").CheckBox9.Value = False
    wb.Sheets("Sheet2").CheckBox10.Value = False
    wb.Sheets("Sheet2").CheckBox11.Value = False

    wb.Sheets("Sheet2").Range("F9,F11,F15,F17,F21,F23,F27,F29,F35,F37,F39,F45,F47,F53,F55,K35").Value = 0
    wb.Sheets("Sheet2").Range("J9:M9,J15:M15,J21:M21,J27:M27").ClearContents

    wb.Sheets("Sheet3").ComboBox1.Text = "-"
    wb.Sheets("Sheet3").ComboBox2.Text = "-"
    wb.Sheets("Sheet3").ComboBox3.Text = "-"
    wb.Sheets("Sheet3").ComboBox4.Text = "-"
    wb.Sheets("Sheet3").ComboBox6.Text = "-"

    wb.Sheets("Sheet3").TextBox1.Text = ""
    wb.Sheets("Sheet3").TextBox2.Text = ""
    wb.Sheets("Sheet3").TextBox3.Text = ""
    wb.Sheets("Sheet3").TextBox5.Text = ""
    wb.Sheets("Sheet3").TextBox6.Text = ""

    wb.Sheets("Sheet3").CheckBox1.Value = False
    wb.Sheets("Sheet3").CheckBox2.Value = False
    wb.Sheets("Sheet3").CheckBox3.Value = False
    wb.Sheets("Sheet3").CheckBox4.Value = False
    wb.Sheets("Sheet3").CheckBox5.Value = False
    wb.Sheets("Sheet3").CheckBox6.Value = False
    wb.Sheets("Sheet3").CheckBox7.Value = False
    wb.Sheets("Sheet3").CheckBox8.Value = False
    wb.Sheets("Sheet3").CheckBox9.Value = False
    wb.Sheets("Sheet3").CheckBox10.Value = False
    wb.Sheets("Sheet3").CheckBox11.Value = False
    wb.Sheets("Sheet3").CheckBox12.Value = False
    wb.Sheets("Sheet3").CheckBox13.Value = False
    wb.Sheets("Sheet3").CheckBox14.Value = False
    wb.Sheets("Sheet3").CheckBox15.Value = False
    wb.Sheets("Sheet3").CheckBox16.Value = False
    wb.Sheets("Sheet3").CheckBox17.Value = False
    wb.Sheets("Sheet3").CheckBox18.Value = False
    wb.Sheets("Sheet3").CheckBox19.Value = False
    wb.Sheets("Sheet3").CheckBox20.Value = False
    wb.Sheets("Sheet3").CheckBox21.Value = False
    wb.Sheets("Sheet3").CheckBox22.Value = False
    wb.Sheets("Sheet3").CheckBox23.Value = False
    wb.Sheets("Sheet3").CheckBox24.Value = False
    wb.Sheets("Sheet3").CheckBox25.Value = False
    wb.Sheets("Sheet3").CheckBox26.Value = False
    wb.Sheets("Sheet3").CheckBox27.Value = False
    wb.Sheets("Sheet3").CheckBox28.Value = False
    wb.Sheets("Sheet3").CheckBox29.Value = False
    wb.Sheets("Sheet3").CheckBox30.Value = False
    wb.Sheets("Sheet3").CheckBox31.Value = False
    wb.Sheets("Sheet3").CheckBox32.Value = False
    wb.Sheets("Sheet3").CheckBox33.Value = False
    wb.Sheets("Sheet3").CheckBox34.Value = False
    wb.Sheets("Sheet3").CheckBox35.Value = False
    wb.Sheets("Sheet3").CheckBox36.Value = False
    wb.Sheets("Sheet3").CheckBox37.Value = False
    wb.Sheets("Sheet3").CheckBox38.Value = False
    wb.Sheets("Sheet3").CheckBox39.Value = False
    wb.Sheets("Sheet3").CheckBox41.Value = False
    wb.Sheets("Sheet3").CheckBox43.Value = False

    wb.Sheets("Sheet3").Range("C9,C11,H9,H11,L(,L11").Value = ""
    wb.Sheets("Sheet3").Range("L17:N17,L18:N18,L19:N19,L20:N20").Value = 0

End Sub

This workbook would be stored in the tracking workbook. Then in that tracking workbook you would have something like:

Sub ClearWorkBooks()
    Dim wb As Workbook
    For Each wb In Workbooks
        If wb.Name <> "Tracking" Then ClearWorkBook wb
    Next wb
End Sub

Note that I streamlined the code by collapsing many of your statements into one by using noncontiguous ranges. Further streamlining is possible, though it is somewhat tangential to your question. You might have to do work on the loop in ClearWorkBooks() so that it only runs on the workbooks that you want to clear.

Upvotes: 1

Related Questions