OK, so here is what I'm thinking, I have several textboxes on
userform. I also have a combobox on the userform that is bound to
workbook. I have it so that I can add an entry from the combobox to th
worksheet thus making a new entry in the list when you add it.

I want to make it so that when I add that name, I not only store th
values in from the combobox to the worksheet, but also from the othe
textboxes on the userform in seperate columns in the same row.

Then when I select the value from the combobox, I want the values fo
the other textboxes to be automatically updated with those value
corresponding to the value chosen in the combobox. How can I go abou
doing this

--
Message posted from http://www.ExcelForum.com

I answered only because you seemed to be getting ignored.
I'm definately no expert. It did work as you requested in
my trials. To run the demo:

1) Delete existing ControlSource and Value settings for
the combo box if they are set.
2) The code assumes only 5 text boxes. These are named
TextBox1 through TextBox5. The combo box name is assumed
to be ComboBox1. Change the code or the control names to
suit.
3) It is assumed that the table of names starts in Cell
A5. Change to the code to suit.
4) Paste the below code to the UF code module.
5) Correct for word wrap.

'Module level declarations
Dim Rng As Range, Rw As Long, Found As Boolean

Private Sub UserForm_Activate()
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub ComboBox1_Change()
Dim i As Long, ii As Long
Found = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Text Then
Found = True
Exit For
End If
Next
If Found Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng(i +
1).Row, ii + 1)
Next
End If
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long, ii As Long
If Not Found Then
Cells(Rw + 1, 1).Value = ComboBox1.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" &
i).Text
Next
End If
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(1, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Re your other post "Timing problem can someone . . . "

I think the answer is simply to use the statement
Application.EnableEvents = False before transfering the
data. Then set it to True immediately after. This should
stop the workbook from responding to the cell changes
resulting from the data transfer. Therefore, the UF won't
be repeatedly called. This is application level code so I
assume it will work. I havn't bothered to test it so I
answered it here to avoid nixing your chance of getting a
better answer.

Regards,
Greg

I should add that you have to press Enter after adding a
name to the list for it to take. This allows you to
correct spelling etc. before execution.

Regards,
Greg

-----Original Message-----
I answered only because you seemed to be getting ignored.
I'm definately no expert. It did work as you requested in
my trials. To run the demo:

1) Delete existing ControlSource and Value settings for
the combo box if they are set.
2) The code assumes only 5 text boxes. These are named
TextBox1 through TextBox5. The combo box name is assumed
to be ComboBox1. Change the code or the control names to
suit.
3) It is assumed that the table of names starts in Cell
A5. Change to the code to suit.
4) Paste the below code to the UF code module.
5) Correct for word wrap.

'Module level declarations
Dim Rng As Range, Rw As Long, Found As Boolean

Private Sub UserForm_Activate()
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub ComboBox1_Change()
Dim i As Long, ii As Long
Found = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Text Then
Found = True
Exit For
End If
Next
If Found Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng(i +
1).Row, ii + 1)
Next
End If
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long, ii As Long
If Not Found Then
Cells(Rw + 1, 1).Value = ComboBox1.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" &
i).Text
Next
End If
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(1, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Re your other post "Timing problem can someone . . . "

I think the answer is simply to use the statement
Application.EnableEvents = False before transfering the
data. Then set it to True immediately after. This should
stop the workbook from responding to the cell changes
resulting from the data transfer. Therefore, the UF won't
be repeatedly called. This is application level code so I
assume it will work. I havn't bothered to test it so I
answered it here to avoid nixing your chance of getting a
better answer.

Regards,
Greg





.

On second thought, I think the change event should be
dispenced with altogether for executing the code. The
spontaneous execution of the Change event can result in
errors. Suggested is the following. You can use the
ComboBox_Exit event to execute the code (as shown below),
or, alternatively, include the code in a Private sub in
the UF code module and fire it using an Apply button.
Change references to suit. Correct for word wrap.

'Module level declarations
Dim Rng As Range, Rw As Long

Private Sub UserForm_Activate()
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub ComboBox1_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Dim i As Long, ii As Long, Found As Boolean
Found = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Text Then
Found = True
Exit For
End If
Next
If Found Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng(i +
1).Row, ii + 1)
Next
Else
Cells(Rw + 1, 1).Value = ComboBox1.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" &
i).Text
Next
End If
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Regards,
Greg

Thanks for trying to help me out here, so I tried it out, this is wha
I've got so far...but I'm getting a compile error sub or function no
defined at
Code
-------------------
Private Sub AddName_Click(
-------------------
here is the what I've got...


Code
-------------------
Private Sub AddName_Click()
Dim i As Long, ii As Long, Found As Boolean
Found = False
For i = 0 To NameBox.ListCount - 1
If NameBox.List(i) = NameBox.Text Then
Found = True
Exit For
End If
Next
If Found Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng(i + 1).Row, ii + 1)
Next
Else
Cells(Rw + 1, 1).Value = NameBox.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" & i).Text
Next
End If
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
NameBox.RowSource = Rng.Address
End Su
-------------------


And the other piece in...


Code
-------------------
Private Sub UserForm_Initialize()
Dim Rng As Range, Rw As Long
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
NameBox.RowSource = Rng.Address
End Su
-------------------


--
Message posted from http://www.ExcelForum.com

The error is probably because you declared the variables
Rng and Rw locally in the UserForm_Initialize code and
then assigned them values. You need to reference these
variables at module level so that they are accessible to
the AddName_Click procedure. Place the following
declarations at the top of the UF code module and remove
the declarations from the UserForm_Initialize procedu
Dim Rng As Range, Rw As Long

FYI, I conducted the following trial successfully. Note
that I kept the default names for all added controls
except for CommandButton2 which I renamed "ApplyBtn". I
decided upon a command button to execute the code to avoid
confusion and complications. The code worked as advertised
in my trials.

Trial instructions:
1) Created a UF.
2) Added 5 text boxes.
3) Added a combo box.
4) Added two command buttons.
5) Changed the name of the second command button
to "ApplyBtn".
6) Added some miscellaneous names to the cells A5:A10 and
put miscellaneous info in the five adjacent columns
(B5:F10).
7) I pasted the below code to the UF code module including
the module level declarations.

'Module level declarations
Dim Rng As Range, Rw As Long

Private Sub ApplyBtn_Click()
Dim i As Long, ii As Long, Found As Boolean
Found = False
For i = 0 To ComboBox1.ListCount - 1
If ComboBox1.List(i) = ComboBox1.Text Then
Found = True
Exit For
End If
Next
If Found Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng(i +
1).Row, ii + 1)
Next
Else
Cells(Rw + 1, 1).Value = ComboBox1.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" &
i).Text
Next
End If
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub UserForm_Initialize()
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Regards,
Greg

This works to enter the persons information into the worksheet, but
want to have it now, so that when I choose the name from the list, tha
the information entered into those other cells automatically shows u
in the other text boxe

--
Message posted from http://www.ExcelForum.com

For the code I gave you yesterday, you have to click the
Apply button both to retrieve the cell values and place
them into the text boxes as well as to paste the text box
values to the worksheet. It depends upon whether you
selected a name from the list or typed a new one into the
window. If that's what you were doing and it still didn't
work then your setup is somehow different from mine.

However, never mind yesterday's code. I wrote an update
(see below) that it will automatically retrieve the cell
values and place them into the text boxes without having
to click the Apply button. Just select a name from the
list. The Apply button is needed only to paste new data to
the worksheet into a new row.

My trials confirmed that this works. Make sure that you
create the UF as I described yesterday. The worksheet data
as per the current setup starts in Cell A5 and continues
down for as long as you like. Miscellaneous data is placed
in the adjacent five columns (Columns B to F). Correct for
word wrap.

'Module level declarations
Dim Rng As Range, Rw As Long

Private Sub UserForm_Initialize()
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub ApplyBtn_Click()
Dim i As Long
Cells(Rw + 1, 1).Value = ComboBox1.Value
For i = 1 To 5
Cells(Rw + 1, i + 1) = Controls("TextBox" & i).Text
Next
Rw = Range("A65536").End(xlUp).Row
Set Rng = ActiveSheet.Range(Cells(5, 1), Cells(Rw, 1))
ComboBox1.RowSource = Rng.Address
End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Integer, ii As Integer

With ComboBox1
If .ListIndex = 0 Then
For ii = 1 To 5
Controls("TextBox" & ii).Text = Cells(Rng
(.ListIndex + 1).Row, ii + 1)
Next
End If
End With
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Regards,
Greg



-----Original Message-----
This works to enter the persons information into the
worksheet, but I
want to have it now, so that when I choose the name from
the list, that
the information entered into those other cells
automatically shows up
in the other text boxes


---
Message posted from http://www.ExcelForum.com/

.

YOU ARE THE MAN! That worked just like I want it to. Thank you soooo
much

--
Message posted from http://www.ExcelForum.com