PDA

View Full Version : Sleeper: Cell Value in Header?



Eltigre
11-29-2006, 12:16 PM
[[Administrative Edit]]: Split from "vbaexpress.com/forum/showthread.php?t=4856" and applied VBA tags.



I have been trying to implement Lucas' solution.

I know I'm probably committing a huge sin by not knowing anything about VBA prior to doing this... but what I did was go to Visual Basic Editor and pasted in Lucas' suggestion. I edited it as shown below to work for my worksheet (I use cell D3 instead). I expected the results to appear in the final printed worksheet and they don't. They also don't appear in print preview. I'm sure I'm missing something. Can you help? Please feel free to tell me to learn VBA first and then ask questions. I can take it. But if you could direct me to a help reference (note: I do not have MS visual basic help installed on my pc and don't have the install disks)

[Code]Option Explicit
'Add A1 from active sheet to each sheets's header
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("D3").Value
End Sub[/VCode]

Dave
11-29-2006, 07:28 PM
Place the code in "Thisworkbook" code not in the worksheet code. Right click "Thisworkbook" in the VB editor, select view code and then paste it there. HTH. Dave

lucas
11-29-2006, 08:15 PM
Sorry not to mention that. Dave is correct.
See attached example. Put what you want in D3 and print preview it to see the results.

To see the code, look for the ThisWorkbook module in the project explorer on the left of the visual basic editor. If the project explorer is not visable, look for it under view in the main menu.

Eltigre
11-30-2006, 09:40 AM
I left out an important piece of information...

My worksheet has an embedded query with a prompt. The user will refresh the data and enter a value (employee code). The value in column D (first cell is D3) changes with each new employee code entered.

The code to insert the cell value in header currently does not overwrite the existing value. I have to manually delete the existing left header text and then refresh the data.

I need to have any existing left header value replaced with the current/new value in D3. If the BeforePrint code won't do this, I'd probably have to have an additional code (upon file open) that clears out the left header value.

What do you think?

Tyger

ps: By the way, I am a newbie to this group (and to VBA obviously) and working with experts/peers like you is far superior to any help I could get elsewhere. There's no other person in my firm with Excel expertise. I truly appreciate y'all and your help.

lucas
11-30-2006, 10:04 AM
It updates for me for a change using a formula...
take a look and give us some feedback.
there is a formula in D3. change the text in cell G3 and D3 updates. Then hit print preview to see the change to the header...?

Eltigre
11-30-2006, 02:30 PM
I'm using Excel 2000. When I update the spreadsheet you sent with a new value, print preview still shows the old value of 444. The printed worksheet shows 444, too.

My calculation setting is Automatic. Cannot think of anything else that would be causing the problem.

lucas
11-30-2006, 02:41 PM
Maybe someone else could try the attachment in post 13 and give us some feedback...it could be a version 2000 issue too.

Eltigre
11-30-2006, 04:13 PM
I tried this on Excel 2003 and the results were the same. The only VBA option I tried that actually works is SheetSelectionChange, which updates the Header with the value in D3 each time the cursor moves to a different cell.

lucas
11-30-2006, 04:48 PM
If you put a new value in G3 and hit enter the value in D3 changes...
then printpreview and it still reads 444?

Eltigre
11-30-2006, 05:13 PM
Yes, Lucas. I open the file you attached (book1.xls). I selected File > Print Preview just to see what was there. 444 was in the header.

I then typed 123 in G3 and hit enter. The value in D3 changed to 123.
I selected File > Print Preview and the left header still read 444.

I tried using the print preview icon instead. Same result.
I tried actually printing the file. Same result. The header did not update.

The only thing that works for me, as I mentioned, is to use SheetSelectionChange instead of Workbook_BeforePrint

lucas
11-30-2006, 05:18 PM
Hmm....it works fine every time if you don't do that first print preview before you change anything...

Gert Jan
12-01-2006, 11:02 AM
Hi,
Tried it, did a printpreview first and it was 444, then changed the value in G3 and the header changed with it.
(i'm using 2003)

Gert Jan

lucas
12-01-2006, 11:43 AM
Thanks for the input GertJan....Not sure what's going on here yet..

Gert Jan
12-01-2006, 12:02 PM
Just a wild guess, Eltigre said he/she has no help installed and has no install disk.
Could this be of influence, or missing references? but, like said: wild guess.......

Gert Jan

malik641
12-17-2006, 10:31 PM
Hey Eltigre,

Could you place a breakpoint in the _BeforePrint code to see if the event even fires before you print preview? To set a breakpoint, go into the VBE (Alt + F11), then left-click adjacent to the code (in the gray area):
ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("D3").ValueIt will highlight the text in a burgundy color.

When you have the breakpoint placed please press PrintPreview and let us know if you are brought to the VBE where the above code will be highlighted in yellow.

Hope this helps.

BTW I tried Steve's (Lucas') code / workbook and it worked fine for me (XL 2003).

CodeMakr
12-18-2006, 11:21 AM
I'm using Excel 2000, and it works for me (whether number or text is in G3)??

jdubya
12-18-2006, 11:37 AM
Hi,
Tried it, did a printpreview first and it was 444, then changed the value in G3 and the header changed with it.
(i'm using 2003)

Gert Jan

I did the same as Gert Jan, and it worked for me. (2003 here also)

Zack Barresse
12-18-2006, 12:04 PM
Do NOT use ActiveSheet. Instead, specify the sheet...

[Code]Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("Sheet1").PageSetup.LeftHeader = Sheets("Sheet1").Range("D3").Value
End Sub[/vCode]

HTH

lucas
12-18-2006, 12:08 PM
What if you wanted it to work on every sheet in the workbook?

Zack Barresse
12-18-2006, 12:44 PM
There was no indication of the entire workbook being desired, it was implied it was only for the one sheet. Then maybe..


Private Sub Workbook_BeforePrint(Cancel As Boolean)
If TypeName(ActiveSheet) = "Worksheet" Then
ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("D3").Value
End If
End Sub