VBA code For Printing

Microsoft Office excel 2007 home & stude...
July 2, 2010 at 18:04:21
Specs: Windows 7
I used http://www.computing.net/answers/of... to edit my Excel VBA code to put a cell reference into my custom header and it worked fine. I had to edit a couple of bits (placement, worksheet name, cell reference) and it was all great.

But I want it, instead of displaying just the cell contents, to display some standard text and the cell content. For example, at the moment cell B15 contains "John", so the header looks like this:

John

I want it instead to say

Name: John

Here is my code:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As Range
Set rFormulaCell = Sheets("OSR").[B15]
ActiveSheet.PageSetup.LeftHeader = rFormulaCell
End Sub

Thanks for all help, and thanks to DerbyDad03 for his detailed and very helpful instructions for the first question!


See More: VBA code For Printing

Report •

#1
July 2, 2010 at 18:09:39
Set rFormulaCell = "Name: " & Sheets("OSR").[B15]

Report •

#2
July 2, 2010 at 20:33:15
Thanks heaps but now I'm getting:

Run-time error '424':

Object required.

When I click "debug" it goes to the edited line. Code now reads:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As Range
Set rFormulaCell = "Name: " & Sheets("OSR").[B15]
ActiveSheet.PageSetup.LeftHeader = rFormulaCell
End Sub

Really appreciate your help on this!


Report •

#3
July 2, 2010 at 20:44:50
Sorry, I didn't test my suggestion.

Try replacing the code with this:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As String
 rFormulaCell = "Name: " & Sheets("OSR").[B15]
 ActiveSheet.PageSetup.LeftHeader = rFormulaCell
End Sub


Report •

Related Solutions

#4
July 2, 2010 at 20:50:05
Ah, because it's concatenated text then it's a string! I see. Thanks so much, that works perfectly. Have a good weekend!

Report •

#5
July 3, 2010 at 04:34:31
Hi,

Shouldn't it be:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rFormulaCell As Range
Set rFormulaCell = Sheets("OSR").[B15]
ActiveSheet.PageSetup.LeftHeader = "Name: " & rFormulaCell.Text
End Sub

because you have to 'set' the range object rFormulaCell to a Cell - in this case Sheets("OSR").[B15] - before you copy it to the header.
The original post was correct in using Set

Regards


Report •

#6
July 3, 2010 at 05:59:44
...
or more simply:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftHeader = "Name: " & Sheets("OSR").[B15].Text
End Sub


Report •

#7
July 3, 2010 at 06:19:22
Actually all three work seem to work.

Yes, you need Set if the variable is declared as a Range, but not if it's declared as a String or - more simply - not used at all.

Obviously your final suggestion is the most efficient.


Report •

Ask Question