combobox change
 

GUS

Yes, the change event will fire when you change the data to which it is
linked. You should consider using the AfterUpdate event instead of the
Change event. When you use the Change event, it fires every time someone
enters a letter (like typing the selection instead of using the mouse) and
you will probably get errors. Another option for populating the combobox is
to use the AddItem method. Instead of setting the RowSource, you can do
something like this

Dim cell as Range

For Each cell in Sheet1.Range("A1:A10")
Me.ComboBox1.AddItem cell.Value
Next cell

This way, the combobox isn't tied to that range, you just populate it every
time you start the userform. You would put this code in the Initialize
event of the userform.

Here's another tip which you may like

Dim i As Long

For i = 1 to 14
Me.Controls("TextBox" & i).Text =
Worksheets("DATA").Cells(putval,i).Value
Next i

It's a little shorter and if you decide to add a textbox later, you can
easily update it.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"GUS" wrote in message
...
I have a strange problem
I am using a combobox to select a name from a range (data)
The range is increasing dynamicaly every time i put a new nameafter the
last
used cell.
I have also a userform contains a combobox and several textboxes

Then i am loading the userform and after that i select a name from the
combobox in order to see in the textboxes all the data from the range.
The next step is to change some of the textbox values and then press a
button in order to update the values in the cells.
I am using combobox.listindex in order to find the correct row witch
contains the data i want to change.

The strange think is happening here.

When the code update the cell witch is inside the rangename exits the sub
and goes again in combobox.change event.
The code is below
(Three subs)

Private Sub ComboBox3_Change()
putval = ComboBox3.ListIndex + 2
Sheets("DATA").Activate

Call showdata
End Sub


Private Sub showdata()
TextBox1.Value = Worksheets("DATA").Cells(putval, 1).Value
TextBox2.Value = Worksheets("DATA").Cells(putval, 2).Value
TextBox3.Value = Worksheets("DATA").Cells(putval, 3).Value
TextBox4.Value = Worksheets("DATA").Cells(putval, 4).Value
TextBox5.Value = Worksheets("DATA").Cells(putval, 5).Value
TextBox6.Value = Worksheets("DATA").Cells(putval, 6).Value
TextBox7.Value = Worksheets("DATA").Cells(putval, 7).Value
TextBox8.Value = Worksheets("DATA").Cells(putval, 8).Value
TextBox9.Value = Worksheets("DATA").Cells(putval, 9).Value
TextBox10.Value = Worksheets("DATA").Cells(putval, 10).Value
TextBox11.Value = Worksheets("DATA").Cells(putval, 11).Value
TextBox12.Value = Worksheets("DATA").Cells(putval, 12).Value
TextBox13.Value = Worksheets("DATA").Cells(putval, 13).Value
TextBox14.Value = Worksheets("DATA").Cells(putval, 14).Value
End Sub



Private Sub CommandButton1_Click()

putval2 = ComboBox3.ListIndex + 2
Sheets("DATA").Activate

ActiveWorkbook.Names("etdata").Delete

Worksheets("DATA").Cells(putval2, 1).Value = TextBox1.Value
Worksheets("DATA").Cells(putval2, 2).Value = TextBox2.Value

'when the code reach this point it goes at combobox_change event and the
'comes again.
'is this happens because the range is changing and if yes how can i
prevent
'that.


Worksheets("DATA").Cells(putval2, 3).Value = TextBox3.Value
Worksheets("DATA").Cells(putval2, 4).Value = TextBox4.Value
Worksheets("DATA").Cells(putval2, 5).Value = TextBox5.Value
Worksheets("DATA").Cells(putval2, 6).Value = TextBox6.Value
Worksheets("DATA").Cells(putval2, 7).Value = TextBox7.Value
Worksheets("DATA").Cells(putval2, 8).Value = TextBox8.Value
Worksheets("DATA").Cells(putval2, 9).Value = TextBox9.Value
Worksheets("DATA").Cells(putval2, 10).Value = TextBox10.Value
Worksheets("DATA").Cells(putval2, 11).Value = TextBox11.Value
Worksheets("DATA").Cells(putval2, 12).Value = TextBox12.Value
Worksheets("DATA").Cells(putval2, 13).Value = TextBox13.Value
Worksheets("DATA").Cells(putval2, 14).Value = TextBox14.Value




lastrow = FirstBlankRow(1, 1)

Range(Cells(2, 3), Cells(lastrow, 3)).Select
a = Range(Cells(2, 3), Cells(lastrow, 3)).Address

ActiveWorkbook.Names.Add name:="etdata", RefersTo:= _
"='DATA'!" & a

Application.ScreenUpdating = True
Unload Me
end sub

End Sub