Using Pictures to return numeric values

[matt3542]

Dear Forum Members,

I am trying to achieve something rather abstract using VBA code and I'm not
entirely sure if its possible, hence any help would be greatly appreciated.
Instead of using numeric values in cells I have represented some data
pictorially (by pasting various .jpg's within cells) and would like to use
these pictures to calculate some numeric totals - I know its probably hard to
visualise how the spreadsheet looks so I have provided a summarised format
below;

Ranking (1 to 10)

Name 1 2 3 4 . . .

Name1 Pic1 Pic7 Pic1 Pic4 cell range
B5:K5
Name2 Pic2 Pic2 Pic2 Pic4
...
...

Totals Pic1 Pic2 Pic3 Pic4 . . . all
pics displayed,7 in total

CmdBtn_Name1* Total (2) Total (0) Total (0) Total (1) "Total" cell range
B18:H18
CmdBtn_Name2* Total (0) Total (3) Total (0) Total (1)

* Upon hitting each CmdBtn I would like the total number of each picture in
range B5:K5 to be displayed in the total cell range (B18:H18) and so forth.
Any help with the code for the CmdBtn's would be fantastic, Many thanks &
best wishes Matt

 

[Joel]

Pictures are not inside cells but placed ontop of the cells. both cells and
Pictures have these four proerties

1) Left
2) Top
3) Height
4) Width

To find which cells a pictre is located on isn't simple. You have to have a
few IF statements to figure this out.

I think a beeter approach is to put into the Picture Name the location and
the value like:

Pict_A_1_10
Pict_B_4_5
Pict_C_7_22

Then you can use code like this

Total = 0

for each pict on activesheet.shapes
"Get column Letter
Col_Letter = mid(pict.name,6)
'remove the rest of line after "_"
Col_Letter = left(Col_Letter,instr(Col_Letter,"_")-1)
RowNumber = mid(pict.name,6)
RowNumber = mid(RowNumber,instr(RowNumber,"_")+1)
Pict_Value = RowNumber
RowNumber = val(left(RowNumber,instr(RowNumber,"_")-1))
Pict_Value = val(mid(RowNumber,instr(RowNumber,"_")+1))

set Inter = intersect(Range(Col_Letter & RownUMBER),range ("B18:H18"))
if not Inter is nothing then
total = total + Pict_Value
end if
next pict

 

[matt3542]

Hi Joel, Many thanks for taking the time to help out, it still amazes me as
to just how altruistic people like yourselves are with regards to helping
others! I'm just about to finish work but I will reply first thing tomorrow
to let you know how I got on.

Cheers
Matt

 

[matt3542]

Good morning Joel,

I am finding it difficult getting this to work, without attaching the file
its quite hard for me to convey what the worksheet looks like which I think
is a big part of the problem. I have tried re-naming the picture name with
the location and value (I'm not sure the value has any relevance as every
instance of the picture should be counted as 1, the ranking part does not
apply) Also if I name each picture Pict how does the code differentiate
between the 7 different pictures. Furthermore would I put the code in the
click event of the CmDBtn or in a seperate module? Sorry I'm really confused
as quite new to VBA. It would seem a shame to waste this code because of
this, would you be willing to take a look at the file, I'm sure you would
know what the problem is just by looking at it in its proper format. If so is
it possible to attach an .xls or e-mail it to you directly as I'm really keen
to understand? Hope to hear back, many thanks, Matt