Need help with using a prompt to re-assign categoies

[SeanDamnit]

Hello MrExcel,

I'm having difficulty figuring out where to begin with this project.

I have an inventory list that will usually be ~10k line items, each grouped in 4-6 unique categories, with about 80 unique items. I will occasionally need to re-categorize items on this list. For the sake of a simple example, let's say it looks like this:

Excel Workbook
	A	B
1	Item	Category
2	Cherry	2
3	Pear	1
4	Cherry	2
5	Pineapple	2
6	Apple	3
7	Banana	1
8	Banana	1
9	Kiwi	1
10	Kiwi	1
11	Cherry	2
12	Banana	1
13	Cherry	2
14	Apple	3
15	Apple	3
16	Cherry	2
17	Apple	3
18	Cherry	2
Sheet2

What I'd like the process to do is:

For each unique category, a prompt will pop up listing each unique item in that category along with a drop down menu next to it giving the user the option to re-categorize that item. All items are set to their current category by default. Once complete the category would be updated in the data, and I can continue with the remaining requirements for my process.

Normally for projects like this I'm able to find a tutorial or something to get me started out there...but I guess my Google skills just aren't on game today. Can anyone point me in the right direction?

I appreciate any help! Thanks!

 

[Andrew Poulsom]

Without building a complete project for you, here is a starter. Say you want to change the category of Apple from 3 to 4. AutoFilter your data for column A = Apple. Select the visible cells in column B, go to the Formula Bar, type 4 and press Ctrl+Enter. Then clear the AutoFilter.

Here is the equivalent VBA code:

Sub Test()
    Dim Rng As Range
    With Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Apple"
        Set Rng = .Parent.AutoFilter.Range
        Set Rng = Rng.Offset(1).Resize(.Rows.Count - 1)
        Rng.Columns(2).SpecialCells(xlCellTypeVisible).Value = 4
        .Parent.AutoFilterMode = False
    End With
End Sub

 

[SeanDamnit]

Without building a complete project for you, here is a starter. Say you want to change the category of Apple from 3 to 4. AutoFilter your data for column A = Apple. Select the visible cells in column B, go to the Formula Bar, type 4 and press Ctrl+Enter. Then clear the AutoFilter.

Here is the equivalent VBA code:

Sub Test()
    Dim Rng As Range
    With Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Apple"
        Set Rng = .Parent.AutoFilter.Range
        Set Rng = Rng.Offset(1).Resize(.Rows.Count - 1)
        Rng.Columns(2).SpecialCells(xlCellTypeVisible).Value = 4
        .Parent.AutoFilterMode = False
    End With
End Sub

The prompt is the part that's giving me the most trouble - any advice, tutorials, etc for that?

 

[Andrew Poulsom]

What sort of prompt do you envisage? Here is some code that populates a ListBox with unique items:

Spreadsheet Page Excel Tips: Filling A Listbox With Unique Items

 

[SeanDamnit]

What sort of prompt do you envisage? Here is some code that populates a ListBox with unique items:

Spreadsheet Page Excel Tips: Filling A Listbox With Unique Items

Awesome, wasn't familiar with collections or userforms, so this is a great start. What I envision is this...for each unique category, there will be a userprompt that looks like this:

Labels on the left are each unique item in that category - drop down list on the right are a list of the categories, filled in with the initial category by default. The user has the option to change the category, which will then be updated in the data.

Pretty sure this is within my capability once I learn a little more on how to build userforms. Reading up now. I'll come back with specific questions if I have them - appreciate the help!

 

[Andrew Poulsom]

It would be better to have one ComboBox for the Items and one ComboBox for the Categories with a CommandButton to change.