Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
I recently installed Microsoft Office 2003 and am experiencing some
odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
Try lowering security in Excel, via Tools, Macro, Security.
RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Sun, 3 Sep 2006, "RB Smissaert" wrote:
Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
Is the workbook an .xla file?
Workbook_Open may not work with that. RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message . .. I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Sun, 3 Sep 2006, "RB Smissaert" wrote:
Is the workbook an .xla file? Workbook_Open may not work with that. No, just a standard workbook (TimeTracking.xls) and as mentioned in my original post, it worked just fine prior to my reinstallation of Windows and Office. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Sun, 3 Sep 2006, "RB Smissaert" wrote:
Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
Try running the VBA CodeCleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message . .. On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message m... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Mon, 4 Sep 2006, "RB Smissaert" wrote:
Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message om... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
Maybe you should post all the code involved.
RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Mon, 4 Sep 2006, "RB Smissaert" wrote: Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message . .. On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message m... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:1tmkf2t07ngsdo09c667nvqtrdn99nk5cg@4ax. com... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Mon, 4 Sep 2006, "RB Smissaert" wrote:
Maybe you should post all the code involved. I did so in my original post. I changed the code (for simplicity) to: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub Even that didn't run at startup. There's no point in posting the entire code that I normally run, because it's long and the problem is not the code. A simple MsgBox won't even run when the spreadsheet is opened. This is not a problem with the code, but with this particular spreadsheet firing off this particular event on this particular machine. If you understand this (and realize that I'm not being difficult), you'll see why it is so frustrating for me. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . On Mon, 4 Sep 2006, "RB Smissaert" wrote: Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message om... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:1tmkf2t07ngsdo09c667nvqtrdn99nk5cg@4ax .com... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
I still think there is some code somewhere in your workbook that is causing
this trouble. Many times I have thought: this is just baffling and I never will find the cause for this problem, but in the end I always do. What happens when you remove all the code of the WB, except: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub Does it compile in the original (trouble-some) state? Could there be a problem with the References? Did you actually run the CodeCleaner? I would be interested in the opinion of one of the real Excel experts. RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Mon, 4 Sep 2006, "RB Smissaert" wrote: Maybe you should post all the code involved. I did so in my original post. I changed the code (for simplicity) to: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub Even that didn't run at startup. There's no point in posting the entire code that I normally run, because it's long and the problem is not the code. A simple MsgBox won't even run when the spreadsheet is opened. This is not a problem with the code, but with this particular spreadsheet firing off this particular event on this particular machine. If you understand this (and realize that I'm not being difficult), you'll see why it is so frustrating for me. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message . .. On Mon, 4 Sep 2006, "RB Smissaert" wrote: Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message m... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:do7mf2t3srbh3eqacg93dim7qf80t0lbmd@4ax. com... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:1tmkf2t07ngsdo09c667nvqtrdn99nk5cg@4a x.com... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
On Mon, 4 Sep 2006, "RB Smissaert" wrote:
I still think there is some code somewhere in your workbook that is causing this trouble. Many times I have thought: this is just baffling and I never will find the cause for this problem, but in the end I always do. What happens when you remove all the code of the WB, except: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub The only code that remained in ThisWorkbook was the Workbook_Open event above. The remaining code was moved to a module for holding. The Workbook_Event still doesn't fire as it should. Does it compile in the original (trouble-some) state? Yes, it compiles fine, and runs as expected when I run it from the VBE. Similarly, if I open the spreadsheet on one of the other machines on my network here at the house, the Workbook_Open event performs perfectly upon opening the document. This problem is limited to my machine, and only to this particular spreadsheet. Could there be a problem with the References? No, because dropping the code to a MsgBox and running it doesn't work either, yet creating a new spreadsheet (on same machine) and writing an Workbook_Open event runs just fine. Did you actually run the CodeCleaner? No, because dropping the code to a MsgBox and running it doesn't work either, yet creating a new spreadsheet (on same machine) and writing an Workbook_Open event runs just fine. I would be interested in the opinion of one of the real Excel experts. Me too. This seems to be one of those weird quirky things that I don't know we'll ever truly figure out. My request was not so much for knowledge (i.e. how to fix Excel when it does this) as it was for experience (i.e. it did that to me once and here's how I fixed it). I don't think it's a problem with Excel, my code, this file, or my machine. The only thing I can think of is that it's a bug that depends on something in the file (specifically with the Workbook_Open event) and my machine in order to appear. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message .. . On Mon, 4 Sep 2006, "RB Smissaert" wrote: Maybe you should post all the code involved. I did so in my original post. I changed the code (for simplicity) to: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub Even that didn't run at startup. There's no point in posting the entire code that I normally run, because it's long and the problem is not the code. A simple MsgBox won't even run when the spreadsheet is opened. This is not a problem with the code, but with this particular spreadsheet firing off this particular event on this particular machine. If you understand this (and realize that I'm not being difficult), you'll see why it is so frustrating for me. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Mon, 4 Sep 2006, "RB Smissaert" wrote: Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message om... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:do7mf2t3srbh3eqacg93dim7qf80t0lbmd@4ax .com... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:1tmkf2t07ngsdo09c667nvqtrdn99nk5cg@4 ax.com... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
How about taking items out of the WB, step by step and re-save and see when
the problem disappears? So, start with taking out normal modules. Take out any class modules. Then delete sheets one by one. etc. Still think it is worth a try to run the CodeCleaner. RBS "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message ... On Mon, 4 Sep 2006, "RB Smissaert" wrote: I still think there is some code somewhere in your workbook that is causing this trouble. Many times I have thought: this is just baffling and I never will find the cause for this problem, but in the end I always do. What happens when you remove all the code of the WB, except: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub The only code that remained in ThisWorkbook was the Workbook_Open event above. The remaining code was moved to a module for holding. The Workbook_Event still doesn't fire as it should. Does it compile in the original (trouble-some) state? Yes, it compiles fine, and runs as expected when I run it from the VBE. Similarly, if I open the spreadsheet on one of the other machines on my network here at the house, the Workbook_Open event performs perfectly upon opening the document. This problem is limited to my machine, and only to this particular spreadsheet. Could there be a problem with the References? No, because dropping the code to a MsgBox and running it doesn't work either, yet creating a new spreadsheet (on same machine) and writing an Workbook_Open event runs just fine. Did you actually run the CodeCleaner? No, because dropping the code to a MsgBox and running it doesn't work either, yet creating a new spreadsheet (on same machine) and writing an Workbook_Open event runs just fine. I would be interested in the opinion of one of the real Excel experts. Me too. This seems to be one of those weird quirky things that I don't know we'll ever truly figure out. My request was not so much for knowledge (i.e. how to fix Excel when it does this) as it was for experience (i.e. it did that to me once and here's how I fixed it). I don't think it's a problem with Excel, my code, this file, or my machine. The only thing I can think of is that it's a bug that depends on something in the file (specifically with the Workbook_Open event) and my machine in order to appear. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message . .. On Mon, 4 Sep 2006, "RB Smissaert" wrote: Maybe you should post all the code involved. I did so in my original post. I changed the code (for simplicity) to: Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub Even that didn't run at startup. There's no point in posting the entire code that I normally run, because it's long and the problem is not the code. A simple MsgBox won't even run when the spreadsheet is opened. This is not a problem with the code, but with this particular spreadsheet firing off this particular event on this particular machine. If you understand this (and realize that I'm not being difficult), you'll see why it is so frustrating for me. MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message m... On Mon, 4 Sep 2006, "RB Smissaert" wrote: Try running the VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm It's not a code problem -- it's something with this particular spreadsheet and only on my machine. The procedure runs fine on other machines, and the event runs fine on my machine in other spreadsheets. Thanks for the suggestion, though. I don't think I'll ever figure out what exactly is causing this. In the interim, I've changed the event to TempWorkbook_Open and called another procedure that holds my original code in it. That seems to be working, and while it doesn't fix the problem, it's a formiddable workaround. Thanks again, MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:pe0nf293eohlq4ng3tks4b2u4fh6ejs2bv@4ax. com... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Is the workbook an .xla file? Workbook_Open may not work with that. RB, what's really weird, is that I created a new workbook and added a MsgBox to the Workbook_Open event and it worked fine. So apparently it's only my OLD spreadsheet that is no longer working as expected. Any suggestions at all? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:do7mf2t3srbh3eqacg93dim7qf80t0lbmd@4a x.com... On Sun, 3 Sep 2006, "RB Smissaert" wrote: Try lowering security in Excel, via Tools, Macro, Security. Hi, and thanks for responding.. My macro security setting is already on Medium (since that change was necessary to load macros) and setting it to Low does not rememdy the problem. My previous installation was set to Medium as well. Any other thoughts? MP- "Mangus Pyke" <manguspyke[at]comcast[dot]net wrote in message news:1tmkf2t07ngsdo09c667nvqtrdn99nk5cg@ 4ax.com... I recently installed Microsoft Office 2003 and am experiencing some odd behavior with the way in which the workbook.open event is handled. I keep a timesheet for which I record my hours on various products at work, which allows me to see a breakdown of where my time is focused, as well as some convenient statistics (average hours/day, etc). I wrote a short procedure a while back that lives in ThisWorkbook and locks all of the worksheets except for the current month's whenever I open the document, with the intent that if I inadvertently leave one unlocked, the document will automatically remedy this for me next time I load it. When I reloaded Office 2003 a couple of weeks ago, it suddenly stopped working. My procedure is not running when the document is opened, and I've even shortened it to just a MsgBox (see below) to test it, but to no avail. Any suggestions would be very welcomed. Private Sub Workbook_Open() MsgBox "Test.", vbOKOnly, "Macro" End Sub MP- |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open
I have been trying to figure out the same issue with my spreadsheet. File works fine on the computer I wrote it on but trying to use it on my laptop and the Workbook_Open won't fire. If you find the cause please post it and I will do the same. Thanks. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_Open | Excel Programming | |||
How to use workbook_open()? | Excel Programming | |||
Workbook_Open() will not run | Excel Programming | |||
Workbook_Open | Excel Programming | |||
workbook_open | Excel Programming |