olorin
Board Regular
- Joined
- Feb 7, 2005
- Messages
- 87
I have a userform that has a listbox and a textbox in it and a couple of buttons.
The listbox is populated by a named range in a different sheet than the main sheet where all the userforms open.
I need the value of the textbox ("productionfigure") to be entered into a particular row in the production worksheet dependant upon what was selected in the listbox.
I have this code that works fine (except for gettin it on the right sheet), but the listbox has about 120 items and I really don't want to enter 120 "If" statements.
I have been helped so much on here (hence gettin even this far), and this is the code that works if i want to add 120 "if" statements
Private Sub ProdFigOK_Click()
If ListBox1.Value = "640001A" Then
row = 2
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
If ListBox1.Value = "641001A" Then
row = 3
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
productionfigure.Value = 0
End Sub
P.S. The last line is to set the value of the textbox to "0", is there a command that would clear the contents after the calculation instead of setting it to zero?
Your help is as always greatly appreciated.
The listbox is populated by a named range in a different sheet than the main sheet where all the userforms open.
I need the value of the textbox ("productionfigure") to be entered into a particular row in the production worksheet dependant upon what was selected in the listbox.
I have this code that works fine (except for gettin it on the right sheet), but the listbox has about 120 items and I really don't want to enter 120 "If" statements.
I have been helped so much on here (hence gettin even this far), and this is the code that works if i want to add 120 "if" statements
Private Sub ProdFigOK_Click()
If ListBox1.Value = "640001A" Then
row = 2
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
If ListBox1.Value = "641001A" Then
row = 3
LastCol = Range("IV" & row).End(xlToLeft).Column
Cells(row, LastCol + 1) = productionfigure.Value
End If
productionfigure.Value = 0
End Sub
P.S. The last line is to set the value of the textbox to "0", is there a command that would clear the contents after the calculation instead of setting it to zero?
Your help is as always greatly appreciated.