Tools/Macro/Visual Basic Editor
 

I'm new to this but I have finally hacked out something that does what I want
by using a basic template from another user. My code is at the bottom of the
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
Range)"
was included in the template. I don't know if this is just a title, whether
it has some specific syntax or format or what.

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
included in the template and "Auto Shape 2" is a shape that I added using the
draw tool. It seems that somewhere in Excel I should be able to find a list
of these shapes and that there should be a way to rename the AutoShapes to
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Face").Select
With Range("FaceInd")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

Me.Shapes("AutoShape 2").Select
With Range("AutoShape2ID")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

End Sub

Tools/Macro/Visual Basic Editor
 

Might be time to read a book on VBA
or Google for a tutorial site
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"AL2000" wrote in message
...
I'm new to this but I have finally hacked out something that does what I
want
by using a basic template from another user. My code is at the bottom of
the
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
Range)"
was included in the template. I don't know if this is just a title,
whether
it has some specific syntax or format or what.

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
included in the template and "Auto Shape 2" is a shape that I added using
the
draw tool. It seems that somewhere in Excel I should be able to find a
list
of these shapes and that there should be a way to rename the AutoShapes to
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Face").Select
With Range("FaceInd")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

Me.Shapes("AutoShape 2").Select
With Range("AutoShape2ID")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

End Sub

Tools/Macro/Visual Basic Editor
 

I understand that in order to be fluent I need to read a book or do extensive
research on visual basic.

However, I'm not a programmer and have no real desire to become a
programmer. I was able to take a basic example and expand it to fill my
needs.

I just am not familiar with the naming convention for sheet codes.

"Bernard Liengme" wrote:

Might be time to read a book on VBA
or Google for a tutorial site
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"AL2000" wrote in message
...
I'm new to this but I have finally hacked out something that does what I
want
by using a basic template from another user. My code is at the bottom of
the
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
Range)"
was included in the template. I don't know if this is just a title,
whether
it has some specific syntax or format or what.

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
included in the template and "Auto Shape 2" is a shape that I added using
the
draw tool. It seems that somewhere in Excel I should be able to find a
list
of these shapes and that there should be a way to rename the AutoShapes to
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Face").Select
With Range("FaceInd")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

Me.Shapes("AutoShape 2").Select
With Range("AutoShape2ID")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

End Sub

Tools/Macro/Visual Basic Editor
 

If you have a legal version of Excel on your system (which I presume you do)
then look in VBA help with Alt + f11 to open the VB editor. Type Name in the
help search box and you can read about the name object or names collection.
It will give you an idea of what the naming convention is all about.

"AL2000" wrote:

I understand that in order to be fluent I need to read a book or do extensive
research on visual basic.

However, I'm not a programmer and have no real desire to become a
programmer. I was able to take a basic example and expand it to fill my
needs.

I just am not familiar with the naming convention for sheet codes.

"Bernard Liengme" wrote:

Might be time to read a book on VBA
or Google for a tutorial site
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"AL2000" wrote in message
...
I'm new to this but I have finally hacked out something that does what I
want
by using a basic template from another user. My code is at the bottom of
the
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
Range)"
was included in the template. I don't know if this is just a title,
whether
it has some specific syntax or format or what.

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
included in the template and "Auto Shape 2" is a shape that I added using
the
draw tool. It seems that somewhere in Excel I should be able to find a
list
of these shapes and that there should be a way to rename the AutoShapes to
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Face").Select
With Range("FaceInd")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

Me.Shapes("AutoShape 2").Select
With Range("AutoShape2ID")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

End Sub

Tools/Macro/Visual Basic Editor
 

The line

Private Sub Worksheet_Change(ByVal Target As Range)

is an event handler for a worksheet. Whenever a user changes a cell on
that worksheet, this routine triggers (runs) and executes the code
contained within.
I modified your code slightly by adding a variable to the routine (shp,
which refers to one of the shapes on your worksheet). The reference to
"Me" in your version is a reference to the current object being acted
upon. In this case, I am assuming that this would be the worksheet
(which I have assumed has a CodeName of "Sheet1". Change this to meet
your purposes. The Shapes (and ShapeRange) objects are a little more
advanced to use, and can be troublesome for beginners. I managed to
eliminate the use of "ShapeRange" in your code to get it to work. Your
worksheet apparently has 2 named ranges on it: one cell named
"FaceInd", which specifies the color for the "Face" shape, and
"AutoShape2ID" for the "AutoShape 2" shape. You can change these to
whatever you want, using the Name edit field next to the Formula bar,
but be sure to change them to agree in the code below.

The revised code below is not ideal, but should give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shp As Shape

Set shp = Sheet1.Shapes("Face")
With Range("FaceInd")
If .Value <= 3 Then
shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
shp.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
shp.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If
End With

Set shp = Sheet1.Shapes("AutoShape 2")
With Range("AutoShape2ID")
If .Value <= 3 Then
shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
shp.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
shp.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If
End With
End Sub
--
Regards,
Bill Renaud

Tools/Macro/Visual Basic Editor
 

Thank you.

"JLGWhiz" wrote:

If you have a legal version of Excel on your system (which I presume you do)
then look in VBA help with Alt + f11 to open the VB editor. Type Name in the
help search box and you can read about the name object or names collection.
It will give you an idea of what the naming convention is all about.

"AL2000" wrote:

I understand that in order to be fluent I need to read a book or do extensive
research on visual basic.

However, I'm not a programmer and have no real desire to become a
programmer. I was able to take a basic example and expand it to fill my
needs.

I just am not familiar with the naming convention for sheet codes.

"Bernard Liengme" wrote:

Might be time to read a book on VBA
or Google for a tutorial site
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"AL2000" wrote in message
...
I'm new to this but I have finally hacked out something that does what I
want
by using a basic template from another user. My code is at the bottom of
the
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
Range)"
was included in the template. I don't know if this is just a title,
whether
it has some specific syntax or format or what.

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
included in the template and "Auto Shape 2" is a shape that I added using
the
draw tool. It seems that somewhere in Excel I should be able to find a
list
of these shapes and that there should be a way to rename the AutoShapes to
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Shapes("Face").Select
With Range("FaceInd")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

Me.Shapes("AutoShape 2").Select
With Range("AutoShape2ID")
If .Value <= 3 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If

.Select
End With

End Sub

Tools/Macro/Visual Basic Editor
 

Bill, thank you so much. After understanding the the first line was a commnad
line and not a name, I was able to read the help menu and understand broadly
what it meant.

I understand exactly what you did with shp. This eliminated a problem in my
code where the image had selection marks.

This macro will satisfy my needs for a complex worksheet where I am
developing graphs and charts and establishing baseline objectives that will
result in a red, yellow, or green box in the upper corner of the chart. This
will allow me to spot problems at a glance.

I really appreciate your help.

"Bill Renaud" wrote:

The line

Private Sub Worksheet_Change(ByVal Target As Range)

is an event handler for a worksheet. Whenever a user changes a cell on
that worksheet, this routine triggers (runs) and executes the code
contained within.
I modified your code slightly by adding a variable to the routine (shp,
which refers to one of the shapes on your worksheet). The reference to
"Me" in your version is a reference to the current object being acted
upon. In this case, I am assuming that this would be the worksheet
(which I have assumed has a CodeName of "Sheet1". Change this to meet
your purposes. The Shapes (and ShapeRange) objects are a little more
advanced to use, and can be troublesome for beginners. I managed to
eliminate the use of "ShapeRange" in your code to get it to work. Your
worksheet apparently has 2 named ranges on it: one cell named
"FaceInd", which specifies the color for the "Face" shape, and
"AutoShape2ID" for the "AutoShape 2" shape. You can change these to
whatever you want, using the Name edit field next to the Formula bar,
but be sure to change them to agree in the code below.

The revised code below is not ideal, but should give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shp As Shape

Set shp = Sheet1.Shapes("Face")
With Range("FaceInd")
If .Value <= 3 Then
shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
shp.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
shp.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If
End With

Set shp = Sheet1.Shapes("AutoShape 2")
With Range("AutoShape2ID")
If .Value <= 3 Then
shp.Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf .Value 3 And .Value <= 6 Then
shp.Fill.ForeColor.RGB = RGB(255, 254, 0)
Else
shp.Fill.ForeColor.RGB = RGB(88, 146, 0)
End If
End With
End Sub
--
Regards,
Bill Renaud

Tools/Macro/Visual Basic Editor
 

Yes, looking up Help for events can be difficult, until you understand
the objects for a particular application. Simply placing the cursor on
the Worksheet_Change event and pressing the <F1 key typically (in Excel
2000) shows the "Keyword Not Found" topic in Help.

Whenever you have an event handler, such as Worksheet_Change in this
case, the name of the routine is of the form Object_Event. The easiest
way to find help is probably to open the Object Browser (press <F2),
copy the "Worksheet" part of the routine name into the Search Text box
and press Enter, then scroll down the Members in the lower-right corner
list box until you come to the Change event (it will have a yellow
lightning bolt to the left of it), select it and press <F1 to bring up
the Help topic.

Is your chart embedded on the worksheet, or on a separate chart sheet?
Your code may get more complicated if the shape is embedded inside (or
on top) of the embedded chart. I assumed that the shape was simply on
top of some cells on the worksheet.
--
Regards,
Bill Renaud

Tools/Macro/Visual Basic Editor
 

Charlie Kyd has some great charts that are used for business dashboards:
http://www.exceluser.com/dash/index.htm

I wanted to add a small shape in the top right corner of each chart that
shows red, yellow, or green.

The data is typically on a separte sheet and there are just a series of
charts on one page. I'm guessing I'll have to reference the second worksheet.
But you have gotten me far enough along that I think I can figure out the
rest.

It's one of those things when you don't know where to start, it looks like a
very big hill.

"Bill Renaud" wrote:

Yes, looking up Help for events can be difficult, until you understand
the objects for a particular application. Simply placing the cursor on
the Worksheet_Change event and pressing the <F1 key typically (in Excel
2000) shows the "Keyword Not Found" topic in Help.

Whenever you have an event handler, such as Worksheet_Change in this
case, the name of the routine is of the form Object_Event. The easiest
way to find help is probably to open the Object Browser (press <F2),
copy the "Worksheet" part of the routine name into the Search Text box
and press Enter, then scroll down the Members in the lower-right corner
list box until you come to the Change event (it will have a yellow
lightning bolt to the left of it), select it and press <F1 to bring up
the Help topic.

Is your chart embedded on the worksheet, or on a separate chart sheet?
Your code may get more complicated if the shape is embedded inside (or
on top) of the embedded chart. I assumed that the shape was simply on
top of some cells on the worksheet.
--
Regards,
Bill Renaud

Tools/Macro/Visual Basic Editor
 

Thanks for the reference to the ExcelUser web site that has the business
dashboards. Those are pretty spiffy, so I bookmarked the site. May come
in handy some day.

In the code, just change the object from Sheet1 to the code name for the
worksheet that has all of your charts, for starters. To make it run
faster, normally you would check to see what the Target argument is and
just update that shape, since that is probably the only cell that
changed. You might want to put the code to update the shape in a
separate subroutine.
--
Regards,
Bill Renaud