Hi guys n gals,
I have a nice, well working workbook which "pops" pictures into a cell
location based on a selection in other cells. I do this so I can delete
the previous pop then paste the photo there, and not have a bunch of them
stack up in the location.
I initially did this as a graphic lookup function for a DVD database,
and there were intended to be hundreds of pictures involved.

This new workbook only uses 6 fixed pictures, which call into the sheet
just fine. Since it is such a small number, I want to place them on the
sheet, then make my "pop" calls to them, instead of having to always
include the files.

Could someone please take a look or help?

There is the workbook and six png graphics to DL from he

The book
http://www.mediafire.com/view/?t8bxjn423ay0lo1


and six shots named pos_1.png thru pos_6.png

in the same listing as the above.

I'm not sure why you can't stack them in position, but if you did then
all you'd need to do is toggle their Visible property. To do this in my
projects I use a string representing the the 6 pics, then pass this to
a Toggle_PicsDisplay procedure that's targeted to those pics only...

The settings string"
"0,0,0,0,0,0" 'all hidden
"1,0,0,0,0,0" 'pic1 visible

...and the Toggle_PicsDisplay procedure also uses a string containing
the names of the 6 pics...

<air code
Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n)).Visible = CBool(vSettings(n))
Next
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sat, 26 Jan 2013 15:10:21 -0500, GS wrote:

I'm not sure why you can't stack them in position, but if you did then
all you'd need to do is toggle their Visible property. To do this in my
projects I use a string representing the the 6 pics, then pass this to
a Toggle_PicsDisplay procedure that's targeted to those pics only...

The settings string"
"0,0,0,0,0,0" 'all hidden
"1,0,0,0,0,0" 'pic1 visible

..and the Toggle_PicsDisplay procedure also uses a string containing
the names of the 6 pics...

<air code
Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n)).Visible = CBool(vSettings(n))
Next
End Sub


That looks good as internally pasted pics get their bitmap space
assigned an internally assigned "picture number".

I will likely gain knowledge of how to make a call to such a pic from
your code, but simply putting all 6 in all three locations and turning
"on" (viability wise) only the one that the lookup code points to in the
three locations. even though the pics would be the same, it might be
easier to make three sets of uniquely named stacked picks, so 18 pictures
in 3 stacks of 6.

I'll work through some ideas. Thanks for the snippit and concept.

I was looking at text effects in comments, but they do not copy and
paste correctly either. The pic and comment pastes, but the comment does
not stay centered the same way as in the cell it gets copied from.

Thanks for your help. I will give feedback soon.
Did you look at my sheet?

On Sat, 26 Jan 2013 15:10:21 -0500, GS wrote:

I'm not sure why you can't stack them in position, but if you did then
all you'd need to do is toggle their Visible property. To do this in my
projects I use a string representing the the 6 pics, then pass this to
a Toggle_PicsDisplay procedure that's targeted to those pics only...

The settings string"
"0,0,0,0,0,0" 'all hidden
"1,0,0,0,0,0" 'pic1 visible

..and the Toggle_PicsDisplay procedure also uses a string containing
the names of the 6 pics...

<air code
Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n)).Visible = CBool(vSettings(n))
Next
End Sub


When I put this in my VB editor, it does not show up on the list of
macros available, so I cannot assign a button to it?

On Sat, 26 Jan 2013 15:10:21 -0500, GS wrote:

I'm not sure why you can't stack them in position, but if you did then
all you'd need to do is toggle their Visible property. To do this in my
projects I use a string representing the the 6 pics, then pass this to
a Toggle_PicsDisplay procedure that's targeted to those pics only...

The settings string"
"0,0,0,0,0,0" 'all hidden
"1,0,0,0,0,0" 'pic1 visible

..and the Toggle_PicsDisplay procedure also uses a string containing
the names of the 6 pics...

<air code
Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n)).Visible = CBool(vSettings(n))
Next
End Sub


Please also see my other responses, but this is the most recent.

I just also found out that I can do this with a simple "bring to front"
command, or step through a few "send to rear" commands until the desired
image is on top, as it obscures the others without turning off their
"visible' attribute.

There may be a series of code segments to operate this method and it
will likely be more code and more complicated.

Shame I cannot change that attribute with a simple function call, as
since they are already in place, I should be able to bring one to the
front based on a cell value and not need to invoke any code.

Maybe I need to author a custom function.

There would seemingly be a way to do this without VB. Any thoughts?

All the pics are exactly the same pixel array size.

You should be able to see the sub if you put it in a standard module.

I didn't realize there are 3 sets of pics and so...

Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant, vSet As Variant
Const sPicSets$ = "a,b,c"

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For Each vSet in sPicSets
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n) & vSet).Visible = _
CBool(vSettings(n))
Next
Next
End Sub

...where the pics are named according to location a, b, or c. Otherwise,
they all have the same name but just append the location identifier.
Optionally, you could pass sPicSets to the procedure if you wanted to
specify individual sets independant of the others...

Sub Toggle_PicsDisplay(sSettings$, Optional sPicSets)
Dim n%, vPicNames As Variant, vSettings As Variant, vSet As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
If sPicSets = "" Then sPicSets = "a,b,c"

For Each vSet in sPicSets
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n) & vSet).Visible = _
CBool(vSettings(n))
Next
Next
End Sub

...where it will process 1, 2, or 3 sets according to what you pass to
it in the sPicSets arg.
===============


I find toggling the Visible property is easier to code for than using
order (front/rear)!

You can control the execution in the Worksheet_Change event so you can
monitor activity in the cells that trigger pics display.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sun, 27 Jan 2013 02:23:04 -0500, GS wrote:

You should be able to see the sub if you put it in a standard module.

I didn't realize there are 3 sets of pics and so...

Sub Toggle_PicsDisplay(sSettings$)
Dim n%, vPicNames As Variant, vSettings As Variant, vSet As Variant
Const sPicSets$ = "a,b,c"

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
vSettings = Split(sSettings, ",")
For Each vSet in sPicSets
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n) & vSet).Visible = _
CBool(vSettings(n))
Next
Next
End Sub

..where the pics are named according to location a, b, or c. Otherwise,
they all have the same name but just append the location identifier.
Optionally, you could pass sPicSets to the procedure if you wanted to
specify individual sets independant of the others...

Sub Toggle_PicsDisplay(sSettings$, Optional sPicSets)
Dim n%, vPicNames As Variant, vSettings As Variant, vSet As Variant

vPicNames = Array("Pic1","Pic2","Pic3","Pic4","Pic5","Pic6")
If sPicSets = "" Then sPicSets = "a,b,c"

For Each vSet in sPicSets
For n = LBound(vPicNames) To UBound(vPicNames)
ActiveSheet.Shapes(vPicNames(n) & vSet).Visible = _
CBool(vSettings(n))
Next
Next
End Sub

..where it will process 1, 2, or 3 sets according to what you pass to
it in the sPicSets arg.
===============


I find toggling the Visible property is easier to code for than using
order (front/rear)!

You can control the execution in the Worksheet_Change event so you can
monitor activity in the cells that trigger pics display.


I have some working code that pops an external file into the location.

I should be able to modify this to make a call to an already placed
image by using the internal image naming structures, no?

Like "Picture 56" instead of an actual filename.

But I do not know how to make such a modification.

Can you see where I would edit this script to call an embedded
"picture #" instead of an external file?

Code follows:

Sub InsertPicture(PictureFileName As String, TargetCells As Range,
picName As String)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

ActiveSheet.Pictures("Picture 56")...

Then you can set it size/position like this...

With ActiveSheet.Pictures("Picture 56")
.Top = TargetCells.Top: .Left = TargetCells.Left
.Width = TargetCells.Offset(0, TargetCells.Columns.Count).Left -
..left
.Height = TargetCells.Offset(TargetCells.Rows.Count, 0).Top - .Top
End With

It's still my opinion to stack in position and toggle visibility!

I haven't got a clue what the heck you're doing with width/height! I'd
use fixed values normally, OR use a fixed size frame so the pics
autofit it. (Just a preference)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sun, 27 Jan 2013 17:57:20 -0500, GS wrote:

ActiveSheet.Pictures("Picture 56")...

Then you can set it size/position like this...

With ActiveSheet.Pictures("Picture 56")
.Top = TargetCells.Top: .Left = TargetCells.Left
.Width = TargetCells.Offset(0, TargetCells.Columns.Count).Left -
.left
.Height = TargetCells.Offset(TargetCells.Rows.Count, 0).Top - .Top
End With

It's still my opinion to stack in position and toggle visibility!

I haven't got a clue what the heck you're doing with width/height! I'd
use fixed values normally, OR use a fixed size frame so the pics
autofit it. (Just a preference)


The code was originally to paste DVD pics into a set range and it was
for rescaling, I think (it also was borrowed code).

But yeah, I can stack them, I simply want to refrain from making the
external call or being required to provide the externally called files
and their required locations (via the code calls)

so, I will try this out, and if it works, I can stop using the external
files.

On my DVD database, this was not a option, since the image 'database'
would make for a HUGE excel file.


Thanks for your help.

CellShocked formulated the question :
On Sun, 27 Jan 2013 17:57:20 -0500, GS wrote:

ActiveSheet.Pictures("Picture 56")...

Then you can set it size/position like this...

With ActiveSheet.Pictures("Picture 56")
.Top = TargetCells.Top: .Left = TargetCells.Left
.Width = TargetCells.Offset(0, TargetCells.Columns.Count).Left -
.left
.Height = TargetCells.Offset(TargetCells.Rows.Count, 0).Top - .Top
End With

It's still my opinion to stack in position and toggle visibility!

I haven't got a clue what the heck you're doing with width/height! I'd
use fixed values normally, OR use a fixed size frame so the pics
autofit it. (Just a preference)


The code was originally to paste DVD pics into a set range and it was
for rescaling, I think (it also was borrowed code).

But yeah, I can stack them, I simply want to refrain from making the
external call or being required to provide the externally called files
and their required locations (via the code calls)

so, I will try this out, and if it works, I can stop using the external
files.

On my DVD database, this was not a option, since the image 'database'
would make for a HUGE excel file.


Thanks for your help.

It would serve you well to use something like PaintShop Pro to resize
and convert to GIF so the size of your Excel file isn't humongous.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion