Selecting every picture in an active cells row

TheTanMan

New Member
Joined
Aug 16, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hello all! I'm new to this website and have been scouring some forums on here to try to get a VBA code to work that selects every photo, pictures, or shape that is in an active cell's row. What my code does so far is insert photos from the file explorer but I'm trying to keep those inserted photos selected to move about afterward.

Here's what I've got so far:

VBA Code:
Sub InsertMultiplePictures()
Dim Pictures() As Variant
Dim PictureFormat As String
Dim PicRng As Range
Dim PicShape As Shape
Dim myshapearray() As String
Dim ws As Worksheet
On Error Resume Next
Pictures = Application.GetOpenFilename(PictureFormat, MultiSelect:=True)
PicColIndex = Application.ActiveCell.Column
If IsArray(Pictures) Then
    PicRowIndex = Application.ActiveCell.Row
    For lLoop = LBound(Pictures) To UBound(Pictures)
        Set PicRng = Cells(PicRowIndex, PicColIndex)
        Set PicShape = ActiveSheet.Shapes.AddPicture(Pictures(lLoop), msoFalse, msoCTrue, PicRng.Left, PicRng.Top, PicRng.Width, PicRng.Height)
        PicColIndex = PicColIndex + 1
    Next
End If

For Each Shape In ActiveSheet.Shapes
    If Shape.Left > Range("A1").Left + Range("A1").Width / 2 And Shape.Left < (Range("C1").Left - Range("B1").Width / 2) Then
        k = k + 1
        ReDim Preserve myshapearray(1 To k) As String
        myshapearray(k) = Shape.Name
    End If
Next
ActiveSheet.Shapes.Range(myshapearray).Select

End Sub

The last For Each I found that selects every pictures in a preset column so it's the closest I've gotten. What am I missing? Any help would be greatly appreciated. Thank you!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try this version:
VBA Code:
'...
Dim cRow As Long                            'ADDED
'...
cRow = ActiveCell.Row                       'ADDED
For Each SHAPE In ActiveSheet.Shapes
    If SHAPE.TopLeftCell.Row = cRow Then    'Modified IF
''    If SHAPE.Left > Range("A1").Left + Range("A1").Width / 2 And SHAPE.Left < (Range("C1").Left - Range("B1").Width / 2) Then  'DELETED
        k = k + 1
        ReDim Preserve myshapearray(1 To k) As String
        myshapearray(k) = SHAPE.Name
    End If
Next
ActiveSheet.Shapes.Range(myshapearray).Select
'...
 
Upvote 0
Solution
Try this version:
VBA Code:
'...
Dim cRow As Long                            'ADDED
'...
cRow = ActiveCell.Row                       'ADDED
For Each SHAPE In ActiveSheet.Shapes
    If SHAPE.TopLeftCell.Row = cRow Then    'Modified IF
''    If SHAPE.Left > Range("A1").Left + Range("A1").Width / 2 And SHAPE.Left < (Range("C1").Left - Range("B1").Width / 2) Then  'DELETED
        k = k + 1
        ReDim Preserve myshapearray(1 To k) As String
        myshapearray(k) = SHAPE.Name
    End If
Next
ActiveSheet.Shapes.Range(myshapearray).Select
'...
You, my friend, are amazing! It worked like a charm! Thank you so much!
 
Upvote 0
Thank you for the feedback
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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