Userform Advice/Help

[Pete]

Need some help/advice

I have a spreadsheet with 40 rows

Each row contains the following

Column A = Last Name
Column B = First Name
Column C = Dollar Amount
Column D = Date
Column E = Comments (50 String Length)

I have a User Form with the following

Combobox1 = Column A and Column B
Textbox1 = Column A
Textbox2 = Column B
Textbox3 = Column C
Textbox4 = Column D
Textbox5 = Column E

Controlbutton1 = Update
Controlbutton2 = Cancel

I want to be able to load the combobox1 with column a/b rows 1 to 40

When a user scrolls the combobox1 I want the corresponding textboxes from
the spreadsheet to be filled in. (if the user scrolls the second name row
2, then textbox1 = row 2 col a, textbox1 = row 2 col b, etc…

If a user changes the data in the userform I want to update the
spreadsheet for that record.

Does this make any sense.

I need to be shown how this can be done. I'm not even sure it can be
done.

 

[Paul Martin]

Hi Pete

You'll need to set your ComboBox ColumnCount property to 2, and
RowSource to A1:E40. This will populate the ComboBox.

On the ComboBox_Change() event, you'll need something like:

Dim iRow as Integer

iRow = ComboBox.ListIndex + 1
txtbox1.Value = Cells(iRow, 1).Value
txtbox2.Value = Cells(iRow, 2).Value
txtbox3.Value = Cells(iRow, 3).Value
etc

If you have a CommandButton to save the textbox values to the
worksheet, on the Button_Click() event put:

Dim iRow as Integer

iRow = ComboBox.ListIndex + 1
Cells(iRow, 1).Value = txtbox1.Value
Cells(iRow, 2).Value = txtbox2.Value
etc

Regards

Paul Martin
Melbourne, Australia

 

[Pete]

Thanks Paul, That was enough to get me started. The rowsource was what I
was looking for. Works just like I wanted it to. Now I can go play with
it.

 

[Pete]

Hey Paul, Everything went find until the update portion.
When I wnet and did the update the only cell(S) that updated
on the spreadsheet were column a

here is my code

Private Sub ComboBox1_Change()
'this works
Dim iRow As Integer
iRow = ComboBox1.ListIndex + 1
UserForm1.TextBox1.Value = Cells(iRow, 1).Value
UserForm1.TextBox2.Value = Cells(iRow, 2).Value
UserForm1.TextBox3.Value = Cells(iRow, 3).Value
UserForm1.TextBox4.Value = Cells(iRow, 4).Value
UserForm1.TextBox5.Value = Cells(iRow, 5).Value


End Sub

Private Sub CommandButton1_Click()
'this does not work, only the first gets updated
Dim iRow As Integer
iRow = ComboBox1.ListIndex + 1
Sheets("sheet1").Cells(iRow, 1).Value = UserForm1.TextBox1.Value
'none of these below update the sheet
Sheets("sheet1").Cells(iRow, 2).Value = UserForm1.TextBox2.Value
Sheets("sheet1").Cells(iRow, 3).Value = UserForm1.TextBox3.Value
Sheets("sheet1").Cells(iRow, 4).Value = UserForm1.TextBox4.Value
Sheets("sheet1").Cells(iRow, 5).Value = UserForm1.TextBox5.Value
End Sub

Private Sub CommandButton2_Click()
'just a cancel button to exit out
UserForm1.Hide
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
ComboBox1.ColumnCount = 2
ComboBox1.RowSource = "a1:e40"
End Sub

 

[Paul Martin]

Hi Pete

I'm not sure what your problem is. What's the error you're getting?

Just a couple of points on the side:
- You do not need to code the ComboBox properties. You can set these
properties in the Properties Window (View, Properties Window)
- If you're unloading UserForm1, you do not need to hide it.

Regards

Paul Martin
Melbourne, Australia

 

[Tom Ogilvy]

Try it this way:

Private Sub ComboBox1_Change()
'this works
Dim iRow As Integer
iRow = ComboBox1.ListIndex + 1
UserForm1.TextBox1.Value = Cells(iRow, 1).Value
UserForm1.TextBox2.Value = Cells(iRow, 2).Value
UserForm1.TextBox3.Value = Cells(iRow, 3).Value
UserForm1.TextBox4.Value = Cells(iRow, 4).Value
UserForm1.TextBox5.Value = Cells(iRow, 5).Value

End Sub

Private Sub CommandButton1_Click()
'this does not work, only the first gets updated
Dim iRow As Integer
iRow = ComboBox1.ListIndex + 1
Sheets("sheet1").Cells(iRow, 1).Value = UserForm1.TextBox1.Value
'none of these below update the sheet
Sheets("sheet1").Cells(iRow, 2).Value = UserForm1.TextBox2.Value
Sheets("sheet1").Cells(iRow, 3).Value = UserForm1.TextBox3.Value
Sheets("sheet1").Cells(iRow, 4).Value = UserForm1.TextBox4.Value
Sheets("sheet1").Cells(iRow, 5).Value = UserForm1.TextBox5.Value
ComboBox1.List = sheets("Sheet1").Range("a1:e40").Value
End Sub

Private Sub CommandButton2_Click()
'just a cancel button to exit out
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
ComboBox1.ColumnCount = 2
ComboBox1.List = sheets("Sheet1").Range("a1:e40").Value
End Sub

 

[Guest]

Hi Tom

Why would one use ComboBox1.List over ComboBox1.RowSource?

Regards

 

[Pete]

Paul, the update does not work

Private Sub CommandButton1_Click()
'this does not work, only the first gets updated
Dim iRow As Integer

iRow = ComboBox1.ListIndex + 1
'note that only cell a gets updated
Sheets("sheet1").Cells(iRow, 1).Value = UserForm1.TextBox1.Value
'none of these below update the sheet
'cells b to e revert back to the orginal spreadsheet values
Sheets("sheet1").Cells(iRow, 2).Value = UserForm1.TextBox2.Value
Sheets("sheet1").Cells(iRow, 3).Value = UserForm1.TextBox3.Value
Sheets("sheet1").Cells(iRow, 4).Value = UserForm1.TextBox4.Value
Sheets("sheet1").Cells(iRow, 5).Value = UserForm1.TextBox5.Value

End Sub

So if row 5 a:e contains the following

a5 b5 c5 d5 e5

and on the userform (which is working) I change textbox2 (refering to b5)
to anything else (same for textbox 3 to 5) it does not change the
spreadsheet values.

if i make a change to textbox1 and click the command button to update it
only updates column a

does that help?

 

[Pete]

Alright Tom, that worked, Thanks. But why? I am confused as to why
rowsource did not work and List did..

 

[Guest]

Pete

I tested the code using ListIndex, and it works for me.

Regards

Paul Martin
Melbourne, Australia

 

[Tom Ogilvy]

When you change the range referred to by rowsource, you cause another change
event to fire and I assume this clears your textboxes. Whatever happens,
there is a bad interaction invoked. In xl97, it causes a crash in my
experience (this is more towards having the rowsource alteration in the
change event itself - not the case here). In any event, disconnecting the
rowsource before making the change is usually successful. Here, I broke it
by assigning the values to the list. You can also do it by recording what
you need to work with, then clearing the rowsource and then resetting it
after making the changes, all in the same event. This type approach might
need a bit of tweaking since you have the two events.

 

[Tom Ogilvy]

To avoid the problem experienced by the OP.

 

[Paul Martin]

OP?

 

[Paul Martin]

OK, that's very helpful thanks Tom

Regards

Paul Martin
Melbourne, Australia

 

[Dave Peterson]

Original Poster.

OP?