View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.programming
Peter T Peter T is offline
external usenet poster
 
Posts: 5,600
Default Using Pictures to return numeric totals

Yes, all you should need to do is change
Set rng = Range("B2:H20")

to
Set rng = Range("B2:K9")

and one more thing, I threw following in as an optional extra as picture
name headers

With Range("h1")
For i = 1 To 7
.Offset(, i) = "Pic" & i
Next
End With


either delete it altogether or change
With Range("h1")

to
With Range("K1")

Another minor thing entirely up to you, if you want totals that = zero to
appear as 0 change
ReDim picCnt(1 To rng.Rows.Count, 1 To 7)

to
ReDim picCnt(1 To rng.Rows.Count, 1 To 7) as Long

but if you want zeros as blanks leave it as is

Regards,
Peter T


"matt3542" wrote in message
...
Hi Peter,

Sorry to intrude on you again, just a quick question if I may..the range
of
cells that have pictures on top of them is B2:K9 (to allow for 8
employees,
each having 10 pictures I thought it would be a simple case of changing
the
range ( but the code does not run as before, can
you please let me know where I am going wrong? Many thanks, matt

"Peter T" wrote:

That was one of my guesses early on, pictures with duplicate names (not
unique!). Although it's easy enough to name multiple objects with
duplicate
names with code I'm curious as to how you did that manually. If I do try
and
do that in the names box, entering a duplicate name selects the object
that
already has that name. Oh well, somehow you did it. Have a go with the
following.

As written should fill I2:O20 with totals of your pictures named Pic1,
Pic2..Pic7 that exist anywhere in each of the 19 rows B2:H20 on a per row
basis.

Sub CountPics()
Dim i As Long, n As Long
Dim rw0 As Long
Dim rng As Range, rTL As Range
Dim pic As Picture, pics As Pictures

With Range("h1")
For i = 1 To 7
.Offset(, i) = "Pic" & i
Next
End With

Set rng = Range("B2:H20")
rw0 = rng.Row - 1

ReDim picCnt(1 To rng.Rows.Count, 1 To 7)

Set pics = ActiveSheet.Pictures

For i = 1 To pics.Count
With pics(i)
If .Name Like "Pic#" Then
n = 0
n = Val(Mid(.Name, 4, 1))
If n Then
Set rTL = .TopLeftCell
If Not Intersect(rng, rTL) Is Nothing Then
picCnt(rTL.Row - rw0, n) = picCnt(rTL.Row - rw0, n) + 1
End If
End If
End If
End With
Next

rng.Offset(, rng.Columns.Count).Resize(, 7).Value = picCnt
End Sub

Regards,
Peter T


"matt3542" wrote in message
...
Hi Peter,

I had not given them unique names I had given each picture the same
name,
i.e All dogs named Dog1, all cats named Cat1..Does this help?

"Peter T" wrote:

I had understood almost everything below but still stuck on the same
thing
since the beginning -

For ease of use lets say - Employee A (A1) Has 10 Pic1's placed
on
top
of adj cells

What is the unique name for each of your 10 Pic1's

Have you named them along the lines of Andy's example of Dog1, Dog2
etc
or
my similar example of PicA_001, PicA_002 etc.

Regards,
Peter T


"matt3542" wrote in message
...
Thank you for your patience Peter,

I have a list of employee names in ColA and each employee row has 10
icon
size pictures to the right of the employee name placed on top of 10
respective cells. There are 7 possible pictures linked to a key
which
explains what each represent. Each picture has been given a unique
name
in
the white Picture Name box in the upper left corner of the worksheet
so
there
are 7 Picture names from Pic1 to Pic7

For ease of use lets say - Employee A (A1) Has 10 Pic1's placed
on
top
of adj cells

Under the list of employee names I have a command button for each
employee.
When a user clicks the button I would like cells to the left to
populate
with
the totals for the number of times each picture appears in the table
above.
There should be 7 individual totals that combined total 10. In the
above
e.g
it would return 10 0 0 0 0 0 0 in 7 seperate cells to the left of
the
button.

I'm really sorry if you still do not follow, it must be hard for you
to
understand what I'm trying to convey without seeing the datasheet,
shame I
can't send you a copy?! Any thoughts, even if its to suggest
scapping
the
idea would be most welcome! Thanks, Matt


"Peter T" wrote:

So you have 7 possible pictures, some may appear more than once,
each
has
a
unique name. I'm still confused.

I have all sorts of guesses as to what you might mean but could you
have
another go at clarifying

Regards,
Peter T

"matt3542" wrote in message
...
Hi Peter, apologies for the confusion, perhaps frequency was the
wrong
way
of
referencing what I meant. There are 10 cells for any given
employee
entity
with 7 possible pictures (obviously some will appear more than
once)
and
all
the pictures have been given a unique name via the name box.
Before
pasting
the pictures into the cells I made sure each cell was selected
and I
have
not
re-positioned manually. Admittedly I had not thought of using the
picture
type fonts because the type of pictures I have used have a
relevance
to
what
is being conveyed. I hope this clarifies things a little better,
hope
to
hear
back from you. Thanks, Matt

"Peter T" wrote:

What do you mean by pictures can appear in any frequency. Whilst
there
may
be several copies of a particular picture on a sheet, each name
will
be
different, which means they are in effect different pictures
which
happen
to
look the same. (There is an almost accidental way to have
pictures
with
duplicate names but that only follows a certain scenario.

You could perhaps name similar pictures something like
PicA_001.pic, PicA_002.pic
then you'd know that pictures with prefix "PicA_" are equivalent
pictures

Secondly, pictures do not "belong" to cells though top-left &
bottom-right
cell locations are easily returned. If for your idea to have any
chance
of
working you'd need to be sure that say a picture's top-left
corner
really
is
located within your cell, not say slightly above or to the left.
Easy
for
a
picture to get nudged slightly off the cell you think it's in.

Subject to clarifying the above, something along the lines of
what
you
are
asking might be possible. Have you considered working with one
of
the
various picture type fonts which might lead to an equivalent
solution
much
more easily (just type in a letter), quickly (in terms of
processing)
and
not least reliably (no ambiguity about which character(s) exist
in
which
cells).

Regards,
Peter T

"matt3542" wrote in message
...
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 on top of cells) and
would
like
to
use a command button to determine the total number of times
the
picture
occurs for a given employee and display the results in cells
B18:H18*

Jbloggs1 Pic1(B5) Pic2(C5) Pic3(D5) Pic4(E5) Pic5(F5)..to
Pic7(K5)

CmdBtnJbloggs1 *TotalPic1 TotalPic2 TotalPic3 TotalPic4...

There are 7 pics that can appear in any order/frequency up to
a
maximum
of
10 times on top of cells B5:K5. I have also changed the name
of
the
pics
to
those given above. Does anyone know if this is possible? Any
help
would
be
fantastic, Many thanks, Matt