[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

URGENT! How to read value in pivot table

stoppal

12/12/2006 4:08:00 PM

Hi,

I need some help. trying to use VB to read the value of a pivot table
so I can conditional format the cells. I know how to conditional
format using VB on normal cells, but I don't know how for pivot tables.
How can I reference values from a pivots table?

thank you
11 Answers

stoppal

12/12/2006 9:50:00 PM

0

PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
table?


stoppal wrote:
> Hi,
>
> I need some help. trying to use VB to read the value of a pivot table
> so I can conditional format the cells. I know how to conditional
> format using VB on normal cells, but I don't know how for pivot tables.
> How can I reference values from a pivots table?
>
> thank you

stoppal

12/13/2006 4:32:00 PM

0

There must be someone that know's how to do this? I keep getting a
type mismatch, which is why I need to figure out how to reference the
pivot table cell not the normal cell.

PLEASE HELP!!!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then

Select Case Target
Case "1/1/2001" To "12/31/2001" '2001

icolor = 6

Case 37257 To 37621 '2002

icolor = 12

Case 37622 To 37986 '2003

icolor = 7

Case 37987 To 38352 '2004

icolor = 53

Case 38353 To 38717 '2005

icolor = 15

Case 38718 To 39082 '2006

icolor = 42

Case 39083 To 39447 '2007

icolor = 45

Case 39448 To 39813 '2008

icolor = 47

Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub





stoppal wrote:
> PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> table?
>
>
> stoppal wrote:
> > Hi,
> >
> > I need some help. trying to use VB to read the value of a pivot table
> > so I can conditional format the cells. I know how to conditional
> > format using VB on normal cells, but I don't know how for pivot tables.
> > How can I reference values from a pivots table?
> >
> > thank you

stoppal

12/13/2006 8:04:00 PM

0

Is no one smart enough to know this?



stoppal wrote:
> There must be someone that know's how to do this? I keep getting a
> type mismatch, which is why I need to figure out how to reference the
> pivot table cell not the normal cell.
>
> PLEASE HELP!!!!!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>
> If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then
>
> Select Case Target
> Case "1/1/2001" To "12/31/2001" '2001
>
> icolor = 6
>
> Case 37257 To 37621 '2002
>
> icolor = 12
>
> Case 37622 To 37986 '2003
>
> icolor = 7
>
> Case 37987 To 38352 '2004
>
> icolor = 53
>
> Case 38353 To 38717 '2005
>
> icolor = 15
>
> Case 38718 To 39082 '2006
>
> icolor = 42
>
> Case 39083 To 39447 '2007
>
> icolor = 45
>
> Case 39448 To 39813 '2008
>
> icolor = 47
>
> Case Else
> 'Whatever
> End Select
>
> Target.Interior.ColorIndex = icolor
> End If
>
> End Sub
>
>
>
>
>
> stoppal wrote:
> > PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> > table?
> >
> >
> > stoppal wrote:
> > > Hi,
> > >
> > > I need some help. trying to use VB to read the value of a pivot table
> > > so I can conditional format the cells. I know how to conditional
> > > format using VB on normal cells, but I don't know how for pivot tables.
> > > How can I reference values from a pivots table?
> > >
> > > thank you

stoppal

12/14/2006 6:12:00 PM

0

I guess no one is smart out there or up to a challenge. This should be
rather simple. I'm surprised no one is has the brain power to answer
it.




stoppal wrote:
> Is no one smart enough to know this?
>
>
>
> stoppal wrote:
> > There must be someone that know's how to do this? I keep getting a
> > type mismatch, which is why I need to figure out how to reference the
> > pivot table cell not the normal cell.
> >
> > PLEASE HELP!!!!!
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim icolor As Integer
> >
> > If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then
> >
> > Select Case Target
> > Case "1/1/2001" To "12/31/2001" '2001
> >
> > icolor = 6
> >
> > Case 37257 To 37621 '2002
> >
> > icolor = 12
> >
> > Case 37622 To 37986 '2003
> >
> > icolor = 7
> >
> > Case 37987 To 38352 '2004
> >
> > icolor = 53
> >
> > Case 38353 To 38717 '2005
> >
> > icolor = 15
> >
> > Case 38718 To 39082 '2006
> >
> > icolor = 42
> >
> > Case 39083 To 39447 '2007
> >
> > icolor = 45
> >
> > Case 39448 To 39813 '2008
> >
> > icolor = 47
> >
> > Case Else
> > 'Whatever
> > End Select
> >
> > Target.Interior.ColorIndex = icolor
> > End If
> >
> > End Sub
> >
> >
> >
> >
> >
> > stoppal wrote:
> > > PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> > > table?
> > >
> > >
> > > stoppal wrote:
> > > > Hi,
> > > >
> > > > I need some help. trying to use VB to read the value of a pivot table
> > > > so I can conditional format the cells. I know how to conditional
> > > > format using VB on normal cells, but I don't know how for pivot tables.
> > > > How can I reference values from a pivots table?
> > > >
> > > > thank you

theSquirrel

12/14/2006 6:17:00 PM

0

not sure i like the browbeating, and I have no knowledge of pivot
tables, but where are you getting your error? You state that its a
type mismatch, where is the mismatch?

theSquirrel

stoppal wrote:
> I guess no one is smart out there or up to a challenge. This should be
> rather simple. I'm surprised no one is has the brain power to answer
> it.
>
>
>
>
> stoppal wrote:
> > Is no one smart enough to know this?
> >
> >
> >
> > stoppal wrote:
> > > There must be someone that know's how to do this? I keep getting a
> > > type mismatch, which is why I need to figure out how to reference the
> > > pivot table cell not the normal cell.
> > >
> > > PLEASE HELP!!!!!
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim icolor As Integer
> > >
> > > If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then
> > >
> > > Select Case Target
> > > Case "1/1/2001" To "12/31/2001" '2001
> > >
> > > icolor = 6
> > >
> > > Case 37257 To 37621 '2002
> > >
> > > icolor = 12
> > >
> > > Case 37622 To 37986 '2003
> > >
> > > icolor = 7
> > >
> > > Case 37987 To 38352 '2004
> > >
> > > icolor = 53
> > >
> > > Case 38353 To 38717 '2005
> > >
> > > icolor = 15
> > >
> > > Case 38718 To 39082 '2006
> > >
> > > icolor = 42
> > >
> > > Case 39083 To 39447 '2007
> > >
> > > icolor = 45
> > >
> > > Case 39448 To 39813 '2008
> > >
> > > icolor = 47
> > >
> > > Case Else
> > > 'Whatever
> > > End Select
> > >
> > > Target.Interior.ColorIndex = icolor
> > > End If
> > >
> > > End Sub
> > >
> > >
> > >
> > >
> > >
> > > stoppal wrote:
> > > > PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> > > > table?
> > > >
> > > >
> > > > stoppal wrote:
> > > > > Hi,
> > > > >
> > > > > I need some help. trying to use VB to read the value of a pivot table
> > > > > so I can conditional format the cells. I know how to conditional
> > > > > format using VB on normal cells, but I don't know how for pivot tables.
> > > > > How can I reference values from a pivots table?
> > > > >
> > > > > thank you

stoppal

12/15/2006 8:00:00 PM

0

the mismatch seems to occur on these lines

Select Case Target
Case "1/1/2001" To "12/31/2001" '2001

But only when I reference a cell populated by a pivot table. If the
cell is normal (meaning no pivot table populated it) then all works. I
think there is a way to reference a cell within a pivot table, but I
don't know how.

thanks for your response. I hope someone can help. I'm at the end of
my wits!

thanks



thesquirrel@gmail.com wrote:
> not sure i like the browbeating, and I have no knowledge of pivot
> tables, but where are you getting your error? You state that its a
> type mismatch, where is the mismatch?
>
> theSquirrel
>
> stoppal wrote:
> > I guess no one is smart out there or up to a challenge. This should be
> > rather simple. I'm surprised no one is has the brain power to answer
> > it.
> >
> >
> >
> >
> > stoppal wrote:
> > > Is no one smart enough to know this?
> > >
> > >
> > >
> > > stoppal wrote:
> > > > There must be someone that know's how to do this? I keep getting a
> > > > type mismatch, which is why I need to figure out how to reference the
> > > > pivot table cell not the normal cell.
> > > >
> > > > PLEASE HELP!!!!!
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > Dim icolor As Integer
> > > >
> > > > If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then
> > > >
> > > > Select Case Target
> > > > Case "1/1/2001" To "12/31/2001" '2001
> > > >
> > > > icolor = 6
> > > >
> > > > Case 37257 To 37621 '2002
> > > >
> > > > icolor = 12
> > > >
> > > > Case 37622 To 37986 '2003
> > > >
> > > > icolor = 7
> > > >
> > > > Case 37987 To 38352 '2004
> > > >
> > > > icolor = 53
> > > >
> > > > Case 38353 To 38717 '2005
> > > >
> > > > icolor = 15
> > > >
> > > > Case 38718 To 39082 '2006
> > > >
> > > > icolor = 42
> > > >
> > > > Case 39083 To 39447 '2007
> > > >
> > > > icolor = 45
> > > >
> > > > Case 39448 To 39813 '2008
> > > >
> > > > icolor = 47
> > > >
> > > > Case Else
> > > > 'Whatever
> > > > End Select
> > > >
> > > > Target.Interior.ColorIndex = icolor
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > stoppal wrote:
> > > > > PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> > > > > table?
> > > > >
> > > > >
> > > > > stoppal wrote:
> > > > > > Hi,
> > > > > >
> > > > > > I need some help. trying to use VB to read the value of a pivot table
> > > > > > so I can conditional format the cells. I know how to conditional
> > > > > > format using VB on normal cells, but I don't know how for pivot tables.
> > > > > > How can I reference values from a pivots table?
> > > > > >
> > > > > > thank you

theSquirrel

12/15/2006 8:05:00 PM

0

I don't know what the value of the cells you are trying to access have
in them, but maybe you can try and do this.

Select Case DateValue(Target.Value)
Case #1/1/2001# To #12/31/2001# '2001

theSquirrel

On Dec 15, 11:59 am, "stoppal" <stop...@hotmail.com> wrote:
> the mismatch seems to occur on these lines
>
> Select Case Target
> Case "1/1/2001" To "12/31/2001" '2001
>
> But only when I reference a cell populated by a pivot table. If the
> cell is normal (meaning no pivot table populated it) then all works. I
> think there is a way to reference a cell within a pivot table, but I
> don't know how.
>
> thanks for your response. I hope someone can help. I'm at the end of
> my wits!
>
> thanks
>
>
>
> thesquir...@gmail.com wrote:
> > not sure i like the browbeating, and I have no knowledge of pivot
> > tables, but where are you getting your error? You state that its a
> > type mismatch, where is the mismatch?
>
> > theSquirrel
>
> > stoppal wrote:
> > > I guess no one is smart out there or up to a challenge. This should be
> > > rather simple. I'm surprised no one is has the brain power to answer
> > > it.
>
> > > stoppal wrote:
> > > > Is no one smart enough to know this?
>
> > > > stoppal wrote:
> > > > > There must be someone that know's how to do this? I keep getting a
> > > > > type mismatch, which is why I need to figure out how to reference the
> > > > > pivot table cell not the normal cell.
>
> > > > > PLEASE HELP!!!!!
>
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > Dim icolor As Integer
>
> > > > > If Not Intersect(Target, Range("Q1:Q500")) Is Nothing Then
>
> > > > > Select Case Target
> > > > > Case "1/1/2001" To "12/31/2001" '2001
>
> > > > > icolor = 6
>
> > > > > Case 37257 To 37621 '2002
>
> > > > > icolor = 12
>
> > > > > Case 37622 To 37986 '2003
>
> > > > > icolor = 7
>
> > > > > Case 37987 To 38352 '2004
>
> > > > > icolor = 53
>
> > > > > Case 38353 To 38717 '2005
>
> > > > > icolor = 15
>
> > > > > Case 38718 To 39082 '2006
>
> > > > > icolor = 42
>
> > > > > Case 39083 To 39447 '2007
>
> > > > > icolor = 45
>
> > > > > Case 39448 To 39813 '2008
>
> > > > > icolor = 47
>
> > > > > Case Else
> > > > > 'Whatever
> > > > > End Select
>
> > > > > Target.Interior.ColorIndex = icolor
> > > > > End If
>
> > > > > End Sub
>
> > > > > stoppal wrote:
> > > > > > PLEASE ANY ONE! Does anyone know how to reference the cells of a pivot
> > > > > > table?
>
> > > > > > stoppal wrote:
> > > > > > > Hi,
>
> > > > > > > I need some help. trying to use VB to read the value of a pivot table
> > > > > > > so I can conditional format the cells. I know how to conditional
> > > > > > > format using VB on normal cells, but I don't know how for pivot tables.
> > > > > > > How can I reference values from a pivots table?
>
> > > > > > > thank you- Hide quoted text -- Show quoted text -

ShaneDevenshire

12/17/2006 4:22:00 PM

0

Hi,

A little more detail would be useful - are you trying to have the
conditional formatting apply to the whole table? Is it the same condition
for all the cells in the table?

Here is a simple macro which applies conditional formatting to a specific
pivot table. All I did was turn on the recorder.

Sub Macro4()
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly, True
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="10"
Selection.FormatConditions(1).Interior.ColorIndex = 22
End Sub

--
Cheers,
Shane Devenshire


"stoppal" wrote:

> Hi,
>
> I need some help. trying to use VB to read the value of a pivot table
> so I can conditional format the cells. I know how to conditional
> format using VB on normal cells, but I don't know how for pivot tables.
> How can I reference values from a pivots table?
>
> thank you
>
>

stoppal

12/18/2006 10:08:00 PM

0

thank you for the extra help once again!!!

The reason I need to add condiftional formating using VBO rather than
the menu bar is b/c the program has a limit of 3 conditions. I need to
have at least 8 different conditions.

The code you provided at least provide some reference to the pivot
talbe, which is new to me, so thank you. do you know how to reference
the value?

thanks




ShaneDevenshire wrote:
> Hi,
>
> A little more detail would be useful - are you trying to have the
> conditional formatting apply to the whole table? Is it the same condition
> for all the cells in the table?
>
> Here is a simple macro which applies conditional formatting to a specific
> pivot table. All I did was turn on the recorder.
>
> Sub Macro4()
> ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly, True
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
> Formula1:="10"
> Selection.FormatConditions(1).Interior.ColorIndex = 22
> End Sub
>
> --
> Cheers,
> Shane Devenshire
>
>
> "stoppal" wrote:
>
> > Hi,
> >
> > I need some help. trying to use VB to read the value of a pivot table
> > so I can conditional format the cells. I know how to conditional
> > format using VB on normal cells, but I don't know how for pivot tables.
> > How can I reference values from a pivots table?
> >
> > thank you
> >
> >

ShaneDevenshire

12/19/2006 1:58:00 AM

0

Hi,

I see, its not really conditional formatting its VBA controlled formatting.
I'm not sure what you mean by "reference the value", please elaborate. I
need more info to help with this.

Keep in mind that you will need to run the macro everytime you modify the
pivot table.

FYI - if and when you get Excel 2007 you will be able to add an unlimited
number of conditional formats, instead of just 3.

I'm at work this week so I won't always be able to respond quickly.
--
Thanks,
Shane Devenshire


"stoppal" wrote:

> thank you for the extra help once again!!!
>
> The reason I need to add condiftional formating using VBO rather than
> the menu bar is b/c the program has a limit of 3 conditions. I need to
> have at least 8 different conditions.
>
> The code you provided at least provide some reference to the pivot
> talbe, which is new to me, so thank you. do you know how to reference
> the value?
>
> thanks
>
>
>
>
> ShaneDevenshire wrote:
> > Hi,
> >
> > A little more detail would be useful - are you trying to have the
> > conditional formatting apply to the whole table? Is it the same condition
> > for all the cells in the table?
> >
> > Here is a simple macro which applies conditional formatting to a specific
> > pivot table. All I did was turn on the recorder.
> >
> > Sub Macro4()
> > ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly, True
> > Selection.FormatConditions.Delete
> > Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
> > Formula1:="10"
> > Selection.FormatConditions(1).Interior.ColorIndex = 22
> > End Sub
> >
> > --
> > Cheers,
> > Shane Devenshire
> >
> >
> > "stoppal" wrote:
> >
> > > Hi,
> > >
> > > I need some help. trying to use VB to read the value of a pivot table
> > > so I can conditional format the cells. I know how to conditional
> > > format using VB on normal cells, but I don't know how for pivot tables.
> > > How can I reference values from a pivots table?
> > >
> > > thank you
> > >
> > >
>
>