Daigle said:
Ken:
How would I modify this so that the range in question on sheet 2 was
changing based on a calculation taking place on sheet 1 (target cells are an
average of values on sheet 1). So once I change a value on sheet 1, I'd want
the symbols to update based on the newly calculated average?
Thanks!
Hi Daigles,
I set up a workbook with tables of values on sheet 1 and a table on
sheet 2 with the average formula referring to sheet 1 values plus the
pie images. When I changed the sheet 1 values I got an error message
that referred to this line in the sheet 2 code...
Selection.Name = "~" & rngCell.Address
I suppose that is because at the time, sheet 2 is not the active sheet
and the selection is always what ever is selected on the active sheet.
I'm guessing that you also encountered this problem, though I could be
wrong.
The solution to that problem is for the code to...
1. Note the name of the active sheet. This is done using a new variable
for storing the active sheet's name. I used strActiveSheet.
2. Activate sheet 2, the sheet with the pie image manipulating code.
That is done by adding the line...
Me.activate
3.After all the pie image manipulating code is done, reactivate the
originally active sheet with the very final line...
Sheets(strActiveSheet).Activate
Because ScreenUpdating is turned off you don't see any of the sheet
changes occur, except for one quick flicker of the screen, and when you
go to sheet 2 you will see that the new pie images are in place.
In a previous post you stated that you got things working using a
combination of my posts.
I therefore don't know the exact details of your final solution,
however, you might be able to figure out what to do if I give you my
solution as it applies to my most recent post...
Option Explicit
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
'next 3 lines of code are the first part of
'the solution to the "average of values
'on other sheet" problem
Dim strActiveSheet As String
strActiveSheet = ActiveSheet.Name
Me.Activate
'Edit the value of strHorizontal depending
'on where you want the Pie image to be
'horizontally positioned in the cell.
'Values used by the code are...
'"Left", "Center" or "Right" (not case sensitive)
Const strHorizontal As String = "center"
'Edit the value of strVertical depending
'on where you want the Pie image to be
'Vertically positioned in the cell.
'Values used by the code are...
'"Top", "Center" or "Bottom" (not case sensitive)
Const strVertical As String = "center"
Dim iHorizontal As Single
Dim iVertical As Single
Select Case UCase(strHorizontal)
Case "LEFT"
iHorizontal = 0
Case "CENTER"
iHorizontal = 0.5
Case "RIGHT"
iHorizontal = 1
End Select
Select Case UCase(strVertical)
Case "TOP"
iVertical = 0
Case "CENTER"
iVertical = 0.5
Case "BOTTOM"
iVertical = 1
End Select
Dim strActiveCellAddress As String
strActiveCellAddress = ActiveCell.Address
Dim rngCell As Range
Dim strPie As String
Dim ShpPie As Shape
For Each rngCell In Range("B2
11")
On Error Resume Next
Me.Shapes("~" & rngCell.Address).Delete
strPie = ""
On Error GoTo 0
Select Case rngCell.Value
Case ""
Case Is < 0
Case Is < 0.125
strPie = "None"
Case Is < 0.375
strPie = "Quarter"
Case Is < 0.625
strPie = "Half"
Case Is < 0.875
strPie = "ThreeQuarters"
Case Is <= 1
strPie = "Full"
End Select
If strPie <> "" Then
Me.Shapes(strPie).Copy
Range(rngCell.Address).PasteSpecial
Selection.Name = "~" & rngCell.Address
Set ShpPie = Me.Shapes("~" & rngCell.Address)
ShpPie.Left = rngCell.Left + _
iHorizontal * (rngCell.Width - ShpPie.Width)
ShpPie.Top = rngCell.Top + _
iVertical * (rngCell.Height - ShpPie.Height)
End If
Next rngCell
Range(strActiveCellAddress).Select
'next line of code is the last part of the solution
'to the "average of values on other sheet" problem
Sheets(strActiveSheet).Activate
End Sub
Let me know how you go.
Ken Johnson
