VBA Help with changing shape color for selected shapes only

[Nimrod]

I'm in need of help. I have an Excel sheet that has some rectangle shapes.
I want to add some command buttons to change the color of these rectangles,
but I only want those shapes I select to be changed.

Here is a "Reset" command button code I use to turn all my shapes Red:

Private Sub CommandButton1_Click()
ActiveSheet.Shapes.SelectAll
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Range("A1").Select
End Sub

Now I know how to change a single shape that I specify:

ActiveSheet.Shapes("Rectangle 1").Select

But how do I change only the ones I currently have selected? And is there a
better way to unselect (deselect) rather than using Range("A1").Select?

Thanks in advance,

Scott

 

[Don Guillett]

try
with selection
..ShapeRange.Fill.ForeColor.SchemeColor = 10

end with

 

[Nimrod]

I get a runtime error "438". Here is how I have it:

Private Sub CommandButton2_Click()
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 12
End With
Range("A1").Select
End Sub

The runtime error '438' "Object doesn't support this property or method".
The debugger highlights the line ".shaperange.fill.forecolor.....".

 

[Don Guillett]

Did you select the shapes first?

 

[Nimrod]

Yes sir. I have two shapes (both rectangles) and I tried to select either
as well as both, and I get the same error. Any other ideas?

Thanks again for your assistance!
Scott

 

[Tom Ogilvy]

Change the takefocusonclick property of your commandbutton to false.

Then it should work.

 

[Nimrod]

Thanks Tom, and Don. The changing of "takefocusonclick" solved the
problem. I have one more problem maybe one of you two can assist
with. The code works great:

Private Sub CommandButton1_Click()
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 8
End With
Range("A1").Select
End Sub

But with no shape selected I get that 438 Runtime Error. What VBA
code can I use to prevent (Stop) the code thus preventing the error.

Thanks again.

Scott

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
On Error Resume Next
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 8
End With
On Error goto 0
Range("A1").Select
End SuB

--
Regards,
Tom Ogilvy

Thanks Tom, and Don. The changing of "takefocusonclick" solved the
problem. I have one more problem maybe one of you two can assist
with. The code works great:

Private Sub CommandButton1_Click()
With Selection
.ShapeRange.Fill.ForeColor.SchemeColor = 8
End With
Range("A1").Select
End Sub

But with no shape selected I get that 438 Runtime Error. What VBA
code can I use to prevent (Stop) the code thus preventing the error.

Thanks again.

Scott

=----

 

[Nimrod]

I'm thankful for all the help I've received. The error trapping for Excel
VBA is the same as Access (hangs head low in shame). Thanks again!