looking up data and using a combobox user form

[Paul]

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.

 

[ShaneDevenshire]

Hi,

Are you trying to do this in the spreadsheet or on a VBA UserForm?

 

[Paul]

hello, With a user form

Hi,

Are you trying to do this in the spreadsheet or on a VBA UserForm?

 

[ShaneDevenshire]

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.

 

[Paul]

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]

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

 

[Paul]

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