Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
Hello all, and thank you to those who have helped me in the past. This is a
great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
Hi,
Are you trying to do this in the spreadsheet or on a VBA UserForm? -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
hello, With a user form
"ShaneDevenshire" wrote: Hi, Are you trying to do this in the spreadsheet or on a VBA UserForm? -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
Hi,
So let's say the user picks Product Name from the list of four types, then they choose Oranges from the second combo box - is this a unique item, meaning is there only one row with Oranges or many rows and if many do you want to change all rows with that name? In the third box they would enter Apples and click a button and change all Oranges to Apples? I am also concerned with what happens when they pick Price from the first drop down. The second drop down would List the name of all the Products, is that correct? Before you answer that here is a start 1. Create a user form and name and caption it 2. Add two combo boxes, name one cboCategory, the other cboItem 3. You have range names for your four columns, lets say they are Number, Name, Qty and Price. Create another column with those same four names. And name this range Category. 4. Select the first combo box and set its row source property to =Category 5. Select the second combo box and set its row source to =Name 6. Add two labels above the combo boxes and change their name property to lblCategory and lblItem. Change their caption properties to what ever you want to be displayed on the user form. 7. Add two Command Buttons. Name one cmdClose and the other cmdApply 8. Change there caption properties to read Close and Apply 9. Double-click the Close button and on the code sheet in the Close_Click subroutine add one line Unload Me Get back to me on the questions above and I will continue. -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
Thank you so muuch for this very thorough answer. I have made up some combo
boxes and just do not know what code to use. I am up to speed on the creation of the user forms and have named all of the ranges have also completed making up the user forms I have many categories that change all of the time, the one constant is the product id number and name. so instead getting them to fill in each category they are only asled for one at a time. the second userform would be where they enter the correction. When changes to categories come they are usually in order... so just say a price change came in, the user would just need to type in the product number or name and then be prompted to enter in this case the new price already selected by the first combox/listbox in the first userform.... perhaps to remind them on the user form a text box could say you are entering data for .... ( and the name that they selected in the first userform. thank you again for your help look forward to seeing what you say. "ShaneDevenshire" wrote: Hi, So let's say the user picks Product Name from the list of four types, then they choose Oranges from the second combo box - is this a unique item, meaning is there only one row with Oranges or many rows and if many do you want to change all rows with that name? In the third box they would enter Apples and click a button and change all Oranges to Apples? I am also concerned with what happens when they pick Price from the first drop down. The second drop down would List the name of all the Products, is that correct? Before you answer that here is a start 1. Create a user form and name and caption it 2. Add two combo boxes, name one cboCategory, the other cboItem 3. You have range names for your four columns, lets say they are Number, Name, Qty and Price. Create another column with those same four names. And name this range Category. 4. Select the first combo box and set its row source property to =Category 5. Select the second combo box and set its row source to =Name 6. Add two labels above the combo boxes and change their name property to lblCategory and lblItem. Change their caption properties to what ever you want to be displayed on the user form. 7. Add two Command Buttons. Name one cmdClose and the other cmdApply 8. Change there caption properties to read Close and Apply 9. Double-click the Close button and on the code sheet in the Close_Click subroutine add one line Unload Me Get back to me on the questions above and I will continue. -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
Hi,
sorry to take so long getting back, but I work full time and don't get a lot of time to look at complex problems. First, I would recommend that you keep the user on the same UserForm through out the process, Add all the controls to 1 UserForm. When they click the Apply button, see previous email, the dialog box should stay open and we need to do a search for the item and then change the appropriate field. Suppose the unique field is Product and is C2:C100, and you need to find "Oranges" then you code would look something like this: For Each cell in Range("C2:C100") If cell = Me.Product then cell.Offset(0,5) = Me.txtPrice end if Next cell -- Thanks, Shane Devenshire "Paul" wrote: Thank you so muuch for this very thorough answer. I have made up some combo boxes and just do not know what code to use. I am up to speed on the creation of the user forms and have named all of the ranges have also completed making up the user forms I have many categories that change all of the time, the one constant is the product id number and name. so instead getting them to fill in each category they are only asled for one at a time. the second userform would be where they enter the correction. When changes to categories come they are usually in order... so just say a price change came in, the user would just need to type in the product number or name and then be prompted to enter in this case the new price already selected by the first combox/listbox in the first userform.... perhaps to remind them on the user form a text box could say you are entering data for ... ( and the name that they selected in the first userform. thank you again for your help look forward to seeing what you say. "ShaneDevenshire" wrote: Hi, So let's say the user picks Product Name from the list of four types, then they choose Oranges from the second combo box - is this a unique item, meaning is there only one row with Oranges or many rows and if many do you want to change all rows with that name? In the third box they would enter Apples and click a button and change all Oranges to Apples? I am also concerned with what happens when they pick Price from the first drop down. The second drop down would List the name of all the Products, is that correct? Before you answer that here is a start 1. Create a user form and name and caption it 2. Add two combo boxes, name one cboCategory, the other cboItem 3. You have range names for your four columns, lets say they are Number, Name, Qty and Price. Create another column with those same four names. And name this range Category. 4. Select the first combo box and set its row source property to =Category 5. Select the second combo box and set its row source to =Name 6. Add two labels above the combo boxes and change their name property to lblCategory and lblItem. Change their caption properties to what ever you want to be displayed on the user form. 7. Add two Command Buttons. Name one cmdClose and the other cmdApply 8. Change there caption properties to read Close and Apply 9. Double-click the Close button and on the code sheet in the Close_Click subroutine add one line Unload Me Get back to me on the questions above and I will continue. -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
looking up data and using a combobox user form
thank you for answering i appreciate it
I ve moved on some from then but am still struggling I have now got 2 user forms I want one to come up and then when I hit ok the other comes up my code makes both come up. and then it crashes here are my codes for userform1 Private Sub CommandButton2_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim r As Range, a() As String, n As Long With Worksheets("HLT51607") For Each r In .Range(.Cells(2, 6), .Cells(2, Columns.Count).End(xlToLeft)) If r.Value < "" Then n = n + 1: ReDim Preserve a(1 To 2, 1 To n) a(1, n) = r.Value: a(2, n) = r.Column End If Next If n 0 Then Me.combobox1.Column = a End With End Sub Private Sub cbcatok_Click() myDataEntry End Sub for userform2: Private Sub cbcancel_Click() Unload Me End Sub Private Sub UserForm_Initialize() Dim r As Range, a() As String, n As Long With Worksheets("HLT51607") For Each r In .Range("a3", .Range("b65536").End(xlUp)) If r.Value < "" Then n = n + 1: ReDim Preserve a(1 To 3, 1 To n) a(1, n) = r.Text: a(2, n) = r.Offset(, 1).Text: a(3, n) = r.Row End If Next End With With Me.combobox2 .ColumnCount = 2 .ColumnWidths = "100;100" '<- change here to suite If n 0 Then .Column = a End With End Sub Private Sub cbokenterdata_Click() myDataEntry End Sub and for the module: Sub myDataEntry() Dim x, y, uf As Object, msg As String On Error Resume Next Set uf = userform1 If uf Is Nothing Then msg = "userform1 is not open" Err.Clear: Set uf = Nothing Set uf = userform2 If uf Is Nothing Then msg = msg & vbLf & "userform2 is not open" On Error GoTo 0 If Len(msg) Then MsgBox msg Exit Sub End If With userform1.combobox1 If .ListIndex = -1 Then Exit Sub x = .List(.ListIndex, 1) End With With userform2.combobox2 If .ListIndex = -1 Then Exit Sub y = .List(.ListIndex, 2) End With Worksheets("HLT51607").Cells(y, x).Value = userform2.combobox3.Value End Sub Sub start() userform1.Show False userform2.Show False End Sub I get the error in this line of code in the module: Worksheets("HLT51607").Cells(y, x).Value = userform2.combobox3.Value "ShaneDevenshire" wrote: Hi, sorry to take so long getting back, but I work full time and don't get a lot of time to look at complex problems. First, I would recommend that you keep the user on the same UserForm through out the process, Add all the controls to 1 UserForm. When they click the Apply button, see previous email, the dialog box should stay open and we need to do a search for the item and then change the appropriate field. Suppose the unique field is Product and is C2:C100, and you need to find "Oranges" then you code would look something like this: For Each cell in Range("C2:C100") If cell = Me.Product then cell.Offset(0,5) = Me.txtPrice end if Next cell -- Thanks, Shane Devenshire "Paul" wrote: Thank you so muuch for this very thorough answer. I have made up some combo boxes and just do not know what code to use. I am up to speed on the creation of the user forms and have named all of the ranges have also completed making up the user forms I have many categories that change all of the time, the one constant is the product id number and name. so instead getting them to fill in each category they are only asled for one at a time. the second userform would be where they enter the correction. When changes to categories come they are usually in order... so just say a price change came in, the user would just need to type in the product number or name and then be prompted to enter in this case the new price already selected by the first combox/listbox in the first userform.... perhaps to remind them on the user form a text box could say you are entering data for ... ( and the name that they selected in the first userform. thank you again for your help look forward to seeing what you say. "ShaneDevenshire" wrote: Hi, So let's say the user picks Product Name from the list of four types, then they choose Oranges from the second combo box - is this a unique item, meaning is there only one row with Oranges or many rows and if many do you want to change all rows with that name? In the third box they would enter Apples and click a button and change all Oranges to Apples? I am also concerned with what happens when they pick Price from the first drop down. The second drop down would List the name of all the Products, is that correct? Before you answer that here is a start 1. Create a user form and name and caption it 2. Add two combo boxes, name one cboCategory, the other cboItem 3. You have range names for your four columns, lets say they are Number, Name, Qty and Price. Create another column with those same four names. And name this range Category. 4. Select the first combo box and set its row source property to =Category 5. Select the second combo box and set its row source to =Name 6. Add two labels above the combo boxes and change their name property to lblCategory and lblItem. Change their caption properties to what ever you want to be displayed on the user form. 7. Add two Command Buttons. Name one cmdClose and the other cmdApply 8. Change there caption properties to read Close and Apply 9. Double-click the Close button and on the code sheet in the Close_Click subroutine add one line Unload Me Get back to me on the questions above and I will continue. -- Thanks, Shane Devenshire "Paul" wrote: Hello all, and thank you to those who have helped me in the past. This is a great forum. I am pulling my hair out. I have a worksheet with named ranges product number, product name qty price. I need code that will allow me to make bulk changes to data. Ideally I would like a combobox that will firsttly ask what variable needs to be changed... ie price. the second combo box then opens and askes the user to enter the product number or product name from a lthe named range, and then allows the user to update the selected variable in this case the new price. This new price is entered into the worksheet. It continues until the user decides to quit or select another variable. All help much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form ComboBox Items: Remember user entries? | Excel Programming | |||
Adding to excel user form combobox | Excel Programming | |||
Could not set the Text property of a ComboBox on User Form. | Excel Programming | |||
Could not set the Text property of a ComboBox on User Form. | Excel Programming | |||
User Form-error 1004 unless opened in sheet w/combobox data | Excel Programming |