Cell Reference in Footer not working

[ljwilson]

I am trying to set the font and the cell reference for the footer in VBA. I have it working, except for the cell reference. I have a value in Z1 (it is actually "*"&W5&"--"&W6&"*"), but it will not display the value, it just prints "Z1" in the footer.

Any advice on what I am doing wrong?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' Created by LJ Wilson
' 2009-04-21
'
    With ActiveSheet.PageSetup
        .LeftFooter = _
        "&""Free 3 of 9 Extended,Regular""&54*ANES.REC*&""Geneva,Regular""&18" & Chr(10) & "ANES.REC"
        .RightFooter = _
        "&""Free 3 of 9 Extended,Regular""&54" & Range("Z1").Value & Chr(10) & "&""Arial,Regular""&18" & Range("Z1").Value
    End With
End Sub

 

[SkipVought]

Hi,

works for me.

Is your Z1 formula...
[tt]
="*"&W5&"--"&W6&"*"
[/tt]
???

I see two barcodes at the bottom of the sheet and the values I have in W5 & W6 on the right.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ljwilson]

Skip,

I had it in the wrong module apparently. It is working now except for two things:

1. On one sheet the size of the print is smaller (I think the sheet is programmatically designed to fit to page). On this sheet the barcodes and text are smaller visually than on the other sheet (even though they are both using the same VBA code above).

2. How would I center the text and the barcode on each side (left and right)?

Thanks for the quick reply.

LJ

 

[SkipVought]

I think the sheet is programmatically designed to fit to page

This is a page setup property.

How would I center the text and the barcode on each side

Turn on your macro recorder and do it in page setup. Observe your recorded results.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ljwilson]

I fixed the issue of the page zoom property by using an If ActiveSheet.Name method to change the font size depending on the page. That seems to work fine.

However, there is not a button to center the text in the footer. Any text that is typed is centered automatically and the VBA that is generated doesn't show how it is done.

Any advice to use VBA to specify centered, left or right justification?

 

[PHV]

Have you tried to use the &C code format ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ljwilson]

I did, but it broke it. I don't know where to put that formatting code. Here is what I am using:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' Created by:   LJ Wilson
' Purpose:      Print Barcodes in the Footer
' Date:         2009-04-21
'
    With ActiveSheet.PageSetup
        If ActiveSheet.Name = "FluidsRTData" Then
            .LeftFooter = _
            "&""Free 3 of 9 Extended,Regular""&68*ANES.REC*&""Geneva,Regular""&22" & Chr(10) & "ANES.REC"
            .RightFooter = _
            "&""Free 3 of 9 Extended,Regular""&68" & Range("Z1").Value & "&""Geneva,Regular""&22" & Chr(10) & Range("Z2").Value
        Else
            .LeftFooter = _
            "&""Free 3 of 9 Extended,Regular""&36*ANES.REC*&""Geneva,Regular""&14" & Chr(10) & "ANES.REC"
            .RightFooter = _
            "&""Free 3 of 9 Extended,Regular""&36" & Range("Z1").Value & "&""Geneva,Regular""&14" & Chr(10) & Range("Z2").Value
        End If
    End With
End Sub

This works, but does not center the footer data. If you can show me where to put the formatting code, it would be greatly appreciated. I have tried several locations, but haven't been successful.

Thannks,

LJ

 

[SkipVought]

AFAIK,

text in the Left header/footer left justifies that text

text in the Center header/footer centers that text

text in the Right header/footer right justifies that text

How would you define the Left or Right BOUNDS for justification? Center on WHAT?

If you want stuff to be what appears to be centered around some immaginary point left or right of center, then YOU will need to decide exactly what that imaginary center point is AND mathematically define that in the Center header/footer, it seems to me IMHO.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Lets see if I can clarify my point.


Suppose your enter the following
[tt]
Left: What's on left
Center: Stuff in the center
Right: This is in rit
[/tt]
Assign Arial 24 to each of the 3 text segments

Observe the results.

Left & Right BOTH have the same number of characters!

Because you are using a proportional font, the Left text takes up more space than the Right text.

That's ONE of the problems you'll need to address, even if you try to center the whole thing like this
[tt]
Left:
Center: What's on left Stuff in the center This is in rit
Right:
[/tt]
But then what happens if your have TWO lines.

It becomes a mess!

You may gain some success, but I doubt it will be a happy task.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ljwilson]

Gotcha, I understand now.

One other quick question:

If I try to reference a cell that is being populated by the following IF statement in Cell 05:

=IF(ISREF('I:\picis\Reports\Temp\H_DATA.XLS'!PatientId1),IF(ISBLANK('I:\picis\Reports\Temp\H_DATA.XLS'!PatientId1),"",'I:\picis\Reports\Temp\H_DATA.XLS'!PatientId1),"")

and is not printing the data when the BeforePrint event fires off my VBA code for the custom footer, what do you think could be the problem?

In Cell Z1, I am using the ="*"&O5&"--"&O6&"*" formula to reference the dynamic values in O5 & O6 (O6 works very similar to O5 above).

Any ideas as to why they would show up on Print Preview, but not when a third party app prints the sheet?

 

[SkipVought]

You might need to force the calculation to occur, as the user might have calculation set to MANUAL.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}