Selecting pictures

Raneis

New Member
Joined
Dec 2, 2016
Messages
3
Hi, I have a sheet full of chart pictures: 20 columns, each with four vertical picture.
I need to randomly select a single picture, but couldn't find the logic behind the serials attached to a picture by the system. I recorded a few macros, arbitrarily selecting a picture - but wasn't able to get the logic behind it.
Any suggestions? Thanks.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Do you just need to know which cell each of your charts is located in? This code builds a table with that info in it. Be careful because it puts the results in column A, so change the value of chdata before you run it if you have anything already in column A:-

Code:
Option Explicit

Public Sub WhereChart()

  Dim shp As Shape
  Const chdata As String = [COLOR=#FF0000][B]"A"[/B][/COLOR]

  Columns(chdata).ClearContents
  
  For Each shp In ActiveSheet.Shapes
    If shp.Type = msoChart Then
      Cells(Cells(Rows.Count, chdata).End(xlUp).Row + 1, chdata) = "Chart """ & shp.Name & """ is in " & getcolumn(shp) & getrow(shp)
    End If
  Next shp
End Sub

Private Function getcolumn(ByVal shp As Shape) As String

  Dim ic As Long

  For ic = 1 To Columns.Count
    If Cells(1, ic).Left > shp.Left Then
      getcolumn = Replace(Replace(Cells(1, ic - 1).Address, "1", ""), "$", "")
      Exit Function
    End If
  Next ic

End Function

Private Function getrow(ByVal shp As Shape) As Long

  Dim ir As Long

  For ir = 1 To Rows.Count
    If Cells(ir, 1).Top > shp.Top Then
      getrow = ir - 1
      Exit Function
    End If
  Next ir

End Function
 

Raneis

New Member
Joined
Dec 2, 2016
Messages
3
Thanks, but that wouldn't work for me. If you imagine the pictures as an array (4 rows by 20 columns), I know which picture I need to select however,
I don't know how the system designates it. I could not create the "exchange table" between my coordinates and the sustem's.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
You'd have to cycle through the pictures one by one and check which one is located at the row and column you're interested in.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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