Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Good morning,
Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Hi,
Below is my code to delete text boxes ... You can adapt it to your situation : Sub DeleteTextBox() Dim myshape As Shape Dim rng As Range For Each myshape In ActiveSheet.Shapes Set rng = myshape.TopLeftCell If Intersect(rng, ActiveCell) Is Nothing Then 'do nothing Else myshape.Delete End If Next myshape End Sub HTH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
the problem is shapes do not sit in cell they sit ontop of cells. they also
don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The code only tests the left side of the shape and the top of the shape. "Please Help" wrote: Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Hi Carim,
Thanks for the code. I take your code and make a few changes to suit my needs, and it does not seem to work. Can you help me more? Below is my modified code. In addition to removing the shapes, I also like to include the code to message the users the # of shapes got removed and referencing the cells selected and worksheet. In addition, how does your code know what type of shape to remove because I only want to remove the shapes which are 13 (msoLinkedPicture). Thanks. Dim myshape As Shape Dim rng As Range dim cCount as Integer For Each myshape In ActiveSheet.Shapes Set rng = myshape.TopLeftCell If Intersect(rng, ActiveCell) Is Nothing Then Else cCount = cCount + 1 myshape.Delete End If Next myshape MsgBox "You just removed " & cCount & " check marks in highlighted cells '" & rng.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." "Carim" wrote: Hi, Below is my code to delete text boxes ... You can adapt it to your situation : Sub DeleteTextBox() Dim myshape As Shape Dim rng As Range For Each myshape In ActiveSheet.Shapes Set rng = myshape.TopLeftCell If Intersect(rng, ActiveCell) Is Nothing Then 'do nothing Else myshape.Delete End If Next myshape End Sub HTH |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Don,
First of all, thanks for the code. Secondly, I will make a note about the meaningful subject line. I take your code and make a few changes to suit my needs, and it does not seem to work. Can you help me more? Below is my modified code. In addition to removing the shapes, I also like to include the code to message the users the # of shapes got removed and referencing the cells. In addition, how does your code know what type of shape to delete because I only want to delete the shapes which are 13 (msoLinkedPicture)? You also reference the range E1:E24. Would it work if a user selects other than E1:E24? What do you mean by "'does the top left corner of the shape overlap rngUsable?"? Thanks. Dim shpLoop As Shape dim cCount as Integer Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then cCount = cCount + 1 shpLoop.Delete End If Next shpLoop MsgBox "You just removed " & cCount & " check marks in highlighted cells '" & rngUsuable.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." "Don Guillett" wrote: Please use a MEANINGFUL subject line. I'm not sure you can select the range if the the shape is covering it, But if so adapt this to suit. Sub ShapesInRangeDelete() 'Iain Dim shpLoop As Shape Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then shpLoop.Delete End If Next shpLoop End Sub ====== This might work best. Hold down the ctrl key while selecting the shapes(not the range) Sub DeleteSelectedshapes() Selection.Delete End sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Please Help" wrote in message ... Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Just use the 2nd code I provided after they hold the ctrl key and select
each shape -- Don Guillett Microsoft MVP Excel SalesAid Software "Please Help" wrote in message ... Don, First of all, thanks for the code. Secondly, I will make a note about the meaningful subject line. I take your code and make a few changes to suit my needs, and it does not seem to work. Can you help me more? Below is my modified code. In addition to removing the shapes, I also like to include the code to message the users the # of shapes got removed and referencing the cells. In addition, how does your code know what type of shape to delete because I only want to delete the shapes which are 13 (msoLinkedPicture)? You also reference the range E1:E24. Would it work if a user selects other than E1:E24? What do you mean by "'does the top left corner of the shape overlap rngUsable?"? Thanks. Dim shpLoop As Shape dim cCount as Integer Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then cCount = cCount + 1 shpLoop.Delete End If Next shpLoop MsgBox "You just removed " & cCount & " check marks in highlighted cells '" & rngUsuable.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." "Don Guillett" wrote: Please use a MEANINGFUL subject line. I'm not sure you can select the range if the the shape is covering it, But if so adapt this to suit. Sub ShapesInRangeDelete() 'Iain Dim shpLoop As Shape Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then shpLoop.Delete End If Next shpLoop End Sub ====== This might work best. Hold down the ctrl key while selecting the shapes(not the range) Sub DeleteSelectedshapes() Selection.Delete End sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Please Help" wrote in message ... Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Joel,
Thanks for your response. For what I need, I don't think we can use the shape measurement because we use many different shapes. However, the shapes are type 13 (msoLinkedPicture). Anymore idea? Thanks. "Joel" wrote: the problem is shapes do not sit in cell they sit ontop of cells. they also don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The code only tests the left side of the shape and the top of the shape. "Please Help" wrote: Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Don,
Shapes will not be selected because there may be a lot of shapes that we want to remove. If the shapes are to be selected individually, it will take a long time. I was hoping that I can just select (highlight) the cells where the shapes are. Then the macro will remove all the shapes in those cells. Plus, I do not want to remove all the shapes in the active sheet. For that, I have a separate macro. One question: By highlighting a group cells, wouldn't make those cells active cells? Therefore, can we use the syntax "Activecell" in the code for what I need? Thanks again for your helps. "Don Guillett" wrote: Just use the 2nd code I provided after they hold the ctrl key and select each shape -- Don Guillett Microsoft MVP Excel SalesAid Software "Please Help" wrote in message ... Don, First of all, thanks for the code. Secondly, I will make a note about the meaningful subject line. I take your code and make a few changes to suit my needs, and it does not seem to work. Can you help me more? Below is my modified code. In addition to removing the shapes, I also like to include the code to message the users the # of shapes got removed and referencing the cells. In addition, how does your code know what type of shape to delete because I only want to delete the shapes which are 13 (msoLinkedPicture)? You also reference the range E1:E24. Would it work if a user selects other than E1:E24? What do you mean by "'does the top left corner of the shape overlap rngUsable?"? Thanks. Dim shpLoop As Shape dim cCount as Integer Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then cCount = cCount + 1 shpLoop.Delete End If Next shpLoop MsgBox "You just removed " & cCount & " check marks in highlighted cells '" & rngUsuable.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." "Don Guillett" wrote: Please use a MEANINGFUL subject line. I'm not sure you can select the range if the the shape is covering it, But if so adapt this to suit. Sub ShapesInRangeDelete() 'Iain Dim shpLoop As Shape Set rngUsable = Range("e1:e24") For Each shpLoop In ActiveSheet.Shapes 'does the top left corner of the shape overlap rngUsable? If Not (Application.Intersect(rngUsable, shpLoop.TopLeftCell) Is Nothing) Then shpLoop.Delete End If Next shpLoop End Sub ====== This might work best. Hold down the ctrl key while selecting the shapes(not the range) Sub DeleteSelectedshapes() Selection.Delete End sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Please Help" wrote in message ... Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Here is a simple modification to handle all seleted areas.
Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left RTop = Range("A1").Top Rright = Range("E5").Left + Range("E5").Width RBottom = Range("E5").Top + Range("E5").Width For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, Thanks for your response. For what I need, I don't think we can use the shape measurement because we use many different shapes. However, the shapes are type 13 (msoLinkedPicture). Anymore idea? Thanks. "Joel" wrote: the problem is shapes do not sit in cell they sit ontop of cells. they also don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The code only tests the left side of the shape and the top of the shape. "Please Help" wrote: Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Joel,
This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left RTop = Range("A1").Top Rright = Range("E5").Left + Range("E5").Width RBottom = Range("E5").Top + Range("E5").Width For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, Thanks for your response. For what I need, I don't think we can use the shape measurement because we use many different shapes. However, the shapes are type 13 (msoLinkedPicture). Anymore idea? Thanks. "Joel" wrote: the problem is shapes do not sit in cell they sit ontop of cells. they also don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The code only tests the left side of the shape and the top of the shape. "Please Help" wrote: Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Glad the problem is so simple. Just moving one line of code should fix
problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left RTop = Range("A1").Top Rright = Range("E5").Left + Range("E5").Width RBottom = Range("E5").Top + Range("E5").Width For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, Thanks for your response. For what I need, I don't think we can use the shape measurement because we use many different shapes. However, the shapes are type 13 (msoLinkedPicture). Anymore idea? Thanks. "Joel" wrote: the problem is shapes do not sit in cell they sit ontop of cells. they also don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The code only tests the left side of the shape and the top of the shape. "Please Help" wrote: Good morning, Please help with my code below. Basically, my code will remove the shapes in the cells selected (instead of all the shapes in the active worksheet) of the active worksheet, and it is not work. For example, if I select cells A1, B1, and C5:G10 and when I run the macro, the macro will only remove the shapes in those cells. Please note the cells selected will be done by users and may not be the same as referenced above. Thanks. Dim cMarks As Shape Dim sCells as Range Dim cCount as Integer sCells.Select For Each sCells In ActiveSheet For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then cCount = cCount + 1 cMarks.Delete End If Next cMarks Next sCells MsgBox "You have removed " & cCount & " check marks in highlighted cells '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Joel,
This new code seems to work. It removes the shapes on the cells that I selected and gives me a message all at once, except one problem. I don't know what causes it. Sometimes, in addition to removing the shapes for the selected cells, it also removes the shapes next to those cells. For example, I selected B3 and B12, and it also removed the shapes in C4. One thing I don't know I should let you know. Sometimes, we may have more than one shape in a cell. For example, Cell B1 may have 3 shapes (and usually they are different shapes). However, the cells for the example that I mentioned above contain only one shape. I also tried on a cell with multiple shapes, and it would cause the same problem. I don't think the problem is one shape or multiple shapes in a cell. Thanks. "Joel" wrote: Glad the problem is so simple. Just moving one line of code should fix problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left RTop = Range("A1").Top Rright = Range("E5").Left + Range("E5").Width RBottom = Range("E5").Top + Range("E5").Width For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, Thanks for your response. For what I need, I don't think we can use the shape measurement because we use many different shapes. However, the shapes are type 13 (msoLinkedPicture). Anymore idea? Thanks. "Joel" wrote: the problem is shapes do not sit in cell they sit ontop of cells. they also don't have a row or column. Both cells and shapes do have left, top, height, width which are pixel measurments. You can write code like the sample below to compartre shapes with cells. Note: The code below doesn't test the right side or botttom of the shape to see if it is inside the range. Wasn't sure if this is a requirement. The |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
There are two reason for deleting the cell to the right.
1) the shape is right on the edge between the two cells. Possibly change the <= to just < might fix the problem from If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then to If cMarks.Top = RTop And _ cMarks.Top < RBottom And _ cMarks.Left = RLeft And _ cMarks.Left < Rright Then 2) I don't know how accurate the check marks werer placed on the worksheet. It is possible the left side of check mark is in the cell to the left. The code can't fix this problem. Can you tell me how the check marks were put into the worksheet? "Please Help" wrote: Joel, This new code seems to work. It removes the shapes on the cells that I selected and gives me a message all at once, except one problem. I don't know what causes it. Sometimes, in addition to removing the shapes for the selected cells, it also removes the shapes next to those cells. For example, I selected B3 and B12, and it also removed the shapes in C4. One thing I don't know I should let you know. Sometimes, we may have more than one shape in a cell. For example, Cell B1 may have 3 shapes (and usually they are different shapes). However, the cells for the example that I mentioned above contain only one shape. I also tried on a cell with multiple shapes, and it would cause the same problem. I don't think the problem is one shape or multiple shapes in a cell. Thanks. "Joel" wrote: Glad the problem is so simple. Just moving one line of code should fix problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left RTop = Range("A1").Top Rright = Range("E5").Left + Range("E5").Width RBottom = Range("E5").Top + Range("E5").Width For Each cMarks In sCells.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
We have a toolbar of shapes. What we do is we select a cell where we want to
place the shape to, and we click on the shape that we want on the toolbar. The shape then places in the cell. Sometimes, we move the shape to a different cell or a different place (e.g. right of the cell) in that cell that we selected. Thanks. "Joel" wrote: There are two reason for deleting the cell to the right. 1) the shape is right on the edge between the two cells. Possibly change the <= to just < might fix the problem from If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then to If cMarks.Top = RTop And _ cMarks.Top < RBottom And _ cMarks.Left = RLeft And _ cMarks.Left < Rright Then 2) I don't know how accurate the check marks werer placed on the worksheet. It is possible the left side of check mark is in the cell to the left. The code can't fix this problem. Can you tell me how the check marks were put into the worksheet? "Please Help" wrote: Joel, This new code seems to work. It removes the shapes on the cells that I selected and gives me a message all at once, except one problem. I don't know what causes it. Sometimes, in addition to removing the shapes for the selected cells, it also removes the shapes next to those cells. For example, I selected B3 and B12, and it also removed the shapes in C4. One thing I don't know I should let you know. Sometimes, we may have more than one shape in a cell. For example, Cell B1 may have 3 shapes (and usually they are different shapes). However, the cells for the example that I mentioned above contain only one shape. I also tried on a cell with multiple shapes, and it would cause the same problem. I don't think the problem is one shape or multiple shapes in a cell. Thanks. "Joel" wrote: Glad the problem is so simple. Just moving one line of code should fix problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: I forgot to post my code. All shapes have four location/size parameters Top, Left, Height, Width Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer RLeft = Range("A1").Left |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
You r method of placing the shape on the worksheet is not very precise. The
problem just may be you arre within the limits of the box to the left. The other problem is if you resize a column the shape doesn't move. It may end up on a different column. You may want to write a macro that adds the check mark. Highlight cells and then run the macro to put the check marks onto the sheets. Do you use linked cells with your check mark? You could highlight the linked cells and remove the check marks based on the linked cell rather than the cells the check marks are sitting on. "Please Help" wrote: We have a toolbar of shapes. What we do is we select a cell where we want to place the shape to, and we click on the shape that we want on the toolbar. The shape then places in the cell. Sometimes, we move the shape to a different cell or a different place (e.g. right of the cell) in that cell that we selected. Thanks. "Joel" wrote: There are two reason for deleting the cell to the right. 1) the shape is right on the edge between the two cells. Possibly change the <= to just < might fix the problem from If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then to If cMarks.Top = RTop And _ cMarks.Top < RBottom And _ cMarks.Left = RLeft And _ cMarks.Left < Rright Then 2) I don't know how accurate the check marks werer placed on the worksheet. It is possible the left side of check mark is in the cell to the left. The code can't fix this problem. Can you tell me how the check marks were put into the worksheet? "Please Help" wrote: Joel, This new code seems to work. It removes the shapes on the cells that I selected and gives me a message all at once, except one problem. I don't know what causes it. Sometimes, in addition to removing the shapes for the selected cells, it also removes the shapes next to those cells. For example, I selected B3 and B12, and it also removed the shapes in C4. One thing I don't know I should let you know. Sometimes, we may have more than one shape in a cell. For example, Cell B1 may have 3 shapes (and usually they are different shapes). However, the cells for the example that I mentioned above contain only one shape. I also tried on a cell with multiple shapes, and it would cause the same problem. I don't think the problem is one shape or multiple shapes in a cell. Thanks. "Joel" wrote: Glad the problem is so simple. Just moving one line of code should fix problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, Thanks for the code. When I run your code, I got the following error: Run-time error '91': Object variable or With block variable not set And it pointed me to the line "For Each cMarks In sCells.Shapes". I am just curious something. How does the code know the range for "sCells" because there is no reference for range for sCells? Thanks. "Joel" wrote: |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help with my code
Joel,
Thanks again very much for your help and patience. I know sometimes it is hard to write a code when we are dealing with shapes. Yes, we may use shapes on the linked cells. Unfortunately, I can write a code to add the shapes because shapes are different and we use a lot of different types of shapes. Would you mind explain to me about your code on why you have to come up with? I would love to learn from this experience. Thanks. Have a safe and nice holiday season! "Joel" wrote: You r method of placing the shape on the worksheet is not very precise. The problem just may be you arre within the limits of the box to the left. The other problem is if you resize a column the shape doesn't move. It may end up on a different column. You may want to write a macro that adds the check mark. Highlight cells and then run the macro to put the check marks onto the sheets. Do you use linked cells with your check mark? You could highlight the linked cells and remove the check marks based on the linked cell rather than the cells the check marks are sitting on. "Please Help" wrote: We have a toolbar of shapes. What we do is we select a cell where we want to place the shape to, and we click on the shape that we want on the toolbar. The shape then places in the cell. Sometimes, we move the shape to a different cell or a different place (e.g. right of the cell) in that cell that we selected. Thanks. "Joel" wrote: There are two reason for deleting the cell to the right. 1) the shape is right on the edge between the two cells. Possibly change the <= to just < might fix the problem from If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= Rright Then to If cMarks.Top = RTop And _ cMarks.Top < RBottom And _ cMarks.Left = RLeft And _ cMarks.Left < Rright Then 2) I don't know how accurate the check marks werer placed on the worksheet. It is possible the left side of check mark is in the cell to the left. The code can't fix this problem. Can you tell me how the check marks were put into the worksheet? "Please Help" wrote: Joel, This new code seems to work. It removes the shapes on the cells that I selected and gives me a message all at once, except one problem. I don't know what causes it. Sometimes, in addition to removing the shapes for the selected cells, it also removes the shapes next to those cells. For example, I selected B3 and B12, and it also removed the shapes in C4. One thing I don't know I should let you know. Sometimes, we may have more than one shape in a cell. For example, Cell B1 may have 3 shapes (and usually they are different shapes). However, the cells for the example that I mentioned above contain only one shape. I also tried on a cell with multiple shapes, and it would cause the same problem. I don't think the problem is one shape or multiple shapes in a cell. Thanks. "Joel" wrote: Glad the problem is so simple. Just moving one line of code should fix problem I'm also initializing the count just in case of problems. I also made a changge so the address printed in the msgbox contains all the addresses. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer cCount = 0 address_string = "" For Each myRange In Selection.Areas if address_string = "" then address_string = myRange.address else address_string = address_string & "," & _ myRange.address end if myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks Next myRange MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & address_string & "' of the Worksheet '" & _ ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, This new code removes all the shapes in the cells selected. However, instead of removing all the shapes and showing the message all at once, removal is done based on each group of cells selected. For example, if I have selected 4 groups of cells (using Ctrl key), the macro will remove 4 times and show message four times for each group of cells. In addition, the counter (cCount) within the message is not counting correctly. It seems double after removing 2nd group of shapes (cells). Please help. Thank you very much for your help and patience. "Joel" wrote: Here is a simple modification to handle all seleted areas. Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer For Each myRange In Selection.Areas myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count LastCol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, LastCol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & _ " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & _ ActiveSheet.Name & "'." Next myRange End Sub "Please Help" wrote: Joel, Thank you again for your patience and your continuing help. I think we are almost there. When I try your latest code, I got no error message, and it removed the shapes. However, it does not remove all the shapes. For example, if I selected the cells B4:E9, B17, E18 and H11. It removed the shapes only in the cells B4:E9. There were total of 9 shapes, and cells B4:B9 has 6 shapes. I also tried it by selecting 2 large range (F4:I11 and D16:F22) of cells (instead of one large range and individual cells like the first example), and it deleted the shapes in the first range. Based on the testing, the macro only removes the shapes in the first cell or first group of cells. The shapes of the rest of selected cells (selecting using the Ctrl key) do not get removed. Thanks. "Joel" wrote: I modified the code to work off selected cells Sub test() Dim cMarks As Shape Dim sCells As Range Dim cCount As Integer Set myRange = Selection myRows = myRange.Rows.Count LastRow = myRange.Row + myRows - 1 myCols = myRange.Columns.Count Lastcol = myRange.Column + myCols - 1 Set LastCell = Cells(LastRow, Lastcol) RLeft = myRange.Left RTop = myRange.Top RRight = LastCell.Left + LastCell.Width RBottom = LastCell.Top + LastCell.Width For Each cMarks In ActiveSheet.Shapes If cMarks.Type = 13 Then If cMarks.Top = RTop And _ cMarks.Top <= RBottom And _ cMarks.Left = RLeft And _ cMarks.Left <= RRight Then cCount = cCount + 1 cMarks.Delete End If End If Next cMarks MsgBox "You have removed " & cCount & " check marks in highlighted cells " _ & myRange.Address & "' of the Worksheet '" & ActiveSheet.Name & "'." End Sub "Please Help" wrote: Joel, The range is pretty much any cell on the sheet, not just A1 and E5. Meaning, we could have shapes in cells A1, E5, G100:I110, etc. From your post, you asked me to change the MsgBox. Does it mean the new one will not reference the cells where we are removing the shapes? Is it possible to reference them in the Msgbox? Thanks. "Joel" wrote: sCells is not defined. You are going to have to set the range by changint the A1 and E5. from MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." to MsgBox "You have removed " & cCount & " check marks in highlighted cells " & "' of the Worksheet '" & ActiveSheet.Name & "'." "Please Help" wrote: Joel, Thanks for your patience and your continuing help. I changed the code to ActiveSheet as you indicated below, and I am still getting the same error message. However, this time it pointed me to the MsgBox: MsgBox "You have removed " & cCount & " check marks in highlighted cells " '" & sCells.Name & "' of the Worksheet '" & ActiveSheet.Name & "'." Please help. Thanks. "Joel" wrote: from For Each cMarks In sCells.Shapes to For Each cMarks In ActiveSheet.Shapes "Please Help" wrote: Joel, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |