TYPE MISMATCH

[smithb2]

I am getting a runtime error of "Type Mismatch" and can not figure out
why:

I declare the following:

Public ctlFormControl, ctlFormControl_2 As Controls

These Control variables are then used in the following procedure which
fills in item controls on a form called "frmCreateOrder" by taking text
from "frmNewOrder." This is done by looping through the controls on
the given form, using the TabIndex of the controls on the form.

Sub CreateOrderItems()
For i = 4 To 12 Step 2
For Each ctlFormControl In frmNewOrder.Controls
If ctlFormControl.TabIndex = i Then
strCtrltext = ctlFormControl.Text
For j = 9 To 33 Step 6


TYPE MISMATCH OCCURS IN THE FOLLOWING BLOCK:

For Each ctlFormControl_2 In
frmCreateOrder.Controls
If ctlFormControl_2.TabIndex = i Then
ctlFormControl_2.Caption = strCtrltext
End If
Next ctlFormControl_2


Next j
End If
Next ctlFormControl
Next i

It appears that VBA is having a problem with ctlFormControl_2. The
puzzling thing is that ctlFormControl is declared the same way, used in
the same way, and works.

Any Suggestions as to what the problem is?

Thanks,
Brian

 

[Doug Glancy]

Brian,

Your control declaration actually declared ctlFormControl as a variant,
which is why there was no error with that one. With ctlFormControl_2 you
want to declare it as a control - not plural:

Public ctlFormControl as Control, ctlFormControl_2 As Control

hth,

Doug

 

[Jim Thomlinson]

ctlFormControl is of type variant, so you do not get the error.
ctlFormControl_2 is of type control (which I don't think will compile).
Change it to type variant and you should be off and running...

Check out this link on declaring variables...
http://www.cpearson.com/excel/variables.htm

 

[smithb2]

Thanks Doug & Jim!

That makes a lot of sense and I think that problem is solved.

The big problem that I have now is that control objects, i.e.
ctlFormControl & ctlFormControl2, do not support the text or caption
proporties and I need to be able to get the text or cation out of the
selected control..........I can't see a way to do this. For example:

in this procedure I am looping through the controls on form
frmQueryResult, getting the tabindex for a given control on the form,
then finding the corresponding control on form frmCreateOrder. I want
to set the control value in frmCreateOrder to the value in the
frmQueryResult control.

Doesn't work...........

'fill in item availability controls on create order form
Sub CreateOrderItemAvail()
For i = 6 To 22 Step 4
For Each ctlFormControl In frmQueryResult.Controls
If ctlFormControl.TabIndex = i Then
strCtrltext = ctlFormControl.Caption
For j = 11 To 35 Step 6
For Each ctlFormControl_2 In
frmCreateOrder.Controls
If ctlFormControl_2.TabIndex = j Then
ctlFormControl_2.Caption = strCtrltext
End If
Next ctlFormControl_2
Next j
End If
Next ctlFormControl
Next i
End Sub

Brian

 

[Doug Glancy]

I'm in a hurry, so don't have time to name all your variables back to the
beginning. Take a look at this though, I think it will give some help.
Don't see a need for global variables. TabIndex starts at 0, not 1, which I
realized easily since my forms only had one control each.

Anyways, hope this gives some help:

Option Explicit

Sub CreateOrderItemAvail()

Dim ctlFormControl, ctlFormControl_2 As MSForms.Control
Dim i As Long, j As Long
Dim strCtrltext As String

For i = 0 To 22 Step 4
For Each ctlFormControl In UserForm1.Controls
If ctlFormControl.TabIndex = i Then
strCtrltext = ctlFormControl.Caption
For j = 0 To 35 Step 6
For Each ctlFormControl_2 In UserForm2.Controls
If ctlFormControl_2.TabIndex = j Then
ctlFormControl_2.Caption = strCtrltext
End If
Next ctlFormControl_2
Next j
End If
Next ctlFormControl
Next i
UserForm1.Show
UserForm2.Show
MsgBox "Is this what you want?"
Unload UserForm1
Unload UserForm2

End Sub

Doug