Excel Crash when using set command

R

Ripan

I have a userform with numerous controls on it. The _Initialize event i
fairly extensive, and one section is setting the following array, whic
is declared in the form:

Dim statusIndicators(8) As MSForms.Image 'Array of status indicators

The code in the _Initialize method is as follows:

'Set status indicator image links
With frmDataEntry
Set statusIndicators(0) = .imgClaimInfo
Set statusIndicators(1) = .imgAccidentInfo
Set statusIndicators(2) = .imgInjuryInfo
Set statusIndicators(3) = .imgCompensability
Set statusIndicators(4) = .imgQuality
Set statusIndicators(5) = .imgRTW
Set statusIndicators(6) = .imgMM
Set statusIndicators(7) = .imgCM1
Set statusIndicators(8) = .imgCM2
End With

Somewhere in this execution, Excel crashes (that is, the "Excel ha
encountered an error and needs to shut down" dialog box with the optio
to send an error report).

Does anyone have an idea what may be wrong? Thanks
 
J

Jim Cone

Ripan,

Try it without using the Set command...
With frmDataEntry
statusIndicators(0) = .imgClaimInfo

or I would probably be using it like this...

Dim statusIndicators(0 to 8) as String
statusIndicators(0) = frmDataEntry.imgClaimInfo.Name

Regards,
Jim Cone
San Francisco, CA
 
R

Ripan

Jim,

Thanks. Quick question. If I just need to access the backcolo
property, can I set the array to just point to the backcolor propert
(In the same fashion as you have set it equal to the name field).

Thanks again,
Ripa
 
J

Jim Cone

Ripan,

Yes, and I believe it would look like the following...
(Note that BackColor is a Long)

'------------------------------------
Dim statusIndicators() as Long
ReDim statusIndicators(0 to 8)
statusIndicators(0) = frmDataEntry.imgClaimInfo.BackColor
.....

Regards,
Jim Cone
San Francisco, CA
 
R

Ripan

Jim,

Thanks again. Two issues:

1. Not using the set statement gives me a run time error (91). I nee
the Set statement for the code to execute correctly.

2. I need to set the object references so that I can use the array t
access other properties of the object.

The peculiar thing about this error is that it does not happen ever
time I use excel. In fact, if I put a break in the code or enter th
VBA editor and then re-run the model, I never run into an error. If
then close down, save, and open it, save, close it, and open it
second time without using the VBA editor. I run into the bug, and it i
always with the Set statement.

Any ideas? I know this is a very peculiar and specific bug, but an
ideas can help me out
 
D

Dave Peterson

If this is an intermittant problem, maybe you could try this against a copy of
your workbook--just in case.

Export that userform to a nice folder.
delete your userform
reimport the userform

Maybe the userform is getting, er, flakey.

If it didn't help, not too much time lost.
 
O

onedaywhen

If something works in break mode when stepping through the code it
usually indicates a timing problem. Break mode slows execution
considerably, allowing processes to complete before execution moves on
to the next process. The usual method of doing the same at run-time is
to add some DoEvents to the code. I usually add a DoEvents just after
I've done something with an ActiveX control, especially actions like
SetFocus and changing its postion/dimensions.

Try putting a DoEvents between each line in your _Initialize sub. If
you require a longer delay to get the code to work, check out the
Sleep API.
 
R

Ripan

Thanks, I appreciate the input.

I put DoEvents between each line and that does not seem to work, I
still get a crash. Do you have any information on the Sleep API (i.e.
how to use it, recommendations, etc.) that could be helpful?

Thanks
 
Top