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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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