Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Question,
We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Steven
Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
You could create a worksheet_open macro. I'm assuming that the "current
year" is the last worksheet in the list. If that's true, put this in it. If it;s not true, you'll need to do something else. Private Sub Workbook_Open() Dim aWB As Workbook Set aWB = ActiveWorkbook Worksheets(aWB.Worksheets.Count).Activate End Sub HTH, Barb Reinhardt "Steven Hilgendorf" wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Thank you Gord and Barb for the tips. Gord I am unable to paste the
script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Could be the VB Editor Windows are minimized somehow but you can get through
another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Hello Gord, thanks for the additional help. I'm still not able to paste
this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Someone locked the project for this workbook. You'll need to supply the
password to open the project. If you're not the developer, then you'll need to talk to that person to get the password. Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
As the information leaks out we get closer to a fix<g
See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Thanks Gord and Dave. Where do you enter the password? It never
prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
The only time I've seen the "project is unviewable" warning message is when I
was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Good point Dave.
I believe projects from 2003 get the same message when opening in earlier versions. Gord On Thu, 21 Jun 2007 05:49:54 -0500, Dave Peterson wrote: The only time I've seen the "project is unviewable" warning message is when I was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Hello Dave & Gord. We are using 2003 and I beleive they upgraded from
'97, where the original file would have been created. I tried the password program at the site you quoted/provided earlier, without success. I don't know what else to do to correct this. Any other suggestions, besides recreating the file in a clean workbook? Thanks again for your efforts, Steven Gord Dibben wrote: Good point Dave. I believe projects from 2003 get the same message when opening in earlier versions. Gord On Thu, 21 Jun 2007 05:49:54 -0500, Dave Peterson wrote: The only time I've seen the "project is unviewable" warning message is when I was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
The problem isn't opening the project (using xl2003) that was protected in xl97,
it's when you try to open the project that was protected in xl2003 (using xl97). Depending on how envolved the workbook is, you may be able to just select all the sheets and then Edit|move or copy sheets and copy them to a new workbook. Then save from there. If that doesn't work, and if it's a small file, I may have something to remove the password. Please zip it and send it to me (if you can). No promises, though. I've seen it work and I've seen it fail. Steven Hilgendorf wrote: Hello Dave & Gord. We are using 2003 and I beleive they upgraded from '97, where the original file would have been created. I tried the password program at the site you quoted/provided earlier, without success. I don't know what else to do to correct this. Any other suggestions, besides recreating the file in a clean workbook? Thanks again for your efforts, Steven Gord Dibben wrote: Good point Dave. I believe projects from 2003 get the same message when opening in earlier versions. Gord On Thu, 21 Jun 2007 05:49:54 -0500, Dave Peterson wrote: The only time I've seen the "project is unviewable" warning message is when I was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Hello Dave and Gord:
I wanted to follow up and let you know I rebuilt the password problem-ed spreadsheet successfully. It took some time, but I managed to turn the sluggish 5MB+ problem into a clean, 1.2MB, snappy file, that behaves when saved ans reopened. Thanks again for all your help and support. I really appreciate it, Steven Dave Peterson wrote: The problem isn't opening the project (using xl2003) that was protected in xl97, it's when you try to open the project that was protected in xl2003 (using xl97). Depending on how envolved the workbook is, you may be able to just select all the sheets and then Edit|move or copy sheets and copy them to a new workbook. Then save from there. If that doesn't work, and if it's a small file, I may have something to remove the password. Please zip it and send it to me (if you can). No promises, though. I've seen it work and I've seen it fail. Steven Hilgendorf wrote: Hello Dave & Gord. We are using 2003 and I beleive they upgraded from '97, where the original file would have been created. I tried the password program at the site you quoted/provided earlier, without success. I don't know what else to do to correct this. Any other suggestions, besides recreating the file in a clean workbook? Thanks again for your efforts, Steven Gord Dibben wrote: Good point Dave. I believe projects from 2003 get the same message when opening in earlier versions. Gord On Thu, 21 Jun 2007 05:49:54 -0500, Dave Peterson wrote: The only time I've seen the "project is unviewable" warning message is when I was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Open Workbook to Specific Worksheet?
Good to hear that you got it fixed. And rebuilding that workbook sounds like it
was the right thing to do. Steven Hilgendorf wrote: Hello Dave and Gord: I wanted to follow up and let you know I rebuilt the password problem-ed spreadsheet successfully. It took some time, but I managed to turn the sluggish 5MB+ problem into a clean, 1.2MB, snappy file, that behaves when saved ans reopened. Thanks again for all your help and support. I really appreciate it, Steven Dave Peterson wrote: The problem isn't opening the project (using xl2003) that was protected in xl97, it's when you try to open the project that was protected in xl2003 (using xl97). Depending on how envolved the workbook is, you may be able to just select all the sheets and then Edit|move or copy sheets and copy them to a new workbook. Then save from there. If that doesn't work, and if it's a small file, I may have something to remove the password. Please zip it and send it to me (if you can). No promises, though. I've seen it work and I've seen it fail. Steven Hilgendorf wrote: Hello Dave & Gord. We are using 2003 and I beleive they upgraded from '97, where the original file would have been created. I tried the password program at the site you quoted/provided earlier, without success. I don't know what else to do to correct this. Any other suggestions, besides recreating the file in a clean workbook? Thanks again for your efforts, Steven Gord Dibben wrote: Good point Dave. I believe projects from 2003 get the same message when opening in earlier versions. Gord On Thu, 21 Jun 2007 05:49:54 -0500, Dave Peterson wrote: The only time I've seen the "project is unviewable" warning message is when I was using xl2k at one location and xl97 at another. If I protected the project in xl2k (or higher) and tried to open it in xl97, then I would get that message. I had to go to the newer version, unlock the project, save the workbook with the project unprotected, then I could do my changes in xl97. My point is that xl97 can't open projects protected in later versions. Is that your situation? Actually, there is another time when I get that message. If the workbook is shared, I'll get it when I try to access the project. Is your workbook shared? If it is, then back to excel and unshare it (tools|Share workbook). ===== My guess is on the xl97 stuff since you did get that first message "project is locked". I don't get that with a shared workbook. Steven Hilgendorf wrote: Thanks Gord and Dave. Where do you enter the password? It never prompts for one? We have a short list of passwords that the former employee used, so I could try these. I will also check out the URL you provided. Thanks again, Steven Gord Dibben wrote: As the information leaks out we get closer to a fix<g See Dave P's response. You may be able to crack in using the info at John McGimpsey's site. http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord On Wed, 20 Jun 2007 18:25:28 -0500, Steven Hilgendorf wrote: Hello Gord, thanks for the additional help. I'm still not able to paste this script and I don't get Thisworkbook. If I choose properties of VBAProject(po.xls), I get "project locked" "project is unviewable"? What does this mean? Is there a way to unlock the project and make it viewable, so I can enter the script? Thanks again for your continued support, Steven Gord Dibben wrote: Could be the VB Editor Windows are minimized somehow but you can get through another way. With your workbook open hit Alt + F11 to open VBE CTRL + r to open project explorer. Select your workbook/project from the list and hit the + sign to expand. Hit the + sign for Microsoft Excel Objects to expand. Double-click on Thisworkbook to open. Paste the code into that module. Gord On Tue, 19 Jun 2007 17:42:09 -0500, Steven Hilgendorf wrote: Thank you Gord and Barb for the tips. Gord I am unable to paste the script into the VB screen. In fact 'view code' does not open with ThisWorbook (code) page? Could the file be protected somehow? If so, how can fix this. The person that created the spreadsheet no longer works for the company and I have taken over her responsibilities. Thanks again, Steven Gord Dibben wrote: Steven Private Sub Workbook_Open() Sheets("2007").Activate ActiveSheet.Range("C1024").Select End Sub With your workbook open, right-click on the Excel logo left of "File" if your window is maximized or on the title bar Excel logo if not maximized. Select "View Code" and Thisworkbook module will open. Paste the code into that module Close that window. Save your workbook and close it. Each time you open it, it will open to the worksheet you have named no matter where you were when you last saved/closed. Gord Dibben MS Excel MVP On Mon, 18 Jun 2007 18:22:47 -0500, Steven Hilgendorf wrote: Question, We have a shared workbook that we use to keep track of purchase order numbers. The workbook contains yearly worksheet tabs (i.e. 2007, 2006, 2005, etc.) The problem is that when some users open the workbook they have to navigate to the current worksheet (2007) at the end of the list to enter the next purchase order. It does not matter if the user saves the workbook in the current worksheet (2007) at the end of the list, say cell C1024 of the 2007 tab. The next time they open the workbook it defaults back to a different worksheet in a different location. Different users startup in different locations and worksheets. How can the users save the file so that when they open it they are in the current year worksheet at the end of the list? Thank you, Steven -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to open a workbook on a specific worksheet. | Excel Worksheet Functions | |||
at startup, i would like a specific workbook to open. | Setting up and Configuration of Excel | |||
How do I open a workbook in a specific sheet | Excel Discussion (Misc queries) | |||
Open workbook to specific worksheet | Excel Discussion (Misc queries) | |||
How to open a workbook with a specific worksheet displayed by defa | New Users to Excel |