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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,222
Office Version
  1. 365
Platform
  1. Windows
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,013
Messages
5,834,925
Members
430,326
Latest member
tomwax46

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