Sorting by picture (Not sorting with picture)...

[WTW_4]

I found a table on the Internet with some information that is useful to me. The table consists of about 700+ rows of data with seven columns. For each row, five of those columns contains either a checkmark or a dollar sign, or nothing at all.

I copied this table and pasted it into Excel 2007. I am able to sort the table by the data in the two columns that do not contain checkmarks or dollar signs, but would like to be able to sort the table by the columns with the checkmarks and dollars signs such that the rows with the checkmarks are grouped together and the rows with the dollar signs are grouped together.

Unfortunately, the checkmarks and dollar signs that were used in the table are images of checkmarks and dollar signs. When I attempt to sort the table by the five columns with the checkmarks and dollar signs nothing happens. The sort function behaves as though there was nothing at all in the cells even if the cell 'contains' an image of a checkmark or a dollar sign.

Within the "Sort" dialogue box I can select what I want to sort. My choices are:
Values
Cell Color
Font Color
Cell Icon - (I'm not sure what this is, but I tried sorting on it to no effect.)

Too bad there's not an option to sort on some property that is associated with images that are 'attached' to the cell.

I spent some time searching for a solution to this problem and found many articles explaining how to cause images to sort along with data. That seems to be working just fine. I need to sort by the images themselves, or at least by some property that is associated with the images.

This isn't the first time I've encountered this problem as I have often found some table or other on the Internet with interesting data and have had the same problem. Does anyone know of a way to do this?

Thanks much...

 

[WTW_4]

Edit: I misread. Yes that would be good to add for all shapes.

Do you mean that the line:

shp.Placement = xlMove

...should be used in place of, or in addition to, one of the lines in the above program? Thanks...

 

[Jerry Sullivan]

Do you mean that the line:

shp.Placement = xlMove

...should be used in place of, or in addition to, one of the lines in the above program? Thanks...

I'm not sure whether Mike's suggestion was intended as an addition to my code or an alternative.

If I'm understanding your OP description of the problem, you are already able to sort by the two fields that have cell values and have the shapes sorted with those rows. So your shapes are already set to move with the cells. You want add the ability to sort by the fields with shapes.

If you are going to delete the shapes and replace them with values in those cells, then there wouldn't be any benefit to adding that statement. You could try the code I suggested in Post #8 with no modifications.

If you want to retain the shapes and add values to those cells, then it would be a good idea to set the shapes to move with the cells. Adding that here should work...

   If Len(sChar) Then
      With shp.TopLeftCell
         If Len(.Value) Then
            MsgBox "Existing value will not be replaced in cell: " _
               & .Address
         Else
            .Value = sChar
            '--optional: either delete shape or set it to move with cell
            '     delete or comment out one of the two statements below
            shp.Placement = xlMove
            '''  shp.Delete  'not used
         End If
      End With
   End If

 

[WTW_4]

You are correct; when I sort the table by the two rows that contain data the images are sorted along with the cells to which they are attached. When I spot checked the properties for a few of the images the "Move but don't size with cells" attribute was already turned on.

I have no need to retain the images after they have been 'converted' into cell values. I ran the code you provided in post # 8 and it worked perfectly!! Very impressive. Thank you very much for your help on this! Man... I need to learn VB.

As I said in my OP when I searched for help on this problem I found lots of posts by people who were trying to get the graphics to sort along with the data, but nothing for anyone trying to sort 'by' the graphics. I seem to encounter this problem just about anytime I copy a table from the Internet; I'm surprised I couldn't find posts by other people trying to do something similar.

Thanks again....!!