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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,455
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,127,087
Messages
5,622,622
Members
415,914
Latest member
Uriel Castillo

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