Updating value in form text box based on combo box input

[burl_h]

I have a form that I load with data from a worksheet, the loading of
the data works fine, see abbreviated code below.

To perform some further evaluation I added some text and combo boxes to
the form, see code at bottom, with the selection from the combo box
PartType of either "Blank" or "Finished" and the listbox PartSize of
certain size I want to apply a cycle time into the text box
PartPrepTime using if statements (for simplicity purposes I only show 2
if statements, but I will enter about 16 in total when complete). The
added code seems to work, it populates the combo box and allows me to
select, I can enter a value into the PartSize text box, but nothing is
diplayed in the text box PartPrepTime. I must be doing something real
stupid can anyone help.

Regards
burl_h

start existing code...........
Private Sub Userform_Initialize()
Dim myVar(1 To 62) As Variant


With frmQuoteForm.ComboBox1
myVar1 = .List(.ListIndex, 0)
myVar2 = .List(.ListIndex, 1)
....
....
myVar62 = .List(.ListIndex, 110)
End With

frmQuoteForm.txtQuote.Value = myVar1
frmQuoteForm.txtExtAttrib.Value = myVar2
......
......
frmQuoteForm.txtMisc.Value = myVar62

end existing code............


added code.........

Dim arrPartType(1 To 2)
arrPartType(1) = "Blank"
arrPartType(2) = "Finished"

Dim EntryCount As Long
For EntryCount = 1 To 2
ComboBoxPartType.AddItem arrPartType(EntryCount)
Next

If Me.ComboBoxPartType = "Blank" And Me.txtPartSize.Value <= 2.0 Then
Me.txtPartPrepTime.Value = "5.0 minutes"
End If

If Me.ComboBoxPartType = "Blank" And Me.txtPartSize.Value <= 4.0 Then
Me.txtPartPrepTime.Value = "8.0 minutes"
End If

end added code...........


End Sub

 

[Guest]

Hi:

Please see my comments in the code.

I think the reason why it was not working was because you did not set the
first item in the combo box.

It is better to use option explicit this will ensure that the variables you
have defined are used.


Private Sub Userform_Initialize()
'You declare this and don't use it.
Dim myVar(1 To 62) As Variant

' it would be better to use a loop but you do not really need
' it as you do not use the variables elsewhere save for the
' assignment below.

' With frmQuoteForm.ComboBox1
' myVar1 = .List(.ListIndex, 0)
' myVar2 = .List(.ListIndex, 1)
' ....
' ....
' myVar62 = .List(.ListIndex, 110) ' is this 62 or 110?
' End With

' frmQuoteForm.txtQuote.Value = myVar1
'frmQuoteForm.txtExtAttrib.Value = myVar2
'......
'......
'frmQuoteForm.txtMisc.Value = myVar62

With me
.txtQuote.Value = = .ComboBox1.List(.ListIndex, 0)
.txtExtAttrib.Value = .ComboBox1.List(.ListIndex, 1)
'......
'......
.txtMisc.Value = .ComboBox1.List(.ListIndex, 110)
end with

' end existing code............

' added code.........

Dim arrPartType(1 To 2)
arrPartType(1) = "Blank"
arrPartType(2) = "Finished"
'add all of them to the list
me.ComboBoxPartType.list = arrPartType
'set the first one on the list
me.ComboBoxPartType.listindex = 0
If Me.ComboBoxPartType = arrPartType(1) _
And Me.txtPartSize.Value <= 2.0 Then
Me.txtPartPrepTime.Value = "5.0 minutes"
elseIf Me.ComboBoxPartType = arrPartType(1) _
And Me.txtPartSize.Value <= 4.0 Then
Me.txtPartPrepTime.Value = "8.0 minutes"
End If

'end added code...........

End Sub

 

[burl_h]

Martin, thanks for your reply.

The existing code I don't have any problem with, it's just the
additional combo and list boxes I added at the end where the problem
exists.
I did try your solution (see below), again I could not get the
PartPrepTime box to display the result of the if statement, any other
thoughts?

burl_h


Dim arrPartType(1 To 2)
arrPartType(1) = "Blank"
arrPartType(2) = "Finished"
'add all of them to the list
me.ComboBoxPartType.list = arrPartType
'set the first one on the list
me.ComboBoxPartType.listindex = 0
If Me.ComboBoxPartType = arrPartType(1) _
And Me.txtPartSize.Value <= 2.0 Then
Me.txtPartPrepTime.Value = "5.0 minutes"
elseIf Me.ComboBoxPartType = arrPartType(1) _
And Me.txtPartSize.Value <= 4.0 Then
Me.txtPartPrepTime.Value = "8.0 minutes"
End If