Where to put code???

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I am trying to create a macro and I have a code that involves a userform. the userform filters data and the rest is copying, pasting, formating, and printing. anyway, do I need to put the whole code in the command button area of the userform or do I put it with the macro or both. thanks for the help.
royboy531
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
I think you've got 2 options.

1 Put the code in the Click event of the command button in the Userform module.

2 Put code to call the macro in the Click event.

Both options will do the same thing but one might be better to use than the other, dependent on what the code actually does.
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
I dont know what the code is about and how it is related to the form, but i can always suggest that you put the code with the macro, if anything you can always assign the button to that macro whenever you need it
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
this is the code for the command button-

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
    
    '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)
       
        Me.Hide
      
        
    End With
    
End Sub
this code brings up my userform and then filters my data. I use the macro(from a button on the toolbar) to start. the rest of the code then creates a new book and pastes the info into a format to print. I am not sure how to put all in the macro??? it starts "sub macro6()" then the userform has "Private Sub btnFilter_Click()". how do I put together?
thanks for the help.
royboy531
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
with a macro you call the userform (i.e. UserForm1.Show)and then the button on the form should have the code above


PS: I think instead of

Code:
.Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _ 
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
you can simply use
Code:
.Range("a1:p1").AutoFilter Field:=1, Criteria1:= startDate
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
iggydarsa, the macro I have is in a module. the code for the userform is in the command button of the userform. as it is right now I have the full code in the module and in the command button with the exception of the "sub macro6()" that is with the module. I tried just putting all of it in one or the other and split but I just can't get it to work unless I have it in both. I was wondering about "the button on the form should have the code above"?? confused a bit. the userform filters my data and needs to be at the beginning of the code, right???

as for the
Code:
.Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _ 
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
I tried to set it up previously with your suggestion and kept running into problems so I just left it this way. I will try it again though. thanks
royboy531
 

iggydarsa

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

Everytime we talk about this I get more confused :)

This is my suggestion: Send your file to my email address, and let me check which code is where, and where it supposed to be. If it is ok with you you can send it to iggydarsa@gmail.com.

Also about
Code:
.Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _ 
            Operator:=xlAnd, Criteria2:=("<=" & startDate)
try this in order to make it logical
Code:
.Range("a1:p1").AutoFilter Field:=1, Criteria1:= "=" & startDate
if it still doesnt work I guess you can always keep the original one that you always had.
 

Forum statistics

Threads
1,078,405
Messages
5,340,036
Members
399,348
Latest member
duel

Some videos you may like

This Week's Hot Topics

Top