none
excel VBA checkbox clear

    Question

  • Hi

    Below is VBA code for claering a userform. I have two checkbox(s) on the form that are unchecked = false.

    If they are checked by the user I want the clear button to uncheck them or to remain unchecked if not checked.

    Code below clears all other boxes but not the checkbox.

    Could you point me in the right direction. My project is all most finished.

     

    Thanks

    Private Sub CmdclearA_Click()
    ' Clear the Form
    For Each Ctl In Me.Controls
    If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
    Ctl.Value = ""

    Comdate.Text = Format(Now() + 1, "DD/MM/YYYY")
    End If
    If Ckleave = True Then
     Ckleave = False
     End If
     
    Next Ctl

    End Sub

    Wednesday, November 16, 2011 1:45 PM

Answers

  • You should clear the check box outside the loop (and set ComDate outside the loop too):

    Private Sub CmdclearA_Click()
        ' Clear the Form
        For Each Ctl In Me.Controls
            If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
                Ctl.Value = ""
            End If
        Next Ctl
        Comdate.Text = Format(Now() + 1, "DD/MM/YYYY")
        If Ckleave = True Then
            Ckleave = False
        End If
    End Sub
    


    Regards, Hans Vogelaar
    • Marked as answer by bigger312 Wednesday, November 16, 2011 7:10 PM
    Wednesday, November 16, 2011 4:40 PM

All replies

  • You should clear the check box outside the loop (and set ComDate outside the loop too):

    Private Sub CmdclearA_Click()
        ' Clear the Form
        For Each Ctl In Me.Controls
            If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
                Ctl.Value = ""
            End If
        Next Ctl
        Comdate.Text = Format(Now() + 1, "DD/MM/YYYY")
        If Ckleave = True Then
            Ckleave = False
        End If
    End Sub
    


    Regards, Hans Vogelaar
    • Marked as answer by bigger312 Wednesday, November 16, 2011 7:10 PM
    Wednesday, November 16, 2011 4:40 PM
  • Thanks Hans.

    Worked perfect.

    Gerry

    Ps While I have you on the line Is there any was I can get this recorded macro to print (Now() + 1, "DD/MM/YYYY") instead of Range("A1:J16").

    Date is recorded in A1

    Private Sub Comprintpatrol_Click()
    '
    ' printpatrol Macro
    '

    '
        Worksheets("Patrol").Range("A1:J16").Select
        Selection.PrintOut Copies:=1

    End Sub

    This see my protect finished

    THanks Again

    Gerry

    Wednesday, November 16, 2011 7:10 PM
  • You could use

     

    Private Sub Comprintpatrol_Click()
        With Worksheets("Patrol").Range("A1:J16")
            .Value = Format(Date + 1, "DD/MM/YYYY")
            .PrintOut Copies:=1
        End With
    End Sub


    Regards, Hans Vogelaar
    • Marked as answer by bigger312 Wednesday, November 16, 2011 7:55 PM
    • Unmarked as answer by bigger312 Thursday, November 17, 2011 12:46 PM
    Wednesday, November 16, 2011 7:40 PM
  • Thanks Again

    Thats Great

     

    Gerry

    Wednesday, November 16, 2011 7:55 PM
  • You could use

     

    Private Sub Comprintpatrol_Click()
        With Worksheets("Patrol").Range("A1:J16")
            .Value = Format(Date + 1, "DD/MM/YYYY")
            .PrintOut Copies:=1
        End With
    End Sub

     


    Regards, Hans Vogelaar


    I tried this code and it filled all the cells ("A1:J16") in worksheet patrol with tomorrows date and printed that.

    The task I am trying to achieve is to print entries for tomorrows date only from the Patrol worksheet.

    Thanks

    Thursday, November 17, 2011 12:50 PM
  • I don't really understand your setup and what exactly you're trying to accomplish. Could you provide more detailed information? Thanks!
    Regards, Hans Vogelaar
    Thursday, November 17, 2011 4:46 PM
  • The Code at present prints a range based on cells ("A1:J16") in a Sheet name patrol.

    In This sheet the user imputs (via Userform) data for tomorrows date so the sheet Column A stores the date but also older dates in this colum where data was  previously entered.

    What I am hoping to do is create VBA code to print from the sheet (patrol) based on entries  for tomorrows date only (Date + 1, "DD/MM/YYYY") based on the date in column A.

    Date Surname Forename I.D Shift No of Deliveries Product1 Product2 Product3 Product4
    19-Nov-11 Barker Colin 21719 9am 25 10 5 6 4
    19-Nov-11 Browne Valerie 21720 9am 30 10 15 5 10
    19-Nov-11 Buttner Gerard 21931 7am 1   1    
    19-Nov-11 Collins Cathal 21700 9am 7 3     4
    18-Nov-11 Browne Valerie 21720 2pm 19 9 5 2 3
    18-Nov-11 Buttner Gerard 21931 9am 12 6   4 2

    In this example I want to print the first 4 entries my problem is that the number of entries for tomorrows date can vary from 1 to 30 plus. So using a range("A1:J16") will not give me the required result.

    I hope this clarifies my problem. Sorry for the delay in getting back to you.

    Thanks Again for your time an input.

    Gerry

     

     

    Friday, November 18, 2011 12:58 PM
  • You could filter the range, print it, then remove the filter again:

    Sub PrintTomorrowsRecords()
        Range("A1").AutoFilter Field:=1, Criteria1:=Format(Date + 1, Range("A2").NumberFormatLocal)
        ActiveSheet.PrintOut
        ' or
        Range("A1").CurrentRegion.PrintOut
        Range("A1").AutoFilter
    End Sub
    
    


    Regards, Hans Vogelaar
    Friday, November 18, 2011 1:28 PM