MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can anyone help me?


Posted by M Walker on July 11, 2001 1:18 AM

Hi,

I'm trying to write a macro that will display a value in a text box when an item is selected form a dropdown list on a userform.

Does anyone know how i can do this?

Thanks in advance.

M Walker


Posted by Ivan F Moala on July 11, 2001 3:01 AM

By dropdown list you mean Combobox.
Then ;
Assumning Combobox1 is it's name AND Textbox1 is the textbox name.....use the comboboxs change
code eg.

Private Sub ComboBox1_Change()
TextBox1.Text = ComboBox1.Value
End Sub

HTH

Ivan

Posted by M Walker on July 11, 2001 3:47 AM

Thanks, however...

Cheers Ivan,

I've now got a response when i click on the combobox... but its the name that i selected form the list not a value!

The value that i would like to show is in the next column. Heres the code i have at the moment:

Application.Worksheets("Totals").Activate
ComboBox1.ColumnCount = 5
ComboBox1.RowSource = "a5:a47"
ComboBox2.ColumnCount = 5
ComboBox2.RowSource = "a48:a157"
ComboBox3.ColumnCount = 5
ComboBox3.RowSource = "a185:a304"
ComboBox4.ColumnCount = 5
ComboBox4.RowSource = "a305:a316"
ComboBox5.ColumnCount = 5
ComboBox5.RowSource = "a158:a184"

So, for example, the response from selecting an item form combobox1 is in the range b5:b47.

Could you help me futher?

Thanks.

M Walker

Posted by Ivan F Moala on July 11, 2001 3:59 AM

Re: Thanks, however...

Try
Private Sub ComboBox1_Change()
TextBox1.Text = Cells(5 + ComboBox1.ListIndex, 2)
End Sub


Note: Combobox2 instead of 5 put in 48 etc

Ivan

Posted by M Walker on July 11, 2001 4:36 AM

Re: Thanks, however...

Hi, again.

That works a treat. Thank you very much. There is just one more question that i can't figure out. Can i use the text box to insert a figure into a formula which will be used to calcul;ate the total and how would i save the new total, replacing the old one?

At the moment i have a four columned spreadsheet named as such; Description of product, addition, Subtraction and Totals. The cells in Totals have a forumal in then which is dependant upon the numbers placed in the previous 2 columns. Ideally i would like the value placed i the text box to be placed in these cells and be incorperated into the Totals formula.

Hopefully not too complicated.

Thanks again.

M Walker

Posted by Ivan F Moala on July 11, 2001 11:22 AM

Re: Thanks, however...

Just reference your textbox.value to the data range for your addition / subtraction columns.

Also note that textboxs have no masking facilities
for data type eg for entering numbers vs text so
a user can enter text. To get around this you could
use something like this;

Private Sub Textbox1_Change()
Dim curpos As Double
curpos = TextBox1.SelStart
If Not ValidateNumeric(TextBox1.Text) Then
TextBox1.Text = Left(TextBox1.Text, curpos - 2)
Else
Range("A1") = TextBox1.Value
End If
End Sub

Private Function ValidateNumeric(strText As String) As Boolean
ValidateNumeric = CBool(strText = "" _
Or strText = "-" _
Or strText = "-." _
Or strText = "." _
Or IsNumeric(strText))
End Function

This will only let in valid figs including -ve
and decimal places.

Ivan

for your textbox I would suggest something like;