Workbook_Open Problem ... Worksheets not loaded yet!?

[Joe HM]

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").ControlFormat.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

 

[Jan Karel Pieterse]

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

 

[Bob Phillips]

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").ControlFormat.Enabled =
False
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Joe HM]

Hello ... works great guy! Thanks!

Joe

 

[Joe HM]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Joe HM]

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

Joe