+ Reply to Thread
Results 1 to 9 of 9

VBA Help with changing shape color for selected shapes only

Hybrid View

  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
    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
    >
    >




  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" <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
    >>
    >>

    >
    >




  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
    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
    > >>
    > >>

    > >
    > >

    >
    >




  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" <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
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  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" <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
    > >> >>
    > >> >>
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >




+ 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