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

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

end with

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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.....".

"Don Guillett" wrote in message
...
try
with selection
.ShapeRange.Fill.ForeColor.SchemeColor = 10

end with

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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

Did you select the shapes first?

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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" wrote in message
...
try
with selection
.ShapeRange.Fill.ForeColor.SchemeColor = 10

end with

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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

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


"Don Guillett" wrote in message
...
Did you select the shapes first?

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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" wrote in message
...
try
with selection
.ShapeRange.Fill.ForeColor.SchemeColor = 10

end with

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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

Change the takefocusonclick property of your commandbutton to false.

Then it should work.

--
Regards,
Tom Ogilvy


"Nimrod" wrote in message
...
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


"Don Guillett" wrote in message
...
Did you select the shapes first?

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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" wrote in message
...
try
with selection
.ShapeRange.Fill.ForeColor.SchemeColor = 10

end with

--
Don Guillett
SalesAid Software

"Nimrod" wrote in message
...
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

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" wrote in message
...
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


On Thu, 28 Apr 2005 16:29:23 -0400, "Tom Ogilvy"
wrote:

Change the takefocusonclick property of your commandbutton to false.

Then it should work.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

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!

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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


On Thu, 28 Apr 2005 16:29:23 -0400, "Tom Ogilvy"
wrote:

Change the takefocusonclick property of your commandbutton to false.

Then it should work.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----