Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - Query Help Please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - Query Help Please
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 "Mark Campbell" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform - Query Help Please
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 "Mark Campbell" wrote in message ... |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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another UserForm query | Excel Programming | |||
Another UserForm query | Excel Programming | |||
Query Results to UserForm | Excel Programming | |||
Userform text box query?? | Excel Programming | |||
Userform text box query?? | Excel Programming |