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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
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
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
I guess what I need is a code to input the info from the userform to the header. Is this very clear???
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Can you post your current Header code? Someone can amend it after that.

Smitty
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
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
 

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321

ADVERTISEMENT

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.
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
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.
 

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
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.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,999
Messages
5,834,809
Members
430,323
Latest member
Regash

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
Top