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

[Bill Williamson]

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..

'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]

Hi @★ Bill Williamson, welcome to the forum

Change control by ctrl:

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]

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]

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?

[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]

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

 

[DanteAmor]

you can use vlookup but I recommend using the Find method

 

[Bill Williamson]

Thanks again, Great help.

Bill Williamson

 

[DanteAmor]

Youre welcome & thanks for the feedback.