Clearing Userform Data, After populating "dataSheet" or Getting "Clear Button" to Work on UserForm

Bill Williamson

New Member
My First time posting, Newbie At VBA. I have been trying to get Userform to clear automatically after hitting OK Button, but also want it to work when Hitting the Clear Button. I have tried Numerous ways to get this to work But, they didn't so I "Commentted them out" so not to interfere with the userform working..



Code:
'Private Sub Clear_Form()


'For Each Ctrl In Me.Controls
 '   Select Case TypeName(Controls)
    
  '      Case "TextBox"
   '         Ctrl.Text = ""
            
    '    Case "ComboBox"
     '       Ctrl.ListIndex = -1
            
      '  Case "CheckBox"
       '     Ctrl.Value = False
            
'End Select


'Next
'End Sub








Private Sub ApperanceCheckBox_Click()


End Sub


Private Sub BRReviewCheckBox_Click()


End Sub


Private Sub BRReview_Click()


End Sub


Private Sub CancelButton_Click()
Unload Me


End Sub






Private Sub CommandButton1_Click()
'Show User form
UserForm1.Show


End Sub


Private Sub ClearButton_Click()




'Sub Clear_Form()


'For Each Ctrl In Me.Controls
 '   Select Case TypeName(Controls)
    
  '      Case "TextBox"
   '         Ctrl.Text = ""
            
    '    Case "ComboBox"
     '       Ctrl.ListIndex = -1
            
      '  Case "CheckBox"
       '     Ctrl.Value = False
            
'End Select


'Next


            
'End Sub




UserForm1_Initialize


'Clear Information from UserForm
'Set Customer.Value = False
'Set CSONumber.Value = ""
'Set JobNumber.Value = ""
'Set PCWeldType.Value = ""
'Set PCWeldGrind.Value = ""
'Set PCFinish.Value = ""
'Set NonPCWeld.Value = ""
'Set NonPCGrind.Value = ""
'Set NonPCFinish.Value = ""


'Set BRReview.Value = False


'Set BOMReview.Value = False


'Set DimReview.Value = False


'Set WeldReview.Value = False
  
'Set Apperance.Value = False
 
'Set Complete.Value = False




    
End Sub






Private Sub UserForm1_Click()
End Sub




Private Sub UserForm1_Initialize()




End Sub
    


Private Sub OKButton_Click()
Dim EmptyRow As Long
'Make Sheet1 Active
    Sheet1.Activate


'Determine Empty Row
EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1




'Transfer Information
Cells(EmptyRow, 1).Value = Customer.Value
Cells(EmptyRow, 2).Value = CSONumber.Value
Cells(EmptyRow, 3).Value = JobNumber.Value
Cells(EmptyRow, 4).Value = PCWeldType.Value
Cells(EmptyRow, 5).Value = PCWeldGrind.Value
Cells(EmptyRow, 6).Value = PCFinish.Value
Cells(EmptyRow, 7).Value = NonPCWeld.Value
Cells(EmptyRow, 8).Value = NonPCGrind.Value
Cells(EmptyRow, 9).Value = NonPCFinish.Value


If BRReview.Value = True Then Cells(EmptyRow, 10).Value = "Yes"
If BRReview.Value = False Then Cells(EmptyRow, 10).Value = "No"


If BOMReview.Value = True Then Cells(EmptyRow, 11).Value = "Yes"
If BOMReview.Value = False Then Cells(EmptyRow, 11).Value = "No"


If DimReview.Value = True Then Cells(EmptyRow, 12).Value = "Yes"
If DimReview.Value = False Then Cells(EmptyRow, 12).Value = "No"


If WeldReview.Value = True Then Cells(EmptyRow, 13).Value = "Yes"
If WeldReview.Value = False Then Cells(EmptyRow, 13).Value = "No"
  
If Apperance.Value = True Then Cells(EmptyRow, 14).Value = "Yes"
If Apperance.Value = False Then Cells(EmptyRow, 14).Value = "No"
 
If Complete.Value = True Then Cells(EmptyRow, 15).Value = "Yes"
If Complete.Value = False Then Cells(EmptyRow, 15).Value = "No"


End Sub


Private Sub UserForm_Click()


Call UserForm1_Initialize






End Sub


Private Sub UserForm_Initialize()




'Clear Information from UserForm
'Set Customer.Value = ""
'Set CSONumber.Value = ""
'Set JobNumber.Value = ""
'Set PCWeldType.Value = ""
'Set PCWeldGrind.Value = ""
'Set PCFinish.Value = ""
'Set NonPCWeld.Value = ""
'Set NonPCGrind.Value = ""
'Set NonPCFinish.Value = ""


End Sub


Thanks for any help on this.

Bill Williamson
 

DanteAmor

Well-known Member
Hi @★ Bill Williamson, welcome to the forum

Change control by ctrl:

Code:
Sub Clear_Form()
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName([COLOR=#ff0000]ctrl[/COLOR])
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
End Sub
 

Bill Williamson

New Member
Thank you for the Welcome and the help.
Doesnt look like I was too far off track. But Do I put the new code Under General Catagory or under the Clear Button "Click" area?
 

DanteAmor

Well-known Member
Thank you for the Welcome and the help.
Doesnt look like I was too far off track. But Do I put the new code Under General Catagory or under the Clear Button "Click" area?

Code:
[COLOR=#0000ff]Private Sub ClearButton_Click[/COLOR]
  Dim ctrl As MSForms.Control
  For Each ctrl In Me.Controls
    Select Case TypeName(ctrl)
      Case "TextBox"
        ctrl.Text = ""
      Case "ComboBox"
        ctrl.ListIndex = -1
      Case "CheckBox"
        ctrl.Value = False
    End Select
  Next
End Sub
 

Bill Williamson

New Member
Dante,

That Worked Perfectly, I also added it at the bottom of my OK Button_Click so after it stores the data, it clears the form. You helped with both. Thank you Very Much.

Im not sure why it wouldn't work with Userform_Initialize, obviously I dont have it set up correctly.


One more quick question, Can I use Vlookup in a Userform, Say I wanted to lookup up records based on either, Customer, CSO# or Job# so I can update check Boxes or other data.

I have done it in regular excell sheets.



Thank you so much,

Bill Williamson
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top