Selecting Shapes
Hi,
I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? |
Selecting Shapes
On May 21, 11:54 am, RLang wrote:
Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? Hello RLang, Here is a macro that will select all Pictures on the active sheet. It does so by creating a 1 based Variant array, which is what the Array function does. The difference here is the Variant array is dynamic. Add a standard module to your project and copy this code to it. Macro Code '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Option Base 1 Sub SelectAllPictures() Dim N As Long Dim ShpArray() As Variant For Each Shp In ActiveSheet If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(N) ShpArray(N) = Shp.Name End If Next Sht Shapes(ShpArray).Select End Sub '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sincerely, Leith Ross |
Selecting Shapes
Hi Leith,
thanks for responding. I'm getting a "Type Mismatch" error on the final select statement. Everything else works up to that point. Here's my code: Option Base 1 Public Sub cmdSelectAll_Click() Dim ImgArray() As Variant, j As Long, Shp j = 0 For Each Shp In ActiveSheet.Shapes If Shp.type = 13 Then j = j + 1 ReDim Preserve ImgArray(j) ImgArray(j) = Shp.Name End If Next Shp Shapes(ImgArray).Select End Sub "Leith Ross" wrote: On May 21, 11:54 am, RLang wrote: Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? Hello RLang, Here is a macro that will select all Pictures on the active sheet. It does so by creating a 1 based Variant array, which is what the Array function does. The difference here is the Variant array is dynamic. Add a standard module to your project and copy this code to it. Macro Code '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Option Base 1 Sub SelectAllPictures() Dim N As Long Dim ShpArray() As Variant For Each Shp In ActiveSheet If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(N) ShpArray(N) = Shp.Name End If Next Sht Shapes(ShpArray).Select End Sub '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sincerely, Leith Ross |
Selecting Shapes
On May 21, 12:54 pm, RLang wrote:
Hi Leith, thanks for responding. I'm getting a "Type Mismatch" error on the final select statement. Everything else works up to that point. Here's my code: Option Base 1 Public Sub cmdSelectAll_Click() Dim ImgArray() As Variant, j As Long, Shp j = 0 For Each Shp In ActiveSheet.Shapes If Shp.type = 13 Then j = j + 1 ReDim Preserve ImgArray(j) ImgArray(j) = Shp.Name End If Next Shp Shapes(ImgArray).Select End Sub "Leith Ross" wrote: On May 21, 11:54 am, RLang wrote: Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? Hello RLang, Here is a macro that will select all Pictures on the active sheet. It does so by creating a 1 based Variant array, which is what the Array function does. The difference here is the Variant array is dynamic. Add a standard module to your project and copy this code to it. Macro Code '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Option Base 1 Sub SelectAllPictures() Dim N As Long Dim ShpArray() As Variant For Each Shp In ActiveSheet If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(N) ShpArray(N) = Shp.Name End If Next Sht Shapes(ShpArray).Select End Sub '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sincerely, Leith Ross Hello RLang, Sorry about that. The line should read... ActiveSheet.Shapes(ShpArray).Select Sincerely, Leith Ross |
Selecting Shapes
Based on Leith's response:
Option Explicit Sub SelectAllPictures2() Dim N As Long Dim ShpArray() As Variant Dim Shp As Shape N = 0 For Each Shp In ActiveSheet.Shapes If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(1 To N) ShpArray(N) = Shp.Name End If Next Shp If N = 0 Then MsgBox "No pics" Else ActiveSheet.Shapes.Range(ShpArray).Select End If End Sub RLang wrote: Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? -- Dave Peterson |
Selecting Shapes
Thanks very much Leith, I'm now squared away based on your advice.
"Leith Ross" wrote: On May 21, 12:54 pm, RLang wrote: Hi Leith, thanks for responding. I'm getting a "Type Mismatch" error on the final select statement. Everything else works up to that point. Here's my code: Option Base 1 Public Sub cmdSelectAll_Click() Dim ImgArray() As Variant, j As Long, Shp j = 0 For Each Shp In ActiveSheet.Shapes If Shp.type = 13 Then j = j + 1 ReDim Preserve ImgArray(j) ImgArray(j) = Shp.Name End If Next Shp Shapes(ImgArray).Select End Sub "Leith Ross" wrote: On May 21, 11:54 am, RLang wrote: Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? Hello RLang, Here is a macro that will select all Pictures on the active sheet. It does so by creating a 1 based Variant array, which is what the Array function does. The difference here is the Variant array is dynamic. Add a standard module to your project and copy this code to it. Macro Code '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Option Base 1 Sub SelectAllPictures() Dim N As Long Dim ShpArray() As Variant For Each Shp In ActiveSheet If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(N) ShpArray(N) = Shp.Name End If Next Sht Shapes(ShpArray).Select End Sub '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sincerely, Leith Ross Hello RLang, Sorry about that. The line should read... ActiveSheet.Shapes(ShpArray).Select Sincerely, Leith Ross |
Selecting Shapes
Thanks as well Dave!
"Dave Peterson" wrote: Based on Leith's response: Option Explicit Sub SelectAllPictures2() Dim N As Long Dim ShpArray() As Variant Dim Shp As Shape N = 0 For Each Shp In ActiveSheet.Shapes If Shp.Type = msoPicture Then N = N + 1 ReDim Preserve ShpArray(1 To N) ShpArray(N) = Shp.Name End If Next Shp If N = 0 Then MsgBox "No pics" Else ActiveSheet.Shapes.Range(ShpArray).Select End If End Sub RLang wrote: Hi, I'd like to create write a function behind a button that selects all the jpg images embedded on the worksheet surface. The command I found for this is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the command which provides for one shot selection. I have not been successful selecting one image at a time without the previous selection "de-selecting". Since there are other buttons and labels on this same worksheet that I don't want selected, I want to start the Array beyond these indexes. Assumption: the jpg's will always be a contiguous set of indicies once the first shape index (type = 13) is found in the shaperange array. I can find the starting index easy enough looping through the shapes array looking at the type. Since the total number of shapes can vary, I can't hardcode in the Array function indicies and I don't know how to place a variable into the arglist of the Array function rather than literals. Any advice? -- Dave Peterson |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com