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

Bill Williamson

Board Regular
Joined
Oct 7, 2019
Messages
124
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top