MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Selection Method of Worksheet class failed

  Asked By: Abbie    Date: Jan 14    Category: MS Office    Views: 4164

We have many workbooks which are being produced in a trial. As part of the
process of producing them, we add a Confidential Block to all sheets in the
book. Many workbooks have upwards of 100 sheets.

I wrote the following code to do that – probably could be done in a much
more efficient method – which I am open to using. Excel VBA is not my strong
suit. <smile>

Anyway – the code cooks along nicely until towards the end of the workbook
and then we get the Error 1004: Selection method of worksheet class failed.
For instance, it will occur on sheet 72 – when there are still 25 more
sheets to go. I can restart the code, and it will continue on.

Do I need a DoEvents in the middle of the code? It runs slow enough as it
is, I hate to slow it down any further, but I will….

Sub ConfidentialSheets()
Dim i As Integer
Dim Counter As Integer

On Error GoTo EH
i = Application.ActiveWorkbook.Worksheets.count
For Counter = 1 To i
With ActiveSheet.PageSetup
.CenterFooter = "CONFIDENTIAL" & Chr(10) & "Subject to Protective
Order" & Chr(10) & "USDC-KS 05-1368"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintQuality = 600
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.Zoom = 100
End With
Debug.Print Counter & ":" & ActiveSheet.Name
Exit Sub

Select Case Err.Number
Case 91
MsgBox "Footers have been applied to " & Counter & " sheets."
Exit Sub
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_EH
End Select

End Sub



10 Answers Found

Answer #1    Answered By: Trae Thompson     Answered On: Jan 14

So refreshing to have you ask a question for a change rather than answering
them! <big g> An opportunity to give back...

I don't know why you're getting that particular error - I would expect an
error 91 - Object variable or With block  variable not set - but I notice
you're not selecting the first worksheet  initially, so I would think it's
very possible it's trying to select PAST the last worksheet.

So, does putting:


just before the For/Next loop help?

Answer #2    Answered By: Rochelle Elliott     Answered On: Jan 14

I had an "Sheets(1).Activate" right before the loop started, but when the
code started failing in the middle of the workbook, we commented that out,
so that we could start at the currently selected sheet.

Yes, an Error 91 does pop-out when they get to the end of the workbook,
which is why I handled it in the EH. The thing is, that this particular
error occurs in the middle of the workbook. <shrug> I first thought it was
something wrong with the particular sheet, however, you can restart the
macro on the sheet previous, and the code will run as expected until it gets
to the end of the workbook. <sigh> Nothing for me to be able to say "Ah HA"

Answer #3    Answered By: Silvia Chapman     Answered On: Jan 14

Just make a subtle change in your code.

Remove " ActiveSheet.Next.Select" from your program. At the beginning of
your For Loop add  this: " Sheets(counter).Select"

This will start at index 1 (each sheet is indexed starting at 1) and
work its way to the maximum amount of sheets  within the workbook.

Answer #4    Answered By: Ty Thompson     Answered On: Jan 14

but that change didn't fix the error. <scratching head>

Answer #5    Answered By: Grady Stewart     Answered On: Jan 14

Not that it would help solve the problem but... Just a thought... Have you
tried reducing the number of worksheets.. Binary chop say.. To see if
there's a number at which everything works ok?

Answer #6    Answered By: Brendan Smith     Answered On: Jan 14

as far as I can tell, the code works in

Answer #7    Answered By: Faiza Mian     Answered On: Jan 14

Upload the file and let him have a look at it.
Sounds like something funky going on with that one sheet.

Answer #8    Answered By: Felix Gray     Answered On: Jan 14

I think I may have figured it out, it seems as though the sheets  which throw
up the error have a space in the footer. The only clue I had was that (none)
was not displayed in the custom footer when looking at the sheet.

So, how to code around that? If there is a space or multiple spaces, I want
t replace the text with my text. However, if there is actually text in the
footer, then I want to append my footer to the existing footer.

Answer #9    Answered By: Sultana Tabassum     Answered On: Jan 14

I'm not sure why spaces would be causing the problem, but if you think
it is then you might want to try trimming the text. Try something like

Dim str as String

With ActiveSheet.PageSetup
If Len(.CenterFooter) > 0 Then
str = Trim(.CenterFooter) & " Your Text Here"
.CenterFooter = str
End if
End With

When I ran a test with spaces, both with and without surrounding text
the above worked just fine by replacing the spaces with the text, or
simply removing them from the surrounding text.

Answer #10    Answered By: Hollie Hughes     Answered On: Jan 14

Since it takes a long time, Autosave wouldn't be trying to come in as
the macro's running would it?

How about avoiding the sheet activations?:

On Error GoTo EH
Counter = 0
For Each sht In Application.ActiveWorkbook.Worksheets
Counter = Counter + 1
With sht.PageSetup

...page setup stuff here

End With
'ActiveSheet.Next.Select (commented out)
Next sht

worked OK on 192 empty sheets  here (XL2003)

ps. I realise that your macro works from the currently active sheet to
the last sheet, whereas mine does all the sheets regardless, but it
could help in diagnosis of the fault. If there ends up being no fault,
then you could perhaps pop in an IF statement of the ilk:

With sht.PageSetup
If .CenterFooter <> "CONFIDENTIAL" & Chr(10) & "Subject to Protective
Order" & Chr(10) & "USDC-KS 05-1368" Then
...page setup stuff here
End If
End With

Or if you want to only affect sheets from the active sheet add  an IF
which sets a flag when the active sheet is reached:

ActiveSheetReached = False
For Each sht In Application.ActiveWorkbook.Worksheets
If sht = ActiveSheet Then activeSheetReached = True
If activeSheetReached Then
Counter = Counter + 1
With sht.PageSetup
...page setup stuff herre
End With
End If
(you might want to put the Counter increment line elsewhere)

If you want the sheet with the error on it to become the active sheet
would a:
in the error handler do it?

Didn't find what you were looking for? Find more on Selection Method of Worksheet class failed Or get search suggestion and latest updates.