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

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

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

Workbook_Open Problem ... Worksheets not loaded yet!?
 

Hello ... works great guy! Thanks!

Joe

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???

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???

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???

Workbook_Open Problem ... Worksheets not loaded yet!?
 

Wow ... you guys are amazing! Thanks so much!

Joe