ComboBox & TextBox Problem

[Hazel]

Hi

I have a UserForm with a ComboBox & TextBoxes and using the the code below
gleaned off this forum

Private Sub UserForm_Initialize()
With Me.ComboBox1
..ColumnCount = 2
..ColumnWidths = "20;100"
..AddItem "A"
..List(.ListCount - 1, 1) = "APPLE"
..AddItem "G"
..List(.ListCount - 1, 1) = "GRAPEFRUIT"
..AddItem "P"
..List(.ListCount - 1, 1) = "PEAR"
'and so on
End With
End Sub

Private Sub ComboBox1_Change()
Tb9.Value = Tb2.Value
Tb10.Value = Tb2.Value
Tb11.Value = Tb2.Value

My problem is that if I change and Select "A" in the ComboBox Tb9,Tb10,Tb11
all display the value of Tb2. I actually need Tb10,Tb11 to display a 0 (zero)
and likewise selecting "G" Tb9,Tb11, to display a 0 (zero) selecting "P"
Tb9,Tb10 to display a 0(zero) can any of you experts out there help me.

 

[Patrick Molloy]

you haven't said where tb2 gets its value from.

your code for the combo change specifiaclly sets the values of tb8,tb10 and
tb11 to tb2. You say that isn't what you need, so delete those lines.

you're also somewhat vague at to how these should be set to zero. they are
text boxes...


you could use
SELECT CASE Combobox1.Value
CASE "P"
tb9=0
tb10=0
CASE "G"
tb9=0
tb11=0
CASE "A"
tb10=tb2
tb11=tb2
END SELECT

 

[Hazel]

Hi Patrick

Thanks for the quick response -- just used your code -- played around with
it and its now doing exactly what I needed -- as regards the value of Tb2 I
enter the info in the textbox -- it is actually numeric and it is now putting
it exactly where I want it on Sheet1. Only got to do 30 entries on the List
and Add Items good job there are only 3 textboxes.

 

[Patrick Molloy]

why not just point the list box to a range?

example:
Sheet1!B545

 

[Hazel]

Hi Patrick

By the time I received your mail, I had already done the alterations in the
Add Item format and it works spot on for which I thank you, just one question
and I have read this somewhere but for the life of me I cannot remember where
is it possible to have 2 textboxes say Tb16 where if you enter a number 1
thru 10 Tb17 would display a 0(zero) however if you entered 11 thru 100 in
Tb16, Tb17 would display 1000 would it be an AfterUpdate Event??

Many Thanks

 

[Patrick Molloy]

yuo can still use a select case statement ....and I'd suggest that you use
the textbox change evemt --its prety fast ...

Option Explicit

Private Sub tb16_Change()
Select Case tb16.Value
Case 1 To 10
tb17 = 0
Case 11 To 100
tb17 = 100
Case Else
tb17 = ""
End Select
End Sub

 

[Hazel]

Hi Patrick

Your help much appreciated and all now working exactly as required - superb.