VBA Help with changing shape color for selected shapes only

  • Thread starter Thread starter Nimrod
  • Start date Start date
N

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
 
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.....".
 
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
 
Change the takefocusonclick property of your commandbutton to false.

Then it should work.
 
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
 
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

Nimrod said:
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
Click to expand...
=----
 
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!
 
Back
Top