ClearPrintTitlesAndAreas

[Francis Hookham]

This is fine when "Print_Titles" and "Print_Area" exist but hiccups if one
or both are not defined

Sub ClearPrintTitlesAndAreas()
ActiveWorkbook.Names("Print_Titles").Delete
ActiveWorkbook.Names("Print_Area").Delete
End Sub

How can I say

If ActiveWorkbook.Names("Print_Titles") = False then
ActiveWorkbook.Names("Print_Titles").Delete
End if

or something like that?

Thanks

Francis Hookham

PS - has no one any ideas about my query "An InputBox prob" dated 25/9?

 

[J.E. McGimpsey]

This is fine when "Print_Titles" and "Print_Area" exist but hiccups if one
or both are not defined

Sub ClearPrintTitlesAndAreas()
ActiveWorkbook.Names("Print_Titles").Delete
ActiveWorkbook.Names("Print_Area").Delete
End Sub

How can I say

If ActiveWorkbook.Names("Print_Titles") = False then
ActiveWorkbook.Names("Print_Titles").Delete
End if

or something like that?

First, a caveat - the Workbook names Print_Area and Print_Titles
refer to sheet-level names. The Workbook-level name only refers to
the *first* sheet that has one of these names defined, i.e. enter
this in the immediate window:

? ActiveWorkbook.Names("Print_Area").Name
Sheet1!Print_Area

So, if you define a print area on both sheet 1 and sheet2, then using

ActiveWorkbook.Names("Print_Area").Delete

will delete the Sheet1 name, but then the workbook level name will
refer to the Sheet2 name:

? ActiveWorkbook.Names("Print_Area").Name
Sheet2!Print_Area

One way to delete these names "legitimately" is to check all the
names in the workbook for "Print_Area", and delete those containing
it.

Dim nm As Name
For Each nm In ActiveWorkbook.Names
Debug.Print nm.Name
If InStr(nm.Name, "Print_Titles") Or _
InStr(nm.Name, "Print_Area") Then nm.Delete
Next nm

if you only want to remove the print area on one sheet, change
ActiveWorkbook to the sheet reference.

However, a "quick and dirty" method is to just ignore the error,
i.e.:

On Error Resume Next
ActiveWorkbook.Names("Print_Titles").Delete
ActiveWorkbook.Names("Print_Area").Delete
On Error GoTo 0

will delete the names (or, rather, the sheet names they refer to).

PS - has no one any ideas about my query "An InputBox prob" dated 25/9?

Don't recall seeing it, but then that was around the time the spam
picked up. I'll take a look...

 

[Francis Hookham]

Thanks J E McG for ClearPrintTitlesAndAreas - I'll look at it - I think the
"quick and dirty" way might do since I use it on a workbook of many sheets
and only want it to cancel those on the active sheet

I noted what you said about:

PS - has no one any ideas about my query "An InputBox prob" dated 25/9?

Don't recall seeing it, but then that was around the time the spam
picked up. I'll take a look...

What spam? - I have been away myself to 10 days and found and am still
getting a huge amount of spam:

"Undeliverable Message: User unknown" with message "Undeliverable message to
bdgugsndiy*puremail.net "

and many similar messages

There are also many, of which this typical:

From: MS Corporation Security Support <grcppig-ybtvxdz*bulletin.ms.net>
To: Commercial Partner <escr-qhbzxsbcfw*bulletin.ms.net>
with attachments: Enclosure (PictureViewer document), Enclosure
(PictureViewer document), qlbyy.exe

* I have replaced @ with an * for safety

Then there are several from "admin" to "" with subject "ATTENTION: Your
mailbox is over the high water mark, 75% based on amount stored!!" and text
"Your mailbox is over the high water mark.
Please delete some messages from your mailbox!"
I have no idea who from

The really irritating thing is that some are even coming into this new
eaddress set up 24 hours ago when I cancelled "fhmacpc"

Of course I have put all straight in the Deleted Items folder and will
delete them from there shortly but

None of my eaddressees are have complained

Sorry to go on so long but is there something I should know?

Francis Hookham

 

[J.E. McGimpsey]

What spam? - I have been away myself to 10 days and found and am still
getting a huge amount of spam:

The Swen ("news" backward) worm started hitting the networks on
Wednesdaythe 17th or Thursday the 18th. It's a particularly
insidious worm in that it not only (if you're a Windows user who's
unfortunate enough to open the .exe file) grabs all the addresses
from your address book and forwards itself to them, it also goes out
to the newsgroups, especially MS groups and slurps up all the
addresses it can find there. It also sent itself in multiple
versions to the groups.

In the last 10 days, I've gotten well into the mid-5 figures of the
worm's output. It has several forms. It replicates itself with a
message purportedly from MS with a 140K attachment to spread. It
also creates 3 or 4 variants on a message that is meant to look like
it was bounced from a domain, and indicating that something you sent
had a virus - presumably to panic the unwary into installing the
patch from MS.

Along about last Thursday morning, the attack escalated to the point
that the ms news server farm appeared to get swamped - whether you
saw a message depended on which server you happened to connect to as
updates from the controlling server were delayed. This made the
groups almost unusable for those connecting to them via NNTP (i.e.,
Entourage, OE, Thoth, News-Watcher). I think the Web interface fared
a bit better, but I never use it. I was only able to connect from my
ISP's news server.

Things seem to be a bit better now. MS is evidently trying to block
all the worm messages, though some variants continue to slip through
(I haven't seen any of the actual worm attachments on the groups
today).

 

[J.E. McGimpsey]

Thanks J E McG for ClearPrintTitlesAndAreas - I'll look at it - I think the
"quick and dirty" way might do since I use it on a workbook of many sheets
and only want it to cancel those on the active sheet

Then make sure you use

On Error Resume Next
ActiveSheet.Names("Print_Area").Delete
On Error GoTo 0

since the Workbook-level "Print_Area") refers to the *first* sheet
that has a Print Area set up, not the active sheet.

 

[Francis Hookham]

Then make sure you use

On Error Resume Next
ActiveSheet.Names("Print_Area").Delete
On Error GoTo 0

since the Workbook-level "Print_Area") refers to the *first* sheet
that has a Print Area set up, not the active sheet.

Great - thanks

Francis Hookham

 

[Francis Hookham]

What spam? - I have been away myself to 10 days and found and am still
getting a huge amount of spam:

The Swen ("news" backward) worm started hitting the networks on
Wednesdaythe 17th or Thursday the 18th. It's a particularly insidious worm
in that it not only (if you're a Windows user who's unfortunate enough to
open the .exe file)

I'm principally a Mac user (since about the first 128k in Cambridge UK) and
have not opened any attachments other than those checked with the sender
first - I do not use the PC for email

Presumably I am just suffering from what others are sending me - I note that
you say they are slowing down so I will just sit tight - what a pity this
marvellous internet is messed up in this way

Thanks for all your help - it is appreciated very much

Francis Hookham