Userform - Query Help Please

[Mark Campbell]

I have a combobox on a user form populated with a list of data from sheet 1
of my workbook. The list contains approx 200 rows of data across 20 columns.

When the user makes a selection from the combobox a number of textboxes on
the userform are populated with data from the selected row.

The user is able to amend or update the data and it is then written back to
the sheet using a button control.
I am using the code listed below to populate the textboxes.

Private Sub Comboviewer2_Change()
Dim RowRange As Range
Set RowRange = Range("workpackages").Rows _
(Me.Comboviewer2.ListIndex + 1)

If Me.Comboviewer2.ListIndex <> -1 Then
With Sheet1
..TextBox1.Text = RowRange.Columns(16).Value
..TextBox6.Text = RowRange.Columns(1).Value
..TextBox2.Text = RowRange.Columns(3).Value
..Tbox8.Text = RowRange.Columns(5).Value

My problem is as follows:
I want to be able to populate the combobox with only selected items from the
list of data on sheet1 -
e.g - only items where a particular column is empty or contains a date.

I am using the code below which populates the combobox as required.

I cannot figure out however how to populate the textboxes on the userform
with data from the row selected in the combobox or how to write the data from
the textboxes back to sheet1. The listindex code I was using previously does
not seem to work??

Thanks for any help or advice.

Regards

Mark

Private Sub UserForm_Initialize()
Dim rng As Range
Dim irow As Integer
Dim iCt As Integer
Dim c As Range
irow = Sheets("sheet1").Range("A1").End(xlDown).Row
Set rng = Sheets("sheet1").Range("A2:A" & irow)
For Each c In rng
If c.Offset(0, 8) <> "" And c.Offset(0, 9) = "" Then
iCt = iCt + 1
With Me.Comboviewer2
AddItem c
End If
Next c
End With
End Sub

 

[PCLIVE]

Here is a possible partial solution for you.

As for populating the textboxes on the userform, you can try something like
this.

UserForm1.Textbox1.Text = Range("A" & Selection.Row).Value

This is not tested, but it should get you started on that portion of your
question.

Regards,
Paul

 

[Jim Rech]

I didn't run this code but it looks as if you didn't match your With and For
loops right. You really don't need the With so I dropped it here. Do
yourself a favor and start indenting your code so you can read and
understand it easier.

Private Sub UserForm_Initialize()
Dim rng As Range
Dim irow As Integer
Dim iCt As Integer
Dim c As Range
irow = Sheets("sheet1").Range("A1").End(xlDown).Row
Set rng = Sheets("sheet1").Range("A2:A" & irow)
For Each c In rng
If c.Offset(0, 8).Value <> "" And c.Offset(0, 9).Value = "" Then
iCt = iCt + 1 ''Using this?
Comboviewer2.AddItem c.Value
End If
Next c
End Sub

--
Jim
|I have a combobox on a user form populated with a list of data from sheet 1
| of my workbook. The list contains approx 200 rows of data across 20
columns.
|
| When the user makes a selection from the combobox a number of textboxes on
| the userform are populated with data from the selected row.
|
| The user is able to amend or update the data and it is then written back
to
| the sheet using a button control.
| I am using the code listed below to populate the textboxes.
|
| Private Sub Comboviewer2_Change()
| Dim RowRange As Range
| Set RowRange = Range("workpackages").Rows _
| (Me.Comboviewer2.ListIndex + 1)
|
| If Me.Comboviewer2.ListIndex <> -1 Then
| With Sheet1
| .TextBox1.Text = RowRange.Columns(16).Value
| .TextBox6.Text = RowRange.Columns(1).Value
| .TextBox2.Text = RowRange.Columns(3).Value
| .Tbox8.Text = RowRange.Columns(5).Value
|
| My problem is as follows:
| I want to be able to populate the combobox with only selected items from
the
| list of data on sheet1 -
| e.g - only items where a particular column is empty or contains a date.
|
| I am using the code below which populates the combobox as required.
|
| I cannot figure out however how to populate the textboxes on the userform
| with data from the row selected in the combobox or how to write the data
from
| the textboxes back to sheet1. The listindex code I was using previously
does
| not seem to work??
|
| Thanks for any help or advice.
|
| Regards
|
| Mark
|
| Private Sub UserForm_Initialize()
| Dim rng As Range
| Dim irow As Integer
| Dim iCt As Integer
| Dim c As Range
| irow = Sheets("sheet1").Range("A1").End(xlDown).Row
| Set rng = Sheets("sheet1").Range("A2:A" & irow)
| For Each c In rng
| If c.Offset(0, 8) <> "" And c.Offset(0, 9) = "" Then
| iCt = iCt + 1
| With Me.Comboviewer2
| AddItem c
| End If
| Next c
| End With
| End Sub
|
|