Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Hello -
I want to make sure that some buttons on one of the sheets of a workbook are disabled if the file was opened in read-only. I put the following in the Activate() handler of that workbook: Private Sub Worksheet_Activate() If ThisWorkbook.ReadOnly Then ThisWorkbook.Sheets("Sheet2").Shapes("xButton").Co ntrolFormat.Enabled = False .... The only problem with that is that if the workbook is opened with that sheet being visible (active) already, it won't work until I switched sheets and come back to this one. I tried to put something similiar into the Workbook_Open() handler but there seems to be a problem since that is called without all the sheets being loaded. Is there a way I can wait until all the sheets are loaded in there and then disable my buttons? Thanks so much! Joe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Joe,
I haven't tried this myself, but you could use OnTime in the Workbook_Open. Something like Private Sub Workbook_Open() If ThisWorkbook.ReadOnly Then Application.Ontime Now + timeserial(0,0,1), "DisableButton" End If and then in a general module Sub DisableButton() ThisWorkbook.Sheets("Sheet2").Shapes("xButton").Co ntrolFormat.Enabled = False End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Joe HM" wrote in message oups.com... Hello - I want to make sure that some buttons on one of the sheets of a workbook are disabled if the file was opened in read-only. I put the following in the Activate() handler of that workbook: Private Sub Worksheet_Activate() If ThisWorkbook.ReadOnly Then ThisWorkbook.Sheets("Sheet2").Shapes("xButton").Co ntrolFormat.Enabled = False ... The only problem with that is that if the workbook is opened with that sheet being visible (active) already, it won't work until I switched sheets and come back to this one. I tried to put something similiar into the Workbook_Open() handler but there seems to be a problem since that is called without all the sheets being loaded. Is there a way I can wait until all the sheets are loaded in there and then disable my buttons? Thanks so much! Joe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Helllo ... I actually jumped the gun a little early. It works great
most of the time but there is a problem when I do the following: I have another sheet with a macro that opens the one with the _Open(). It inserts some cells and does stuff and then it does a .SaveAs and ..Close. On the file. For some reason, the file I just saved as opens automatically if I keep the OnTime in the _Open(). Any idea what that could be??? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
How much time delay do you have on the OnTime.
It might be an idea to do the Close in an OnTime procedure. -- HTH RP (remove nothere from the email address if mailing direct) "Joe HM" wrote in message oups.com... Helllo ... I actually jumped the gun a little early. It works great most of the time but there is a problem when I do the following: I have another sheet with a macro that opens the one with the _Open(). It inserts some cells and does stuff and then it does a .SaveAs and .Close. On the file. For some reason, the file I just saved as opens automatically if I keep the OnTime in the _Open(). Any idea what that could be??? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
The ontime schedule macro can't execute until the workbook is closed and the
calling code stops. then it fires - reopening the workbook so it can run. In the code that peforms the functions you describe, use application.EnableEvents = False ' open, process, close the workbook Application.EnableEvents = True this will keep the workbook_open event from firing. -- Regards, Tom Ogilvy "Joe HM" wrote in message oups.com... Helllo ... I actually jumped the gun a little early. It works great most of the time but there is a problem when I do the following: I have another sheet with a macro that opens the one with the _Open(). It inserts some cells and does stuff and then it does a .SaveAs and .Close. On the file. For some reason, the file I just saved as opens automatically if I keep the OnTime in the _Open(). Any idea what that could be??? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Wow ... you guys are amazing! Thanks so much!
Joe |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Hi Joe,
I tried to put something similiar into the Workbook_Open() handler but there seems to be a problem since that is called without all the sheets being loaded. Is there a way I can wait until all the sheets are loaded in there and then disable my buttons? Put your code in a normal module (insert, module) and use ontime to invoke the macro from Workbook_Open: Private Sub Workbook_Open() Application.Ontime Now, "YourButtonHidingSub" End Sub Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open Problem ... Worksheets not loaded yet!?
Hello ... works great guy! Thanks!
Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Workbook_open | Excel Discussion (Misc queries) | |||
Excel 97 Workbook_Open problem | Excel Programming | |||
Workbook_Open problem | Excel Programming | |||
problem with code in workbook_open event | Excel Programming | |||
how to know if a xla is 'loaded' | Excel Programming |