|
Do 3 Tasks on Opening File Q
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 |
Do 3 Tasks on Opening File Q
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.... "Sean" wrote in message ps.com... 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 |
Do 3 Tasks on Opening File Q
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" wrote: 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 |
Do 3 Tasks on Opening File Q
no idea Tushar, I just recevied some of the code (and it worked) then I
added to it and it didn't. Tushar Mehta wrote: 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 In article om, says... 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 |
Do 3 Tasks on Opening File Q
Thanks Corey but get a Goto Debug on
Application.GoTo Reference:=sh.Range("a1"), Scroll:=True Corey wrote: 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.... "Sean" wrote in message ps.com... 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 |
Do 3 Tasks on Opening File Q
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" wrote in message ps.com... 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 |
Do 3 Tasks on Opening File Q
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 wrote: 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" wrote in message ps.com... 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 |
Do 3 Tasks on Opening File Q
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" wrote in message ps.com... 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 wrote: 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" wrote in message ps.com... 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 |
Do 3 Tasks on Opening File Q
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 Bob Phillips wrote: 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" wrote in message ps.com... 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 wrote: 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" wrote in message ps.com... 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 |
| All times are GMT +1. The time now is 08:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com