Excel 2000 Header 1

[Superbaker]

Is there a way to display the month and year in a header. I don't want the full date so is there a way to put just the month and year.

THanks

James

 

[Wray69]

Check out this microsoft KB article;

http://support.microsoft.com/default.aspx?scid=kb;en-us;213510

To use a date or time format other than the default format, use any of the following methods:
Enter the date or time manually in a different format.

- or -
Create a macro to change the format.

- or -
Change the short date or time format in Control Panel. This can be done by using the International, Regional Settings, or Regional Options option in Control Panel.

Regards -

Wray

 

[Superbaker]

Is there an easier way to do this. &[Date] puts in the date. Is there a similiar thing like &[Month} or something like that?

 

[DaleWatson123321]

James,

If you want a solution that provides EXACTLY what you require - Month Year - e.g. April 2003, then the following will work...

Sub SetPrintHeadings()
h_l = Range("hdr_l&quot.Value
h_c = Range("hdr_c&quot.Value
h_r = Range("hdr_r&quot.Value
With ActiveSheet.PageSetup
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
End With
End Sub

The above example demonstrates that you can, if desired, pick up from your worksheet any one or all three of the headers (left, center, right).

For this example, I've created 3 range names for 3 separate cells on a separate sheet (out of the way). The names are: hdr_l, hdr_c, and hdr_r. You will of course need to create the same range names to fit with the VBA routine.

In one of these named cells, copy the following formula...

=CHOOSE(MONTH(TODAY()),"January","February","March","April","May","June","July","August","September","October","November","December&quot&" "&FIXED(1900+(YEAR(TODAY())-1900),0,TRUE)

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

James,

I'm doing a "follow-up" on my suggestion, just in case you missed it.

I'm confident that once you try the solution I offered, you'll be pleased with the results.

Can you please advise as how how you made out ?

Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Superbaker]

I have been busy so I just had a chance to try it. I am not sure what you mean by this part.

For this example, I've created 3 range names for 3 separate cells on a separate sheet (out of the way). The names are: hdr_l, hdr_c, and hdr_r. You will of course need to create the same range names to fit with the VBA routine.

In one of these named cells, copy the following formula...

=CHOOSE(MONTH(TODAY()),"January","February","March","April","May","June","July","August","September","October","November","December&quot&" "&FIXED(1900+(YEAR(TODAY())-1900),0,TRUE)

I know to copy the =choose portion into a cell but I am not sure what you mean above that. I dont know anything about macro's. I have inserted this already:

Sub SetPrintHeadings()
h_l = Range("hdr_l&quot.Value
h_c = Range("hdr_c&quot.Value
h_r = Range("hdr_r&quot.Value
With ActiveSheet.PageSetup
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
End With
End Sub

I am not sure where to go from there.

 

[DaleWatson123321]

James,

hdr_l, hdr_c, and hdr_r are arbitrary "range names" (one can use whatever one prefers) that I used to identify the
cells where you can place the information you want to include in the left-header, center-header, or right-header. If you plan on using less than all three header positions, then you can eliminate the one(s) not required.

The VBA routine will "pick up" the data from these 3 (named) cells and place that data in the header positions.

You're certainly not the only one who doesn't know about the use of "range names". One of the MOST IMPORTANT items often NOT taught or adequately emphasized by Microsoft and/or teaching institutions, is the use of "RANGE NAMES".

Range Names are EXTREMELY important for a variety of reasons. I'll list a couple...

1) By creating and using names, you can easily make your workbook much more &quot;user-friendly&quot; - both for yourself and others. Generally, one should frequently assign names to all the various parts (ranges) of a spreadsheet application. Then, these names can be used to: a) go to the various locations - by hitting the &quot;GoTo&quot; key <F5>, followed by the range name, and <Enter>. Normally, the naming practice followed is to assign names that relate to the type of data located in the various ranges. For example, you might assign the name &quot;Expenses&quot; to a range containing expense data, or &quot;Revenue&quot; to revenue data, etc. Or, you might decide to use abbreviations - e.g. &quot;Exp&quot; or &quot;Rev&quot;, or even simply &quot;e&quot; and &quot;r&quot;.

2) If you have various ranges that you've assigned names to, you can easily reference these names when deciding to select a range for printing.

3) VBA programming is especially made MUCH, MUCH easier when range names are used. If programmers do NOT use range names, they will use what is commonly referred to as &quot;hard code&quot; to refer to the cell coordinates. &quot;Hard code&quot; is code that does NOT change on its own, and therefore needs to be changed by the programmer whenever modifications are made to an application. With the use of range names, Excel &quot;internally&quot; maintains a &quot;link&quot; between the names and the cells to which the names are assigned. Therefore, whenever rows/columns are inserted/deleted, or data is moved from place to place, the range names are &quot;automatically&quot; kept &quot;in sync&quot;. Thus there is no need for VBA code to be changed. Especially in the case of larger applications, it can amount to a HUGE saving in time for programmers if they have used range names.

While there are different methods of creating range names, the method I always recommend is the following...
a) Highlight the cell or range-of-cells (to be named)
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

Once you've created the range names hdr_l, hdr_c, and hdr_r, copy the formula into one of these cells.

Then run the routine. You could assign the routine to a &quot;macro button&quot;. The macro button is located on the &quot;Forms&quot; toolbar. You can either add the &quot;Forms&quot; toolbar to the top of your screen - by using (from the menu) Tools - Customize. Then click on the &quot;Toolbars&quot; tab, and then click (check off) &quot;Forms&quot;.

Once you have the toolbar, click the &quot;macro button&quot; icon, and then click-and-drag on your worksheet to create the size of button you prefer. You can edit the button to change the name. To assign your VBA code (macro) to the button, right-click on the button, and choose &quot;Assign macro&quot;.

Another option to activating your macro, is to use a &quot;hotkey&quot;. To do this, hold down the <Alt> key and hit <F8>. Click on your VBA routine name, and the click on &quot;Options&quot;. Then opposite &quot;Shortcut key&quot;, enter a letter. You'll notice it will say &quot;Ctrl+&quot; - which means you can then activate your macro by holding down the <Control> key and hitting the letter you've chosen. Naturally, you do NOT want to assign your macro to a letter that is commonly used - e.g. <Control> C for copying, <Control> V for pasting, etc. You also have the option to hold down the <Shift> key while you choose a letter. Then, to activate the macro, you would hold down BOTH the <Control> and <Shift> keys with your left hand, while you hit the letter with your right hand.

I hope all this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Superbaker]

That worked but I have a few more questions though. I used a hotkey to execute the macro. Do i need to keep the cells with the code in them on the sheet still or can I somehow hide it so its not seen. Also, will this macro run everytime the spreadsheet is opened up. Lastly, will this apply to all the sheets in the spreadsheet. Thanks for you help.

 

[DaleWatson123321]

James,

Sorry for the delay - I had to attend a meeting.

There is no need to keep the VBA code on the worksheet.

If by chance you're also referring to the cells with the header information (including the Month/Year formula), you should place this info on a SEPARATE sheet (and hide the sheet if you prefer).

As for the macro running every time you open the workbook, it currently will NOT run. If you DO want it to run, you can include it in a &quot;Workbook_Open&quot; event.

To do so, you would use <Alt> <F11> to enter the VBA Editor. Then on the left side (under the &quot;Project Explorer&quot; window) click on ThisWorkbook. Then copy and paste the following code... Note: If the Project Explorer window is not showing, you can open it, by clicking on (from the menu): View - Project Explorer.

In the following example, &quot;Sheet2&quot; is chosen as the Worksheet to have the header-settings set. This will only apply to worksheet(s) you specify - by code, or by use of your shortcut key - where you activate the code via the hotkey once you are on the worksheet(s) where you require the header settings.

Private Sub Workbook_Open()
Worksheets(&quot;Sheet2&quot.Select
Application.ScreenUpdating = False
h_l = Range(&quot;hdr_l&quot.Value
h_c = Range(&quot;hdr_c&quot.Value
h_r = Range(&quot;hdr_r&quot.Value
With ActiveSheet.PageSetup
.LeftHeader = h_l
.CenterHeader = h_c
.RightHeader = h_r
End With
End Sub

I hope this helps. &quot;Thanks&quot; for the STAR - it's appreciated.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca