| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating value in form text box based on combo box input
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating value in form text box based on combo box input
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 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "burl_h" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Updating value in form text box based on combo box input
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 Martin Fishlock wrote: 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 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "burl_h" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
| specify a specific column to input text based on another cell's content | Excel Programming | |||
| Updating cell values on sheet based on selected combo box value | Excel Programming | |||
| Web Qery Based On Form Field Input | Excel Programming | |||
| Change the size of text used in a Form combo box | Excel Discussion (Misc queries) | |||
Linear Mode
