VBA Help with changing shape color for selected shapes only

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
donaldb@281.com
"Nimrod" <spikelee7@hotmail.com> wrote in message
news:OT0M8d$SFHA.2916@TK2MSFTNGP15.phx.gbl...
> 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" <donaldb@281.com> wrote in message
news:OqnyIr$SFHA.3392@TK2MSFTNGP12.phx.gbl...
> try
> with selection
> .ShapeRange.Fill.ForeColor.SchemeColor = 10
>
> end with
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Nimrod" <spikelee7@hotmail.com> wrote in message
> news:OT0M8d$SFHA.2916@TK2MSFTNGP15.phx.gbl...
>> 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
donaldb@281.com
"Nimrod" <spikelee7@hotmail.com> wrote in message
news:eRURoGATFHA.2128@TK2MSFTNGP14.phx.gbl...
> 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" <donaldb@281.com> wrote in message
> news:OqnyIr$SFHA.3392@TK2MSFTNGP12.phx.gbl...
> > try
> > with selection
> > .ShapeRange.Fill.ForeColor.SchemeColor = 10
> >
> > end with
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Nimrod" <spikelee7@hotmail.com> wrote in message
> > news:OT0M8d$SFHA.2916@TK2MSFTNGP15.phx.gbl...
> >> 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" <donaldb@281.com> wrote in message
news:OjPBoIATFHA.3184@TK2MSFTNGP09.phx.gbl...
> Did you select the shapes first?
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Nimrod" <spikelee7@hotmail.com> wrote in message
> news:eRURoGATFHA.2128@TK2MSFTNGP14.phx.gbl...
>> 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" <donaldb@281.com> wrote in message
>> news:OqnyIr$SFHA.3392@TK2MSFTNGP12.phx.gbl...
>> > try
>> > with selection
>> > .ShapeRange.Fill.ForeColor.SchemeColor = 10
>> >
>> > end with
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > donaldb@281.com
>> > "Nimrod" <spikelee7@hotmail.com> wrote in message
>> > news:OT0M8d$SFHA.2916@TK2MSFTNGP15.phx.gbl...
>> >> 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" <spikelee7@hotmail.com> wrote in message
news:%23K7pb5ATFHA.1040@TK2MSFTNGP10.phx.gbl...
> 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" <donaldb@281.com> wrote in message
> news:OjPBoIATFHA.3184@TK2MSFTNGP09.phx.gbl...
> > Did you select the shapes first?
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Nimrod" <spikelee7@hotmail.com> wrote in message
> > news:eRURoGATFHA.2128@TK2MSFTNGP14.phx.gbl...
> >> 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" <donaldb@281.com> wrote in message
> >> news:OqnyIr$SFHA.3392@TK2MSFTNGP12.phx.gbl...
> >> > try
> >> > with selection
> >> > .ShapeRange.Fill.ForeColor.SchemeColor = 10
> >> >
> >> > end with
> >> >
> >> > --
> >> > Don Guillett
> >> > SalesAid Software
> >> > donaldb@281.com
> >> > "Nimrod" <spikelee7@hotmail.com> wrote in message
> >> > news:OT0M8d$SFHA.2916@TK2MSFTNGP15.phx.gbl...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <twogilvy@msn.com>
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 =----

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" <spikelee7@hotmail.com> wrote in message
news:3c2371ljr85e43mi7jo4uf7ojfn06uul6v@4ax.com...
> 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" <twogilvy@msn.com>
> 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" <twogilvy@msn.com> wrote in message
news:eAXXHIGTFHA.3140@TK2MSFTNGP14.phx.gbl...
> 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" <spikelee7@hotmail.com> wrote in message
> news:3c2371ljr85e43mi7jo4uf7ojfn06uul6v@4ax.com...
>> 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" <twogilvy@msn.com>
>> 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
> =----
>
>