Application.OnTime at Workbook_Open

[LordDragon]

Greetings,


I was having problems with my workbook opening, so I moved most of the code that was trying to load during the Workbook_Open procesure to another sub and then tried to call it using Application.OnTime.






Private Sub Workbook_Open()


'Sets the password for each Worksheet, but still allows the code to work.
Dim wkSheet As Worksheet


For Each wkSheet In Worksheets
wkSheet.Protect "Password", UserInterfaceOnly:=True
Next wkSheet


'Forces the Workbook to open on the Instructions page.
Application.Goto Sheets("Instructions").Range("A1"), True




myTime = Now() + TimeValue("00:00:05")


Application.OnTime Earliesttime:=myTime, Procedure:="SetSystemSelectionBoxes", Schedule:=True





The Application.OnTime thing seems to be working fine, but instead of running the called sub, it gives me a notice that the sub either doesn't exist, or that macros are disabled.

I know macros are not disabled because I'm not getting the "Enable Content" warning, and it's running this one.

I have never used the Application.OnTime thing before, so I found some examples and modified them to what I needed. I'm not positive I did that correctly though.

Here is the code I'm attempting to call.





Private Sub SetSystemSelectionBoxes()

'Sets the options for the drop boxes on the System Selection sheet.
With Worksheets("System Selection").VoltageBox
.AddItem "110V"
.AddItem "220V"
End With

With Worksheets("System Selection").UnitsBox
.AddItem "Imperial"
.AddItem "Metric"
.AddItem "Mixed"
End With

With Worksheets("System Selection").TorqueBox
.AddItem "Electric"
.AddItem "Hydraulic"
End With

With Worksheets("System Selection").AutoDrillerBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").CasingBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").ChokeBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").EDRBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").ePVTBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").ESRBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").FlowBox
.AddItem "Yes"
.AddItem "No"
End With


With Worksheets("System Selection").GABox
.AddItem "No"
.AddItem "Yes"
End With


With Worksheets("System Selection").HGasBox
.AddItem "No"
.AddItem "Yes"
End With


With Worksheets("System Selection").PRDBox
.AddItem "No"
.AddItem "Yes"
End With


With Worksheets("System Selection").PVTBox
.AddItem "No"
.AddItem "Yes"
End With

With Worksheets("System Selection").ProbeBox
.AddItem "Radar"
.AddItem "Mud Probe"
.AddItem "Both"
End With


With Worksheets("System Selection").SideKickBox
.AddItem "Yes"
.AddItem "No"
End With

With Worksheets("System Selection").UJBBox
.AddItem "Yes"
.AddItem "No"
End With


With Worksheets("System Selection").WorkstationsBox
.AddItem "No"
.AddItem "Yes"
End With


End Sub


Any help would be appreciated.

[Jan Karel Pieterse]

If there is another workbook open that may contain the same sub, OnTime max cause Excel to try to call the routine in the wrong workbook. To make sure the right sub is called, modify your ontime code to:


Application.OnTime Earliesttime:=myTime, Procedure:="'" & ThisWorkbook.Name & "'!SetSystemSelectionBoxes", Schedule:=True

[Aflatoon]

Your code is marked Private. It should be public and in a normal module (which should not have the same name as the routine).

[snb]

In this case there's no reason to use application.ontime at all.

[Jan Karel Pieterse]

It regularly happens that code called directly from an Open even fails to run with unexpected errors, but runs without any problem when called using Application.Ontime. My theory is that this happens because sometimes this code gets called before has actually Excel finished doing its startup chores. Using OnTime ensures Excel is ready.

[LordDragon]

Jan & Aflatoon,

Thank you for the suggestions. I changed the code as suggested by Jan. Even though I didn't have any other Excel books open when I was getting the error, I know others might. Best to plan ahead.

I also moved the called code to a different module as Aflatoon suggested.

[p45cal]

keeping it shorter and easier to tweak:
Public Sub SetSystemSelectionBoxes()
'Sets the options for the drop boxes on the System Selection sheet.
With Worksheets("System Selection")
With .VoltageBox
.AddItem "110V"
.AddItem "220V"
End With
With .UnitsBox
.AddItem "Imperial"
.AddItem "Metric"
.AddItem "Mixed"
End With
With .TorqueBox
.AddItem "Electric"
.AddItem "Hydraulic"
End With
With .ProbeBox
.AddItem "Radar"
.AddItem "Mud Probe"
.AddItem "Both"
End With
For Each itm In Array(.SideKickBox, .UJBBox)
itm.AddItem "Yes"
itm.AddItem "No"
Next itm
For Each itm In Array(.AutoDrillerBox, .CasingBox, .ChokeBox, .EDRBox, .ePVTBox, .ESRBox, .FlowBox, .GABox, .HGasBox, .PRDBox, .PVTBox, .WorkstationsBox)
itm.AddItem "No"
itm.AddItem "Yes"
Next itm
End With
End Sub

[SamT]

IMO, that sub dshould be in the Sheet's code module along with

Private Sub Worksheet_Activate()
Static Sheet_Initialized As Boolean

If Not Sheet_Initialized Then SetSystemSelectionBoxes
Sheet_Initialized = True

End Sub

Private Sub SetSystemSelectionBoxes()
'
'
End Sub

[LordDragon]

p45cal,

Thanks, I was going to look into making my code easier to use when I was done adding all the features that were required.

I'll have to make a few minor tweaks to your suggested code. There are some of the boxes that are "Yes & No" that need to default to Yes and others that need to default to No. But that won't be a big deal.

[p45cal]

There are some of the boxes that are "Yes & No" that need to default to Yes and others that need to default to No. But that won't be a big deal.I tried to adhere to your Yes/No order, but all start off blank, unless you set a default value at the same time (and then it may not matter which order Yes and No come in in the list). Also if the controls on the sheet are already present, just using .additem will continue adding more and more items each time the code is run unless you .clear it first, so you might end up with the likes of:
For Each itm In Array(.SideKickBox, .UJBBox)
itm.Clear
itm.AddItem "Yes"
itm.AddItem "No"
itm.Value = "Yes"
Next itm

[snb]

Sub M_snb()
With sheets("System Selection")
.VoltageBox.List=split("110V 220V")
.UnitsBox.list= split("Imperial Metric Mixed")
.TorqueBox.List= split("Electric Hydraulic")
.ProbeBox.ist=split("Radar Mud_Probe Both")
.SideKickBox.List=split("Yes No")
For Each it In Array(.AutoDrillerBox, .CasingBox, .ChokeBox, .EDRBox, .ePVTBox, .ESRBox, .FlowBox, .GABox, .HGasBox, .PRDBox, .PVTBox, .WorkstationsBox)
it.List= .sidkickbox.list)
next
end with
End Sub