userform in a macro

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I have a macro that will put a header on a page. I want to be able to have a userform come up to input a specific date onto the header that already had a title that is alway there. Is there a way I can do this???
thanks
royboy531
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
yes this can be done. You could use a userform or a inputbox to do this. Create your userform and in macro load userform vbmodal
 
Upvote 0
I guess what I need is a code to input the info from the userform to the header. Is this very clear???
 
Upvote 0
Roy

The best place to start for this would be to record a macro when you set the header.

This would give you the code to do that, and then you would need to adapt it to work with the userform.

Somemore information would be helpful.
 
Upvote 0
Ok, here is part of the macro that involves the header code. After the header part that says "Quality......week ending" I want my userform to come up and ask for a date. I have a userform created already titled userform3. here is the code
Code:
   With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Quality Control Data Summary, Week Ending"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = -3
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 6
    End With
I apriciate everyone's help, thanks
royboy531
 
Upvote 0
I still think you are better off with an input box. If you want to load the userform at this point, just do the following:

Code:
   With ActiveSheet.PageSetup 
        .LeftHeader = "" 
        .CenterHeader = "Quality Control Data Summary, Week Ending" 
              userform3.show vbmodal
        .RightHeader = "" 
        .LeftFooter = "" 
        .CenterFooter = "Page &P" 
        .RightFooter = "" 
        .LeftMargin = Application.InchesToPoints(0.25) 
        .RightMargin = Application.InchesToPoints(0.25) 
        .TopMargin = Application.InchesToPoints(1) 
        .BottomMargin = Application.InchesToPoints(1) 
        .HeaderMargin = Application.InchesToPoints(0.5) 
        .FooterMargin = Application.InchesToPoints(0.5) 
        .PrintHeadings = False 
        .PrintGridlines = False 
        .PrintComments = xlPrintNoComments 
        .PrintQuality = -3 
        .CenterHorizontally = True 
        .CenterVertically = False 
        .Orientation = xlPortrait 
        .Draft = False 
        .PaperSize = xlPaperLetter 
        .FirstPageNumber = xlAutomatic 
        .Order = xlDownThenOver 
        .BlackAndWhite = True 
        .Zoom = False 
        .FitToPagesWide = 1 
        .FitToPagesTall = 6 
    End With

if you want to use the inputbox instead, you can change the:
Code:
userform3.show vbmodal
to:
Code:
redoinput:
inputdate = inputbox("What is the Date?")
     if isdate(inputdate) = false then goto redoinput
'inputdate is now your date that you can use in your code elsewhere.
 
Upvote 0
I used the userform because I already had one created. What code will I use in the userform to complete the action of putting the information into the header. Thanks for helping.
 
Upvote 0
I couldn't get the code for the userform so I tried to use a input box. It seemed to work somewhat but when I put inputdate at the end of my header do I need to do put it in "" or something. when it finished it just said inputdate. thanks for the help.
 
Upvote 0
How's this:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> PageSetup()
   <SPAN style="color:#00007F">Dim</SPAN> ans <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

        ans = InputBox("Please enter the Week Ending Date", "Week Ending Date", Date)
  
        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup
             .CenterHeader = "Quality Control Data Summary, Week <SPAN style="color:#00007F">End</SPAN>ing " & ans
             .CenterFooter = "Page &P"
             .LeftMargin = Application.InchesToPoints(0.25)
             .RightMargin = Application.InchesToPoints(0.25)
             .PrintComments = xlPrintNoComments
<SPAN style="color:#007F00">'             .PrintQuality = -3</SPAN>
             .CenterHorizontally = <SPAN style="color:#00007F">True</SPAN>
             .Orientation = xlPortrait
             .BlackAndWhite = <SPAN style="color:#00007F">True</SPAN>
             .FitToPagesWide = 1
             .FitToPagesTall = 6
         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        
End <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Also note that with Page Setup it's a good idea to take out any default settings as they'll just slow the code down.

Smitty
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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