List Box filled depending on value in another cell

[rob nobel]

I have the code (set out below the question), that fills a ListBox to enter
the item to the active cell.
The line "Set AllCells = Sheet11.Range("B5:B54")" refers to the items but I
would like this procedure to pull the items from 8 different ranges
depending on what the value is in column F (on the same row where the cell
is active that prompts this procedure).
In other words, if cell I38 is activated, this procedure should run
automatically and determine what the value is in F38, to in turn determine
which range to select to fill the ListBox.
So, if F38 = "Gen", the range is Sheet11.Range("B5:B54").
If F38 = "Min", the range is Sheet11.Range("G5:G54").
If F38 = "Bld", the range is Sheet11.Range("L5:L54"),
and so on for the other 5 options.

Can someone please help me with this? (Also, how to activate the procedure
when any of the cells I26 to I1525 is activated.)

Rob

Sub mSelectCode()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' Where the items are.
Set AllCells = Sheet11.Range("B5:B54")

Sub mFillCodeList()
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

' Update the labels on UserForm1
With ufSelectCode
.Label1.Caption = "Total Items: " & AllCells.Count
.Label2.Caption = "Unique Items: " & NoDupes.Count
End With

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
ufSelectCode.ListBox1.AddItem Item
Next Item

' Show the ufSelectCode
ufSelectCode.Show
End Sub

 

[rob nobel]

Just an addendum to the previous post:
I really would like a 2 column list box. Which means that the first range
("B5:B54") needs to be sorted but then its corresponding "mate" in C5:C54 to
be shown in the 2nd column of the list box.
Can this be done without too much trouble? It all seems too hard to
understand how the sort procedure can keep the adjacent column intact with
it.
Thanks,
Rob

 

[Dave Peterson]

Any chance you can sort the original ranges and then just pick up the values
from there ignoring the duplicated values?

If yes, how about:

Option Explicit
Sub mSelectCode()

Dim AllCells As Range
Dim iCtr As Long

Set AllCells = Nothing
With ActiveSheet
Select Case LCase(.Cells(ActiveCell.Row, "F").Value)
Case Is = "gen": Set AllCells = Sheet11.Range("B5:B54")
Case Is = "min": Set AllCells = Sheet11.Range("G5:G54")
Case Is = "bld": Set AllCells = Sheet11.Range("L5:L54")
Case Else
MsgBox "error!!!!!"
Exit Sub
End Select
End With

With AllCells.Resize(, 2) 'two columns
.Sort key1:=.Cells(1, 1), order1:=xlAscending, header:=xlNo
End With

' Add the sorted, non-duplicated items to a ListBox
For iCtr = 1 To AllCells.Cells.Count
With ufSelectCode.ListBox1
If iCtr = 1 Then
.AddItem CStr(AllCells(iCtr).Value)
.List(.ListCount - 1, 2) = AllCells(iCtr).Offset(0, 1).Value
Else
If AllCells(iCtr).Value = AllCells(iCtr - 1).Value Then
'do nothing
Else
.AddItem CStr(AllCells(iCtr).Value)
.List(.ListCount - 1, 2) _
= AllCells(iCtr).Offset(0, 1).Value
End If
End If
End With
Next iCtr

' Update the labels on UserForm1
With ufSelectCode
.Label1.Caption = "Total Items: " & AllCells.Cells.Count
.Label2.Caption = "Unique Items: " & ufSelectCode.ListBox1.ListCount
End With


' Show the ufSelectCode
ufSelectCode.Show
End Sub

 

[rob nobel]

Getting a bit over the top of my knowledge here Dave, but thanks.
At the moment I'm running a sheet module macro that calls your procedure (or
tries to) but comes up with "Expected Variable or procedure, not module".
In the sheet macro I have
Private Sub CodeSelect_Click()
Call mSelectCode
End Sub

(I placed your version of the procedure in its own general module.)

Also, how do I get the procedure to run when any cell in a specific column
is activated (without needing to click the CodeSelect button), say column G
Rob

 

[Dave Peterson]

It sounds like you may have named the procedure the same as the module.

Hit ctrl-R to see the project explorer and select that module.
Then hit F4 to see its properties and rename it.

Mod_mSelectCode (maybe???)

It's not a good idea to have procedures/functions/modules/and even names (like
range names and workbook/worksheet names) share the same name.

(I know it confuses me and sometimes it can confuse xl.)

 

[rob nobel]

Spot on Dave!
Thanks for all of this but I think it's all getting to look like a monster
which I can't control (lack of VBA smarts on my part!!).
In view of this I'm trying to simplify it all, particularly in forgetting
about the sort process.
If I tell you what I'm actually trying to do maybe you could advise me the
best approach ?

In column F26:F1525 the user selects, via data val, from 8 account names.
In column L26:L1525 I want a 2 column list box appear (when the cell in
column L is selected) appropriate to the selection made in col F. (Each
account will have its own peculiar items to select from.)
The lists to select from are pivot tables (one for each account). I thought
this would make it easy as the pivot tables can automatically sort the data
which could then be used to populate the list boxes. But this is where the
problem lies as the pivot tables grow or shrink depending on the number of
items.
So.....

Q1. Is there a way to populate the list box (2 columns) by simply using the
pivot name somehow? (All the pivots will be on one other sheet.)
(If I populate the listbox by setting the range that the pivots may grow to,
I get a lot of blanks at the end of the list.)
Q2. How is the list box activated upon activating any of the cells that are
in L26:L1525 so I don't have to click a button?

 

[Dave Peterson]

There are lots of "subdivisions" of pivottables.

One of them is the RowField. This may give you an idea. (not sure ho



Dim PvtField As PivotField
Dim pvtTable As PivotTable
Dim myVar As Variant

Set pvtTable = Worksheets("Sheet3").PivotTables(1)
Set PvtField = pvtTable.RowFields(1)
myVar = PvtField.DataRange.Resize(, 2).Value

But I'm not too sure how your pivottable is laid out.

Spot on Dave!
Thanks for all of this but I think it's all getting to look like a monster
which I can't control (lack of VBA smarts on my part!!).
In view of this I'm trying to simplify it all, particularly in forgetting
about the sort process.
If I tell you what I'm actually trying to do maybe you could advise me the
best approach ?

In column F26:F1525 the user selects, via data val, from 8 account names.
In column L26:L1525 I want a 2 column list box appear (when the cell in
column L is selected) appropriate to the selection made in col F. (Each
account will have its own peculiar items to select from.)
The lists to select from are pivot tables (one for each account). I thought
this would make it easy as the pivot tables can automatically sort the data
which could then be used to populate the list boxes. But this is where the
problem lies as the pivot tables grow or shrink depending on the number of
items.
So.....

Q1. Is there a way to populate the list box (2 columns) by simply using the
pivot name somehow? (All the pivots will be on one other sheet.)
(If I populate the listbox by setting the range that the pivots may grow to,
I get a lot of blanks at the end of the list.)
Q2. How is the list box activated upon activating any of the cells that are
in L26:L1525 so I don't have to click a button?

 

[rob nobel]

Thanks Dave but cant seem to fit your suggested code into the procedure
succesfully.
What I have at the moment that works but has a lot of blank cells at the end
of the list is this.....

Sub SelectCode()
Application.ScreenUpdating = False

'Activate sheet to populate list box
Sheet15.Activate
ufSelectCode.ListBox1.ColumnCount = 2
ufSelectCode.ListBox1.RowSource = ("B5:C64")

With ufSelectCode
.Label1.Caption = "Select item from list. Number of items to choose
from = " & Range("A2")
End With
Sheet4.Activate
Application.ScreenUpdating = True
ufSelectCode.Show
End Sub

What you gave which I've amended to suit is this....

Dim PvtField As PivotField
Dim pvtTable As PivotTable
Dim myVar As Variant

Set pvtTable = Sheet15.PivotTables(1)
Set PvtField = pvtTable.RowFields(1)
myVar = PvtField.DataRange.Resize(, 2).Value

Could you also advise how to do the following...
Q2. How is the list box activated upon activating any of the cells that are
in L26:L1525 of sheet 4 so I don't have to click a button to run the
procedure?

Rob

 

[rob nobel]

Just a quick note re how my pivot table is set out.
There are 2 row fields.
Those row fields are what populates the List box.
The 2nd (or right hand side) field, which is the field that populates the
2nd columna is the one that should be copied to the cell from the selection
in the list box.
Hope that helps.
Rob

 

[Dave Peterson]

Once you've got the values into myVar, you can use that for the listbox's list.

UserForm1.ListBox1.List = myVar


I'd rather have to click a button. (maybe in row 1 (with frozen panes so that
it's always available, or on a floating toolbar). I could select a cell and
decide that it's not the one I want.

(Sometimes being too helpful makes it difficult for you the developer and
impossible for the user.)

but maybe you could use a worksheet event:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub
UserForm1.Show
End Sub

right click on the sheet4's tab and select view code.

Paste it in and try it out. (then delete it and use a button! <bg>)

Thanks Dave but cant seem to fit your suggested code into the procedure
succesfully.
What I have at the moment that works but has a lot of blank cells at the end
of the list is this.....

Sub SelectCode()
Application.ScreenUpdating = False

'Activate sheet to populate list box
Sheet15.Activate
ufSelectCode.ListBox1.ColumnCount = 2
ufSelectCode.ListBox1.RowSource = ("B5:C64")

With ufSelectCode
.Label1.Caption = "Select item from list. Number of items to choose
from = " & Range("A2")
End With
Sheet4.Activate
Application.ScreenUpdating = True
ufSelectCode.Show
End Sub

What you gave which I've amended to suit is this....

Dim PvtField As PivotField
Dim pvtTable As PivotTable
Dim myVar As Variant

Set pvtTable = Sheet15.PivotTables(1)
Set PvtField = pvtTable.RowFields(1)
myVar = PvtField.DataRange.Resize(, 2).Value

Could you also advise how to do the following...
Q2. How is the list box activated upon activating any of the cells that are
in L26:L1525 of sheet 4 so I don't have to click a button to run the
procedure?

Rob

 

[rob nobel]

Excellent Dave. Just needed the ".List" bit to do the trick and it works
great. Thank you, thank you, thank you.
I'll give some thought to the rest. I really need it to happen
automatically to prevent the user doing anything else but choose from the
lists.
Rob

 

[Dave Peterson]

Whew! <vbg>

<<snipped>>

 

[rob nobel]

As I said, it works great, but only for the one table and I'm stumped. What
I have so far is this....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub

Dim PvtField As PivotField
Dim pvtTable As PivotTable
Dim myFieldVar As Variant
Set pvtTable = Sheet15.PivotTables(1)
Set PvtField = pvtTable.RowFields(1)

With Sheet4
Select Case LCase(.Cells(ActiveCell.Row, "F").Value)
Case Is = Range("F16"): Set myFieldVar = Sheet15.PivotTables(1)
End Select
End With

myFieldVar = PvtField.DataRange.Resize(, 2).Value
ufSelectCode.ListBox1.List = myFieldVar
ufSelectCode.Show
End Sub

I've tried unsuccesfully to alter this so that the correct list will be
shown in the userform depending on what the value is in column F.
There are 8 values to choose from, which are stored in F16:F23
What I need to happen is....
If the cell in column F = Range ("F16") then the RowFields(1) from
PivotTable (1) should populate the list.
If the cell in column F = Range ("F17") then the RowFields(1) from
PivotTable (2) should populate the list,
and so on for 8 tables.

Can you help me set this up please.

Rob

 

[Dave Peterson]

I'm not sure if I have it straight and there's questions at the end:

But maybe something like this (untested--I was to lazy to build a form and a
workbook and pivottables!):


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim pvtTable As PivotTable

'what happens if they select more than one cell?
If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub

With Sheet4
Select Case LCase(.Cells(Target.Row, "F").Value)
Case Is = .Range("F16").Value
Set pvtTable = Sheet15.PivotTables(1)
Case Is = .Range("F16").Value
Set pvtTable = Sheet15.PivotTables(2)
End Select
End With

ufSelectCode.ListBox1.List _
= pvtTable.RowFields(1).DataRange.Resize(, 2).Value

ufSelectCode.Show

End Sub

Note that I dropped the variables and just assigned the .list to what I wanted
directly.

Now my questions.

You used "with Sheet4". Is sheet4 the owner of this code--where you're doing
the selection?

And are you checking F(row) in the same sheet as the selection? (I got confused
at what you really wanted.)

If it's all on the same sheet, then I think I'd write it this way (mostly
personal preference):

Select Case LCase(me.Cells(Target.Row, "F").Value)
Case Is = me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(1)
Case Is = me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(2)
End Select

The me. stuff refers to the object (worksheet in this case) owning the code.

 

[rob nobel]

Don't know about the lazy bit. All I know is that you've helped me
immensely!

The code, as you supplied, gives an error for this section....
ufSelectCode.ListBox1.List _
= pvtTable.RowFields(1).DataRange.Resize(, 2).Value
Error box says,
"Object variable or With block variable not set" (Error 91)

As you've deliberately not used variables, how is this fixed? (Setting
variables is still something I don't understand.)
Also, by not declaring variables, does that slow down calculation (Which I'd
like to minimise as much as pos)?

In answer to some of the questions....
Sheet4 is the owner of this code and Column F is on the same sheet.
Basically, the user does everything on this one sheet, (other than initual
setting up of account names and certain details). That is, all general
inputs are on sheet 4.
I've therefore used your suggestion for the following code...
Select Case LCase(me.Cells(Target.Row, "F").Value)
Case Is = me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(1)
Case Is = me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(2)
End Select

**Except I altered the 2nd ("F16") to ("F17")
Hope this helps you understand what I'm trying to achieve.

Rob

 

[Dave Peterson]

My first guess is that it's not the change I made (ahem!), I think it didn't
match one of your "cases"

This portion only did a couple of checks:

Select Case LCase(me.Cells(Target.Row, "F").Value)
Case Is = Me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(1)
Case Is = Me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(2)
End Select

If it didn't match one of those values, then pvtTable was never set.

So to avoid this, you can check to see if it got set:

If pvtTable Is Nothing Then
Beep 'error messages???
Else
ufSelectCode.ListBox1.List _
= pvtTable.RowFields(1).DataRange.Resize(, 2).Value

ufSelectCode.Show
End If

===
and you use Set when you're dealing with object variables.

For instance:

dim rng as range
set rng = sheet4.range("a1")

a range has a bunch of properties that travel with it. It can have a border, a
numberformat, a value, a value2, a parent (the worksheet that it lives on), all
that stuff.

when you use Set, you're saying that some object (has more stuff than a simple
variable) should inherit all those properties.

a simple variable like:
dim myLongNumber as Long
can hold one thing--a big ole integer. No borders, no parent, no colors. It's
plain and simple.


The only reason I tossed the variables was because we weren't really using
them. We only used them to get to the two columns. (And the only reason I used
them initially was I didn't quite know how you were using them--IIRC.)

======
And sorry about the F16/F17 stuff.
I just copied and pasted.
I just copied and pasted.
I just copied and pasted.
I just copied and pasted.

(hehe)

 

[Dave Peterson]

(and I copied the duplicated F16 version to start with!)

Sorry!

 

[rob nobel]

Dave, I pasted in the beep bit and it beeps every time!
When I changed the Select Case bit as follows ......

Select Case (Me.Cells(Target.Row, "F").Value)
Case Is = "GEN"
Set pvtTable = Sheet15.PivotTables(1)
Case Is = "MIN"
Set pvtTable = Sheet15.PivotTables(2)
End Select

the correct Pivot table is selected ( 1) when I select the cell in
Range("L26:L1525") that the Cell value in Column F is "GEN"
But when I select a cell where the cell value is "MIN", the Pivot table that
populates the List box is No. 8 ???

So it appears I have 2 problems.
1. the correct pivot table is not selected and,
2. the procedure is not responding to the "Case Is = Me.Range("F16").Value"
bit.
(I need the "Range("F16").Value" bit to work because the values "GEN, MIN"
etc., are not necessarily permenant values. )


What now?
Rob

 

[Dave Peterson]

Is it possible that you have mixed case in the cells?

select case ucase(me.cells(target.row,"F").value))
Case Is = ucase(Me.Range("F16").Value)

?????

Another alternative if it is the text case, add "option compare text" to the top
of your module.

Then gen=Gen=GEn=GeN=GEN=...

And no leading/trailing spaces???

If yes, add trim(ucase())

Dave, I pasted in the beep bit and it beeps every time!
When I changed the Select Case bit as follows ......

Select Case (Me.Cells(Target.Row, "F").Value)
Case Is = "GEN"
Set pvtTable = Sheet15.PivotTables(1)
Case Is = "MIN"
Set pvtTable = Sheet15.PivotTables(2)
End Select

the correct Pivot table is selected ( 1) when I select the cell in
Range("L26:L1525") that the Cell value in Column F is "GEN"
But when I select a cell where the cell value is "MIN", the Pivot table that
populates the List box is No. 8 ???

So it appears I have 2 problems.
1. the correct pivot table is not selected and,
2. the procedure is not responding to the "Case Is = Me.Range("F16").Value"
bit.
(I need the "Range("F16").Value" bit to work because the values "GEN, MIN"
etc., are not necessarily permenant values. )

What now?
Rob

 

[rob nobel]

Just a thought which may help. In regard to the Pivot Tables, what makes
the table number 1,2 or 3, etc? Is it designated by the PivotTable > Table
Options name or some other property factor. The names for all the pivot
tables are shown as "PivotTable1", "PivotTable2", and so on. Which makes me
think that the Sheet15.PivotTables(1) bit may not be directing it to the
correct table??? For Instance, what would happen to the code if the "Pivot
table1" was changed to "FirstAccount"? Which PivotTable would the
Sheet15.PivotTables(1) refer to and how would the (1) bit work in this case?
Rob

Dave, I pasted in the beep bit and it beeps every time!
When I changed the Select Case bit as follows ......

Select Case (Me.Cells(Target.Row, "F").Value)
Case Is = "GEN"
Set pvtTable = Sheet15.PivotTables(1)
Case Is = "MIN"
Set pvtTable = Sheet15.PivotTables(2)
End Select

the correct Pivot table is selected ( 1) when I select the cell in
Range("L26:L1525") that the Cell value in Column F is "GEN"
But when I select a cell where the cell value is "MIN", the Pivot table that
populates the List box is No. 8 ???

So it appears I have 2 problems.
1. the correct pivot table is not selected and,
2. the procedure is not responding to the "Case Is = Me.Range("F16").Value"
bit.
(I need the "Range("F16").Value" bit to work because the values "GEN, MIN"
etc., are not necessarily permenant values. )


What now?
Rob

My first guess is that it's not the change I made (ahem!), I think it didn't
match one of your "cases"

This portion only did a couple of checks:

Select Case LCase(me.Cells(Target.Row, "F").Value)
Case Is = Me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(1)
Case Is = Me.Range("F16").Value
Set pvtTable = Sheet15.PivotTables(2)
End Select

If it didn't match one of those values, then pvtTable was never set.

So to avoid this, you can check to see if it got set:

If pvtTable Is Nothing Then
Beep 'error messages???
Else
ufSelectCode.ListBox1.List _
= pvtTable.RowFields(1).DataRange.Resize(, 2).Value

ufSelectCode.Show
End If

===
and you use Set when you're dealing with object variables.

For instance:

dim rng as range
set rng = sheet4.range("a1")

a range has a bunch of properties that travel with it. It can have a border, a
numberformat, a value, a value2, a parent (the worksheet that it lives on), all
that stuff.

when you use Set, you're saying that some object (has more stuff than a simple
variable) should inherit all those properties.

a simple variable like:
dim myLongNumber as Long
can hold one thing--a big ole integer. No borders, no parent, no

colors.

It's
plain and simple.


The only reason I tossed the variables was because we weren't really using
them. We only used them to get to the two columns. (And the only

reason
I used

them initially was I didn't quite know how you were using them--IIRC.)

======
And sorry about the F16/F17 stuff.
I just copied and pasted.
I just copied and pasted.
I just copied and pasted.
I just copied and pasted.

(hehe)

(Which

I'd

form

and

what

I

will