reset or empty a userform

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
In my macro I have a userform to filter data. sometimes after I use the macro and then run it later the previously entered data will show back up in the userform. what can i put in my code to clear the data after it filters???
thanks
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, royboy531,

to my sense we need some more info
what's the code you use for the userform ?
(initialize or activate event?)
what controls do you want to clear ?

perhaps you feed the userform with some public variables which are not reset ?

kind regards,
Erik
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
for example if you have textbox named tbStartDate and want the data in it to be dissappeared after clicking a button, on the code you should say

Code:
tbStartDate=""
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
What happens if you Unload the form (e.g. Unload UserForm1) and then .Show it again? It should clear any previous data. HTH
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

iggydarsa said:
for example if you have textbox named tbStartDate and want the data in it to be dissappeared after clicking a button, on the code you should say

Code:
tbStartDate=""

there was another question which is probably more important
what's the code you use for the userform ?
(initialize or activate event?)
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
here is the code I have for the userform
Code:
Private Sub btnFilter_Click()
Dim startDate As String
Dim shift As String
    
    'reads date from the userform
    startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
    shift = tbshift 'tbshift is the name of the textbox for shift
    
    Application.DisplayAlerts = False
    
    'FILTERS
    With Worksheets("sheet1")
        .AutoFilterMode = False
        .Range("a1:p1").AutoFilter
        .Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift
        .Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
       UserForm1.Hide
        Range("A1:P" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
    End With

If I used tbstartdate="" where do I put it and do I also need to have one for the tbshift??
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647

ADVERTISEMENT

There should be "End Sub" you can type it right before that... but i think the real problem might be the Hide comment... If I am mistaken please somebody correct me but Hiding doesnt unloads the form and consumes memory... so I would prefer to use "Unload me" instead of "Userform1.Hide"
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
what would be the exact code for unload me?? Unload.Me??

I do still want the useform to go away when I click the command button.
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Or Unload with the name of the form. When I use Unload Me, I've had some unpredictable results from time to time. Perhaps I just wasn't using it right, when I first started playing with userforms, but I've gotten out of the habit of using Me. Just my $0.02
 

Watch MrExcel Video

Forum statistics

Threads
1,118,722
Messages
5,573,818
Members
412,551
Latest member
soking
Top