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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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,057
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,090
Members
412,441
Latest member
kelethymos
Top