Format multiple selected shapes

bjprengel

New Member
Joined
Dec 14, 2016
Messages
2
Hi all,

First timer here, currently I am learning VBA and I am progressing pretty good if I say so myself (with Google of course:P).

I want to do the following: I want to be able to select multiple shapes and by clicking a button I want to be able to format the selected shapes. I am currently able to do this with one shape selected (see code) but when I select multiple shapes I get my error msgBox.

The Code:
Sub ActiveShape_10()

'PURPOSE: Determine the currently selected shape
Dim ActiveShape As Shape
Dim UserSelection As Variant

'Pull-in what is selected on screen
Set UserSelection = ActiveWindow.Selection

'Determine if selection is a shape I think I have to change something here... but I have no clue what :confused::confused:
On Error GoTo NoShapeSelected
Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
On Error Resume Next

'Do Something with your Shape variable
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With

Exit Sub

'Error Handler
NoShapeSelected:
MsgBox "You do not have a shape selected!"

End Sub

Heard there is a lot of expertise here so I hope you guys can help me out :)!!

Thanks in advance,

Grt.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

Code:
[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] ActiveShape_10()

    [color=green]'PURPOSE: Determine the currently selected shape[/color]
    [color=darkblue]Dim[/color] oShapeRange [color=darkblue]As[/color] ShapeRange
    [color=darkblue]Dim[/color] UserSelection [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] NoShapeSelected
    
    [color=green]'Pull-in what is selected on screen[/color]
    [color=darkblue]Set[/color] UserSelection = ActiveWindow.Selection
    
    [color=green]'Determine if selection is a ShapeRange[/color]
    [color=darkblue]Set[/color] oShapeRange = UserSelection.ShapeRange
    
    [color=green]'Do Something with ShapeRange[/color]
    [color=darkblue]With[/color] Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    [color=darkblue]End[/color] With
    
    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=green]'Error Handler[/color]
NoShapeSelected:
    MsgBox "You do not have a shape selected!"

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top