Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / April 2008

Tip: Looking for answers? Try searching our database.

Change color of multiple autoshapes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
T-bone - 17 Apr 2008 03:21 GMT
I need to change the color of several autoshape based on different cells

I know how to change one autoshape using a worksheet_change event
but i can't just copy and paste this and change the object name + cell name.

is it possible to have multiple worksheet_change events in the same
worksheet??
Earl Kiosterud - 17 Apr 2008 04:36 GMT
T-bone,

You have only one worksheet_change event, but in it you can test to see which cell was
changed with something like

If not Intersect(Target, Range("A1") is nothing then
 ' do range A1 stuff here
 end if
If not Intersect(Target, Range("A2") is nothing then
 ' do range A2 stuff here
 end if
Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

>I need to change the color of several autoshape based on different cells
>
[quoted text clipped - 3 lines]
> is it possible to have multiple worksheet_change events in the same
> worksheet??
T-bone - 17 Apr 2008 05:26 GMT
Hey Earl,
thanks for your reply
but i don't think i have explained this well enough

hopefully this will help

i have 6 objects named "Object(1-6)"
i have 6 cells named "Cella(1-6)"
i have another 6 cells named "Cellb(1-6)"

i want object 1 ("Object1") to be filled green if
"Cella1" is greater than "Cellb1"
i want object 1 ("Object1") to be filled yellow if
"Cella1" is equal to "Cellb1"
i want object 1 ("Object1") to be filled red if
"Cella1" is less to "Cellb"

i want "Object 2" to be filled green if
"Cella2" is greater than "Cellb2"
i want "Object 2" to be filled yellow if
"Cella2" is equal to "Cellb2"
i want "Object 2" to be filled red if
"Cella2" is less to "Cellb2"

and so on for each of the 6 objects

can you please help with this

T.

> T-bone,
>
[quoted text clipped - 14 lines]
> > is it possible to have multiple worksheet_change events in the same
> > worksheet??
Dave Peterson - 17 Apr 2008 12:49 GMT
In my simpler test, I put 2 ovals from the Drawing toolbar and added names to 4
cells.

The ovals were named Object1 and Object2.
The cells were named CellA1, CellB1, CellA2 and CellB2.

This seemed to work ok:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

   Dim myShape As Shape
   Dim CellA As Range
   Dim CellB As Range
   Dim iCtr As Long
   Dim myColor As Long

   With Me
       For iCtr = 1 To 2 'You'll change this to 6
           Set myShape = Nothing
           Set CellA = Nothing
           Set CellB = Nothing
           On Error Resume Next
           Set myShape = .Shapes("Object" & iCtr)
           Set CellA = .Range("CellA" & iCtr)
           Set CellB = .Range("CellB" & iCtr)
           On Error GoTo 0
           
           If myShape Is Nothing _
            Or CellA Is Nothing _
            Or CellB Is Nothing Then
               MsgBox "Design error with Object/CellA/CellB " & iCtr
           Else
               If Intersect(Target, Union(CellA, CellB)) Is Nothing Then
                   'do nothing
               Else
                   If CellA.Value > CellB.Value Then
                       myColor = 11
                   ElseIf CellA.Value = CellB.Value Then
                       myColor = 13
                   Else
                       myColor = 10
                   End If
                   myShape.OLEFormat.Object.ShapeRange _
                       .Fill.ForeColor.SchemeColor = myColor
               End If
           End If
       Next iCtr
   End With
   
End Sub

> Hey Earl,
> thanks for your reply
[quoted text clipped - 50 lines]
> > > is it possible to have multiple worksheet_change events in the same
> > > worksheet??

Signature

Dave Peterson

T-bone - 18 Apr 2008 02:21 GMT
Dave you are a GENIUS

Thank you so much!!

> In my simpler test, I put 2 ovals from the Drawing toolbar and added names to 4
> cells.
[quoted text clipped - 102 lines]
> > > > is it possible to have multiple worksheet_change events in the same
> > > > worksheet??
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.