+ Reply to Thread
Results 1 to 9 of 9

VBA Help with changing shape color for selected shapes only

  1. #1
    Nimrod
    Guest

    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



  2. #2
    Don Guillett
    Guest

    Re: VBA Help with changing shape color for selected shapes only

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

    end with

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Nimrod" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    >




  3. #3
    Nimrod
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    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" <[email protected]> wrote in message
    news:[email protected]...
    > try
    > with selection
    > .ShapeRange.Fill.ForeColor.SchemeColor = 10
    >
    > end with
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "Nimrod" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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
    >>
    >>

    >
    >




  4. #4
    Don Guillett
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    Did you select the shapes first?

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Nimrod" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > try
    > > with selection
    > > .ShapeRange.Fill.ForeColor.SchemeColor = 10
    > >
    > > end with
    > >
    > > --
    > > Don Guillett
    > > SalesAid Software
    > > [email protected]
    > > "Nimrod" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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
    > >>
    > >>

    > >
    > >

    >
    >




  5. #5
    Nimrod
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    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" <[email protected]> wrote in message
    news:[email protected]...
    > Did you select the shapes first?
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "Nimrod" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > try
    >> > with selection
    >> > .ShapeRange.Fill.ForeColor.SchemeColor = 10
    >> >
    >> > end with
    >> >
    >> > --
    >> > Don Guillett
    >> > SalesAid Software
    >> > [email protected]
    >> > "Nimrod" <[email protected]> wrote in message
    >> > news:[email protected]...
    >> >> 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
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  6. #6
    Tom Ogilvy
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    Change the takefocusonclick property of your commandbutton to false.

    Then it should work.

    --
    Regards,
    Tom Ogilvy


    "Nimrod" <[email protected]> wrote in message
    news:%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > Did you select the shapes first?
    > >
    > > --
    > > Don Guillett
    > > SalesAid Software
    > > [email protected]
    > > "Nimrod" <[email protected]> wrote in message
    > > news:[email protected]...
    > >> 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > try
    > >> > with selection
    > >> > .ShapeRange.Fill.ForeColor.SchemeColor = 10
    > >> >
    > >> > end with
    > >> >
    > >> > --
    > >> > Don Guillett
    > >> > SalesAid Software
    > >> > [email protected]
    > >> > "Nimrod" <[email protected]> wrote in message
    > >> > news:[email protected]...
    > >> >> 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
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




  7. #7
    Nimrod
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    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" <[email protected]>
    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 =----

  8. #8
    Tom Ogilvy
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]>
    > 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

    =----



  9. #9
    Nimrod
    Guest

    Re: VBA Help with changing shape color for selected shapes only

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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" <[email protected]>
    >> 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

    > =----
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1