Do 3 Tasks on Opening File Q

[Sean]

I am trying to do 3 things when I open up a file

1) Unprotect all worksheets in the file
2) Go to A1 in all sheets
3) Finally activate C6 in the sheet Current week

I had 2 & 3 above working and I tried adding 1 above, but am hitting
debug so I guess I have some syntax wrong




Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets

ws.unProtect Password:="1234"

Next Dim sh As Worksheet

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Current Week").Select
Application.ScreenUpdating = True
ActiveWindow.Zoom = 75

Range("C6").Select

End Sub

 

[Tushar Mehta]

You declare the variable sh three times!

Also, you declare the variable as sh but what do you use in the first loop?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Corey]

Try:


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
Unprotect Password:="1234"
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True
Next
ThisWorkbook.Sheets("Current Week").Select
Application.ScreenUpdating = True
ActiveWindow.Zoom = 75

Range("C6").Select

End Sub

The Sheets will be left Unprotected though....

Corey....

 

[Guest]

1. to unprotect all worksheets and select A1
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets

If wSheet.ProtectContents = True Then
wSheet.Unprotect
wSheet.Cells(1, 1).Select

End If
Next wSheet

End Sub

2. to select C6 in active sheet
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Cells(6, 3).Select
End Sub

End Sub

2. Where you are on other wotksheets is irrelevant.

 

[Sean]

no idea Tushar, I just recevied some of the code (and it worked) then I
added to it and it didn't.

 

[Sean]

Thanks Corey but get a Goto Debug on

Application.GoTo Reference:=sh.Range("a1"), Scroll:=True

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets
sh.Unprotect
sh.Activate
sh.Range("A1").Select
Next sh
With Activeworkbook.Worksheets("Current Week")
.Activate
.Range(C6").Select
End With


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sean]

I've hit a slight snag in that not all sheets have the same password
protection (hadn't noticed that). SO if say Sheet1, Sheet2, Sheet3
Password = xyz and Sheet4 and Sheet5 Password = 1234, how would I
incorporate this?


Private Sub Workbook_Open()
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password = "1234"
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Current Week")
.Activate
.Range("C6").Select
End With


End Sub

 

[Bob Phillips]

Private Sub Workbook_Open()
For Each sh In ActiveWorkbook.Worksheets
On Error Resum Next
sh.Unprotect Password = "1234"
sh.Unprotect Password = "xyz"
On Error Goto 0
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Current Week")
.Activate
.Range("C6").Select
End With


End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sean]

I assume your code Bob would check the two passwords. Problem now is
when I open I am prompted to enter the password (on the password
protected sheets)

My code slightly tweaked is (note some sheets are protected but not
with a password)

Private Sub Workbook_Open()
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Unprotect
sh.Unprotect Password = "1234"
On Error GoTo 0
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Current Week")
.Activate
.Range("C6").Select

End With

End Sub