After a Sheet loop, all but last sheet is selected. Why?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using XL 2003 & 97

After running the following loop, all of the sheets are selected except the
last sheet which has Cell A1 selected.

I wish all of the sheets had cell A1 selected (as per the VBA code) -
therefore as a user selectes the other sheets only A1 is selected in contrast
to all of the cells being selected as is the case now.

What do I not realize about the following loop?

Sub Unhide_All() ' Unhides all Columns, Rows and WorkSheets
Dim wks As Worksheet
Dim myOrigSheetProtectStatus As Boolean
For Each wks In Worksheets
myOrigSheetProtectStatus = ActiveSheet.ProtectContents
If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=True
End If
Cells.Select
On Error Resume Next
Selection.EntireColumn.Hidden = False
On Error Resume Next
Selection.EntireRow.Hidden = False
wks.Visible = True
wks.Activate
ActiveWindow.FreezePanes = False
Cells.Range("A1").Select
If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=False
End If
Next
End Sub

TIA Dennis
 
I think you've got things in the wrong order:

Option Explicit
Sub Unhide_All() ' Unhides all Columns, Rows and WorkSheets
Dim wks As Worksheet
Dim myOrigSheetProtectStatus As Boolean
For Each wks In Worksheets

wks.Visible = True
wks.Activate

myOrigSheetProtectStatus = ActiveSheet.ProtectContents
If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=True
End If

Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False

ActiveWindow.FreezePanes = False
Cells.Range("A1").Select

If myOrigSheetProtectStatus = True Then
ActiveSheet.Protect UserInterfaceOnly:=False
End If
Next
End Sub

Your code has this line:
Cells.Select
which selects the cells on the activesheet. But you haven't activated the
"next" sheet yet.

I think I'd get rid of the references to Activesheet, too:

Option Explicit
Sub Unhide_All() ' Unhides all Columns, Rows and WorkSheets
Dim wks As Worksheet
Dim myOrigSheetProtectStatus As Boolean
For Each wks In Worksheets
With wks
.Visible = True
.Activate

myOrigSheetProtectStatus = .ProtectContents
If myOrigSheetProtectStatus = True Then
.Protect UserInterfaceOnly:=True
End If

.Cells.EntireColumn.Hidden = False
.Cells.EntireRow.Hidden = False

ActiveWindow.FreezePanes = False
.Range("A1").Select
If myOrigSheetProtectStatus = True Then
.Protect UserInterfaceOnly:=False
End If
End If
Next
End Sub
 
Close but no Cigar for me yet!

Thanks for your time and knowledge to help my coding get better.

Dennis

************************************************
 
There was a typo at the bottom of my last suggestion (the final "End If" should
have been "End With").

But it worked after that change for me. What didn't work for you?
 
****************************************

For those who may wish to use this routine:

Change the second line above End Sub to End With (Not End If)

****************************************
 
My comment was was about my inability to write perfect code yet. It had
nothing to do with your typo which I noticed before I ran it.

Works Great! Thanks Dave
 
Ahhhh. I see now. (I misread your comment as it didn't work for you.)

Glad you found my typo and fixed it.


My comment was was about my inability to write perfect code yet. It had
nothing to do with your typo which I noticed before I ran it.

Works Great! Thanks Dave
Click to expand...
 
Back
Top