Solved: Timer Panel\Please Wait userform Help

[Phelony]

Hi Guys

I've written the below, but I'm clearly not doing something right.

I've got a userform with 9 image boxes in it, each of them starts blank (blended with the form background) and then each second one colours itself blue until all 9 are complete and then it closes.

I actually need different timescales, but for testing purposes seconds are fine.

Ultimately, this userform will pop up and start it's "animation" while an entirely seperate script runs in the background of Excel.

Could someone point out what I'm not doing right please?

this is in the userform code

[VBA]
Private Sub userform_activate()
Application.OnTime Now + TimeValue("0:00:01"), Name:="First"
Application.OnTime Now + TimeValue("0:00:02"), Name:="second"
Application.OnTime Now + TimeValue("0:00:03"), Name:="third"
Application.OnTime Now + TimeValue("0:00:04"), Name:="fourth"
Application.OnTime Now + TimeValue("0:00:05"), Name:="fifth"
Application.OnTime Now + TimeValue("0:00:06"), Name:="sixth"
Application.OnTime Now + TimeValue("0:00:07"), Name:="seventh"
Application.OnTime Now + TimeValue("0:00:08"), Name:="eighth"
Application.OnTime Now + TimeValue("0:00:09"), Name:="ninth"
Unload waitingform
End Sub
[/VBA]

this (and others like it) are in a seperate module

[VBA]Sub first()
Image1.BackColor = vbBlue
End Sub[/VBA]


I'm just bashing my head against a wall now and am probably just tying myself in knots.

Any help will be greatly appreciated.

Phel

[GTO]

Greetings Phel,

Not tested, but I would greatly suspect unloading the form in this manner as the culprit. After 'Unload waitingform' executes occurs and before the first procedure awaiting execution (from OnTime) executes, the form and the form's object module no longer exist to access.

Mark

[Phelony]

That helped, it's actually started running the code! Thanks for that.

Although I seem to be having some object issues:

[VBA]Sub first()
With waitingform
Image1.BackColor = vbBlue
End With
End Sub[/VBA]

It's highlighting the Image1 line as requiring an object, I thought the "with" would solve that but apparently not.

Not entirely sure what it needs naming as an object, the image1 or the waitingform userform?

Object naming is a particular pet hate of mine!

Any ideas?

[Phelony]

Making progress....

So I've determined that the module code needs to be:

[VBA]
Sub first()
Dim Image1 As ConsoDebtProj.waitingform.Image1
Set Image1 = ConsoDebtProj.waitingform.Image1
Image1.BackColor = vbBlue
End Sub
[/VBA]

However, I keep getting the "user defined type not defined" errror.

So to the reference libraries and enable the activex controls. However, this is still not working...so I'm kinda at a loss.

[GTO]

That helped, it's actually started running the code! Thanks for that.

Although I seem to be having some object issues:

[vba]Sub first()
With waitingform
Image1.BackColor = vbBlue
End With
End Sub[/vba]

It's highlighting the Image1 line as requiring an object, I thought the "with" would solve that but apparently not.

Not entirely sure what it needs naming as an object, the image1 or the waitingform userform?

Object naming is a particular pet hate of mine!

Any ideas?

When you use With, anything that is between With and End With that you want to go with what is referenced in the With line (in this case, waitingform), must start with a dot - like:

[VBA]Sub First()
With UserForm1.Image1
.BackColor = vbBlue
End With
End Sub[/VBA]

Making progress....

So I've determined that the module code needs to be:

[vba]
Sub first()
Dim Image1 As ConsoDebtProj.waitingform.Image1
Set Image1 = ConsoDebtProj.waitingform.Image1
Image1.BackColor = vbBlue
End Sub
[/vba]

However, I keep getting the "user defined type not defined" errror.

So to the reference libraries and enable the activex controls. However, this is still not working...so I'm kinda at a loss.

The error is because ConsoDebtProj.waitingform.Image1 is not a legitimate variable type. See, a variable could be a String, Long, Object, etc, or, when we have other libraries referenced, certain things in the library, in this case an Image control - like:

[VBA]Sub MySub()
Dim myimage As MSForms.Image

Set myimage = UserForm1.Image1
myimage.BackColor = vbBlue
End Sub[/VBA]

Now - take those two snippets and put them in a Standard Module. Then place:

[VBA]Option Explicit

Private Sub UserForm_Activate()
Stop
'Call MySub
Call First
End Sub[/VBA]

...in a new userform with one Image control named 'Image1'. When you run the form, the Stop will of course throw you into debug mode and you can step through the code by pressing F8. You will see that either sub will now work, as long as the userform is loaded.

[Phelony]

Thanks for that.

I had at close of business Friday written as the seperate module:

[VBA]
Sub first()

Dim waitingform As Conso1.waitingform
Set waitingform = Conso1.waitingform

Dim Image1 As Object
Set Image1 = Conso1.waitingform.Image1

With waitingform

Image1.BackColor = vbBlue

End With

End Sub
[/VBA]

Which appears to be working, although as it only works when run in sequence at the moment, I've got to play a little more to get it to run while imbedded in a standalone sub.

Thank you all for your help.

Phel