ActiveSheet.Name not working

[ljwilson]

This is wierd. This was working until the workbook was modified. What I am doing is automatically populating the footer area with text and barcodes based on cell contents. Since both sheets have different print size percentages (and also different cells where the data for the barcode exists), I am using the sheetname to determine which cells to look at and what size font to use.

This used to work and not it is not. I have verified that the sheetnames have not changed. If I change the logical comparison from a "equals" to a "!equals" then I get a barcode for the account# and Unit # on 2nd page, but not on first. Change the logical comparison the other way and I get it on page 1, but not 2.

Here is the code:

' Created by:   LJ Wilson
' Purpose:      Print Barcodes in the Footer
' Date:         2009-04-21
' Notes:        The reason the font sizes are different between the two sheets is that
'               the pages are adjusted to different percentages to print on a single page.
'
   With ActiveSheet.PageSetup
        If ActiveSheet.Name <> "AnesRecord" Then
            .LeftFooter = _
            "&""Code128Wide,Regular""&62" & Azalea_Code_128_A("ANES.REC") & "&""Geneva,Regular""&24" & Chr(10) & "ANES.REC"
            .RightFooter = _
            "&""Code128Wide,Regular""&62" & Azalea_Code_128_A(Range("V3").Value & "--" & Range("V4").Value) & _
            "&""Geneva,Regular""&24" & Chr(10) & Range("V3").Value & "--" & Range("V4").Value
        Else
            .LeftFooter = _
            "&""Code128Wide,Regular""&36" & Azalea_Code_128_A("ANES.REC") & "&""Geneva,Regular""&14" & Chr(10) & "ANES.REC"
            .RightFooter = _
            "&""Code128Wide,Regular""&36" & Azalea_Code_128_A(Range("O5").Value & "--" & Range("O6").Value) & _
            "&""Geneva,Regular""&14" & Chr(10) & Range("O5").Value & "--" & Range("O6").Value
        End If
    End With

I am using this both on the OnSheetChange and OnBeforePrint events. I have found it doesn't work properly if not in both (this spreadsheet is auto-generated from a third party application).

So, I am fine with not being able to use the sheetname and thought about just checking for an empty cell in V3 and V4 to see what sheet I am on. How would I do this? Is there a way to trim the contents and then check the length?

Any other ideas?

Thanks,

LJ

 

[ljwilson]

No ideas? I have even tried using the codename property and it still doesn't behave properly.

 

[xlbo]

Not sure about the shete name....are there any chart sheets etc in the workbook?

In terms of checking a cell, you can use

IF Trim(Activesheet.Range("V3"))="" then
'cell empty
ELSE
'cell not empty
END IF

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question