List Box filled depending on value in another cell

[rob nobel]

I think you're getting somewhere Dave. I won't say me!!
This is what I have so far, but there is still a problem regarding selecting
the correct Pivot Table. (Please see my earlier post about my concerns.)
I also like your extra bit to check for the fact that more than 1 cell may
be selected, thanks!

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

Select Case UCase(Me.Cells(Target.Row, "F").Value)
Case Is = UCase(Me.Range("F16").Value)
Set pvtTable = Sheet15.PivotTables(1)
Case Is = UCase(Me.Range("F17").Value)
Set pvtTable = Sheet15.PivotTables(2)
Case Is = UCase(Me.Range("F18").Value)
Set pvtTable = Sheet15.PivotTables(3)
Case Is = UCase(Me.Range("F19").Value)
Set pvtTable = Sheet15.PivotTables(4)
Case Is = UCase(Me.Range("F20").Value)
Set pvtTable = Sheet15.PivotTables(5)
Case Is = UCase(Me.Range("F21").Value)
Set pvtTable = Sheet15.PivotTables(6)
Case Is = UCase(Me.Range("F22").Value)
Set pvtTable = Sheet15.PivotTables(7)
Case Is = UCase(Me.Range("F23").Value)
Set pvtTable = Sheet15.PivotTables(8)
End Select

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

ufSelectCode.Show

End Sub

 

[rob nobel]

I'm also having a bit of trouble with this line, Dave ....

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

As if the cell in column F is blank it doesn't exit the sub. If you could
explain what it does I may be able to try some things myself?? Or doesn't
this line do that?
Rob

 

[Dave Peterson]

Don't forget to add the:

if pvtTable is nothing then
'warnings...
else
ufSelectCode.ListBox1.List _
= pvtTable.RowFields(1).DataRange.Resize(, 2).Value
ufSelectCode.Show
end if

 

[Dave Peterson]

Since this is inside a Worksheet_SelectionChange event, it's looking at the cell
you just selected.

This line:
If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub
Says if you selected a cell that was not in L26:L1525 to get out of the
Sub--exit right away.

If you want to check to see if column F of the same row is empty:

if isempty(me.cells(target.row,"F")) then exit sub

But that just checks to see if the cell is empty. What happens if you have
something in that cell, but it doesn't correspond to one of the values in your
"select case" statements.

You may need to still add the:
if pvttable is nothing then
'don't show the form
else
'show the form
end if

Heck. Make that a should add. It'll stop you program from blowing up if
something changes that you don't expect. Better a warning message instead of a
macro failure.

 

[Dave Peterson]

If you know the names of the pivottables, the you should use them. (I would
anyway.)

I'm not sure how excel keeps track of the indexes--I don't think pivottables(1)
is always the first one added. In fact, in minor testing, it looks like it's in
reverse order. (But that was very minor testing!)

But you could always add the name of the pivottable if you create it in code.
(I'm sure you've seen that code in your recorded macros.)

But if the pivottables already exist, then just right click inside each and
choose Table Options.

At the top of that dialog is where you can check the name and even rename it.

I ran a little macro that selected the pivottable range and displayed the index
and name.

It may help you see what's going on.

Option Explicit
Sub testm()
Dim wks As Worksheet
Dim iCtr As Long
Dim pvttable As PivotTable

Set wks = ActiveSheet
For iCtr = 1 To wks.PivotTables.Count
Set pvttable = wks.PivotTables(iCtr)
pvttable.TableRange1.Select
MsgBox iCtr & "--" & pvttable.Name
Next iCtr
End Sub

And if you rely on the indices (I wouldn't!), remember that if you delete one,
then the rest will get renumbered.

I know you like sheet4 vs. worksheets("my Sheet 4"), too.

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

 

[rob nobel]

Now we're cookin', (nearly)!
I agree with the indexes numbering. I thought it was a problem with the
code, as I noticed that the first table =PivotTable1 and the second =
PivotTable8, 3=7, 4=6 and so on.
Weird but true! (Your little macro was helpful too to prove the point.)

Anyway, naming them simply as ...
"Set pvttable = Sheet15.PivotTables("PivotTable1")" and so on
has done the trick.

I've also include the bit to check if a Pivot table is present or not,
though I don't think that will be necessary, as there will always be a table
for each name (8 in all) that will appear in column F. (the fields may be
blank, but the table will still be present). I'll need to test what happens
if the fields are blank.

The only bit I'm having trouble with is....
If IsEmpty(Me.Cells(Target.Row, "F")) Then Exit Sub

It doesn't exit if the corresponding cell in column F is empty. I reckon
it's because there is a formula in those cells. Is there a way round this?
If not, I can delete the formula as it's not essential. ( Just a help to
fill the next row in column F with the value that preceded it in the
previous row of column F.)

Thankyou very much, Dave for all the help, explanations, time and effort!!

Rob

 

[rob nobel]

A quick note:
The pivot field for any table can never be empty (the way I have them
structured).
Rob

 

[Dave Peterson]

If a cell has a formula, it's not empty. It may look blank, but it's not empty.

you could use:

if me.cells(target.row,"F").value = "" then exit sub
or if you may have spaces:
if trim(me.cells(target.row,"F").value) = "" then exit sub

And it doesn't hurt to add that extra check. It might save a few phone calls!

 

[Dave Peterson]

I don't know what this means, but I bet you're getting happier!

<vbg>

 

[rob nobel]

Sorry about this.....seems to be a habit of mine, but I think I found the
cure.

If Me.Cells(Target.Row, "F") = "" Then Exit Sub

Does it for me instead of

If IsEmpty(Me.Cells(Target.Row, "F")) Then Exit Sub

Now, that's the first thing that I tried that works, so please don't lower
my little bit of self esteem I have left and tell me that's not OK

Rob

Now we're cookin', (nearly)!
I agree with the indexes numbering. I thought it was a problem with the
code, as I noticed that the first table =PivotTable1 and the second =
PivotTable8, 3=7, 4=6 and so on.
Weird but true! (Your little macro was helpful too to prove the point.)

Anyway, naming them simply as ...
"Set pvttable = Sheet15.PivotTables("PivotTable1")" and so on
has done the trick.

I've also include the bit to check if a Pivot table is present or not,
though I don't think that will be necessary, as there will always be a table
for each name (8 in all) that will appear in column F. (the fields may be
blank, but the table will still be present). I'll need to test what happens
if the fields are blank.

The only bit I'm having trouble with is....
If IsEmpty(Me.Cells(Target.Row, "F")) Then Exit Sub

It doesn't exit if the corresponding cell in column F is empty. I reckon
it's because there is a formula in those cells. Is there a way round this?
If not, I can delete the formula as it's not essential. ( Just a help to
fill the next row in column F with the value that preceded it in the
previous row of column F.)

Thankyou very much, Dave for all the help, explanations, time and effort!!

Rob

If you know the names of the pivottables, the you should use them. (I would
anyway.)

I'm not sure how excel keeps track of the indexes--I don't think pivottables(1)
is always the first one added. In fact, in minor testing, it looks like it's in
reverse order. (But that was very minor testing!)

But you could always add the name of the pivottable if you create it in code.
(I'm sure you've seen that code in your recorded macros.)

But if the pivottables already exist, then just right click inside each and
choose Table Options.

At the top of that dialog is where you can check the name and even

rename

it.

I ran a little macro that selected the pivottable range and displayed

the

index
and name.

It may help you see what's going on.

Option Explicit
Sub testm()
Dim wks As Worksheet
Dim iCtr As Long
Dim pvttable As PivotTable

Set wks = ActiveSheet
For iCtr = 1 To wks.PivotTables.Count
Set pvttable = wks.PivotTables(iCtr)
pvttable.TableRange1.Select
MsgBox iCtr & "--" & pvttable.Name
Next iCtr
End Sub

And if you rely on the indices (I wouldn't!), remember that if you

delete

one,
then the rest will get renumbered.

I know you like sheet4 vs. worksheets("my Sheet 4"), too.

"GEN,

MIN"

have

a

the

end:

build

a

Then

Exit

selection?

(I

column

F.

RowFields(1)

from

RowFields(1)

 

[rob nobel]

Darn....your time (reply on the answer to the problem), is 3 mins before
mine. So who holds the rights to the answer

and...."it doesn't hurt to add that extra check. It might save a few phone
calls!"...all true!

Thanks!

Rob