Hi all,

I want to change the images on a button or other object suitable for a
control. I neet to create a three or four state "button" and I think
that a changing icon image is the best way.

I have seen where vba can be used to load an image off of storage...
but that seems foolish given that I will have many buttons... and they
will be constantly/regularly changing.

Is there a way that I can somehow imbed the image into my worksheet
and then reference to that so that the image source remains local to
the worksheet? This will of course make the worksheet far more
portable as opposed to breaking if someone not on my network tries to
use it.

Code snippits appreciated!

Thank you so much.

-Ryder-

I'm not sure what you're doing, but you can use pictures (insert|pictures|from
file) directly on the worksheet and assign them each the same macro.

If you name the pictures consistently, you can just hide the ones you don't want
and show the next in the sequence.

For instance, I put 4 pictures in the same location and named them:

PicA_01
PicA_02
PicA_03
PicA_04

And then I assigned them all this macro:

Option Explicit
Sub testme()
Dim WhichOneIsVisible As Long
Dim pCtr As Long
Dim myPict As Picture
Dim myPrefix As String
Dim UnderScorePos As Long

Set myPict = ActiveSheet.Pictures(Application.Caller)
UnderScorePos = InStr(1, myPict.Name, "_", vbTextCompare)

If UnderScorePos = 0 Then
MsgBox "Design error!" & vbLf & "Contact Ryder at xxxxxx"
Exit Sub
End If

myPrefix = Left(myPict.Name, UnderScorePos - 1)
WhichOneIsVisible = Val(Mid(myPict.Name, UnderScorePos + 1))

'your code that does the real work here
'even if it's just a bunch of calls


'hide the old picture
myPict.Visible = False

'determine the next one that should be visible
Set myPict = Nothing
On Error Resume Next
Set myPict = ActiveSheet.Pictures(myPrefix & "_" _
& Format(WhichOneIsVisible + 1, "00"))
On Error GoTo 0

'show the next in the sequence
If myPict Is Nothing Then
'at the last one of the group so show the first
ActiveSheet.Pictures(myPrefix & "_01").Visible = True
Else
myPict.Visible = True
End If

End Sub

Powell" wrote:

Hi all,

I want to change the images on a button or other object suitable for a
control. I neet to create a three or four state "button" and I think
that a changing icon image is the best way.

I have seen where vba can be used to load an image off of storage...
but that seems foolish given that I will have many buttons... and they
will be constantly/regularly changing.

Is there a way that I can somehow imbed the image into my worksheet
and then reference to that so that the image source remains local to
the worksheet? This will of course make the worksheet far more
portable as opposed to breaking if someone not on my network tries to
use it.

Code snippits appreciated!

Thank you so much.

-Ryder-

--

Dave Peterson

Hi Dave,

Yes, I thought of that... but I have maybe 20 buttons... and I don't
want to have 20x4 buttons... I think I would get carpal tunnel just
setting up 80 buttons for something that should be easy to do.... just
by changing the image on the control... instead of playing visibility
tricks on them.

I noticed that by examining: Me.CommandButton1.Picture I see
some number... perhaps it is a pointer to an image that I can exploit?

I played around with it withoug success.

Any idea what this is and how I might exploit it?

Thanks!

-RS-


On Jul 29, 6:52*pm, Dave Peterson wrote:
I'm not sure what you're doing, but you can use pictures (insert|pictures|from
file) directly on the worksheet and assign them each the same macro.

If you name the pictures consistently, you can just hide the ones you don't want
and show the next in the sequence.

For instance, I put 4 pictures in the same location and named them:

PicA_01
PicA_02
PicA_03
PicA_04

And then I assigned them all this macro:

Option Explicit
Sub testme()
* * Dim WhichOneIsVisible As Long
* * Dim pCtr As Long
* * Dim myPict As Picture
* * Dim myPrefix As String
* * Dim UnderScorePos As Long

* * Set myPict = ActiveSheet.Pictures(Application.Caller)
* * UnderScorePos = InStr(1, myPict.Name, "_", vbTextCompare)

* * If UnderScorePos = 0 Then
* * * * MsgBox "Design error!" & vbLf & "Contact Ryder at xxxxxx"
* * * * Exit Sub
* * End If

* * myPrefix = Left(myPict.Name, UnderScorePos - 1)
* * WhichOneIsVisible = Val(Mid(myPict.Name, UnderScorePos + 1))

* * 'your code that does the real work here
* * 'even if it's just a bunch of calls

* * 'hide the old picture
* * myPict.Visible = False

* * 'determine the next one that should be visible
* * Set myPict = Nothing
* * On Error Resume Next
* * Set myPict = ActiveSheet.Pictures(myPrefix & "_" _
* * * * * * * * * * *& Format(WhichOneIsVisible + 1, "00"))
* * On Error GoTo 0

* * 'show the next in the sequence
* * If myPict Is Nothing Then
* * * * 'at the last one of the group so show the first
* * * * ActiveSheet.Pictures(myPrefix & "_01").Visible = True
* * Else
* * * * myPict.Visible = True
* * End If

End Sub





Powell" wrote:

Hi all,

I want to change the images on a button or other object suitable for a
control. *I neet to create a three or four state "button" and I think
that a changing icon image is the best way.

I have seen where vba can be used to load an image off of storage...
but that seems foolish given that I will have many buttons... and they
will be constantly/regularly changing.

Is there a way that I can somehow imbed the image into my worksheet
and then reference to that so that the image source remains local to
the worksheet? *This will of course make the worksheet far more
portable as opposed to breaking if someone not on my network tries to
use it.

Code snippits appreciated!

Thank you so much.

-Ryder-

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Oh! I did find something that works!

I made 4 additional buttons that have the images on them that I want.
I can then set any OTHER button to those buttons:


Private Sub CommandButton1_Click()
Me.CommandButton3.Picture = PlaceholderButton1.Picture
End Sub

From there, it should be easy to make a 4-way toggle...

-RS-


On Jul 30, 9:46*am, Ryder S wrote:
Hi Dave,

Yes, I thought of that... but I have maybe 20 buttons... and I don't
want to have 20x4 buttons... I think I would get carpal tunnel just
setting up 80 buttons for something that should be easy to do.... just
by changing the image on the control... instead of playing visibility
tricks on them.

I noticed that by examining: * * Me.CommandButton1.Picture * I see
some number... perhaps it is a pointer to an image that I can exploit?

I played around with it withoug success.

Any idea what this is and how I might exploit it?

Thanks!

-RS-

On Jul 29, 6:52*pm, Dave Peterson wrote:



I'm not sure what you're doing, but you can use pictures (insert|pictures|from
file) directly on the worksheet and assign them each the same macro.

If you name the pictures consistently, you can just hide the ones you don't want
and show the next in the sequence.

For instance, I put 4 pictures in the same location and named them:

PicA_01
PicA_02
PicA_03
PicA_04

And then I assigned them all this macro:

Option Explicit
Sub testme()
* * Dim WhichOneIsVisible As Long
* * Dim pCtr As Long
* * Dim myPict As Picture
* * Dim myPrefix As String
* * Dim UnderScorePos As Long

* * Set myPict = ActiveSheet.Pictures(Application.Caller)
* * UnderScorePos = InStr(1, myPict.Name, "_", vbTextCompare)

* * If UnderScorePos = 0 Then
* * * * MsgBox "Design error!" & vbLf & "Contact Ryder at xxxxxx"
* * * * Exit Sub
* * End If

* * myPrefix = Left(myPict.Name, UnderScorePos - 1)
* * WhichOneIsVisible = Val(Mid(myPict.Name, UnderScorePos + 1))

* * 'your code that does the real work here
* * 'even if it's just a bunch of calls

* * 'hide the old picture
* * myPict.Visible = False

* * 'determine the next one that should be visible
* * Set myPict = Nothing
* * On Error Resume Next
* * Set myPict = ActiveSheet.Pictures(myPrefix & "_" _
* * * * * * * * * * *& Format(WhichOneIsVisible + 1, "00"))
* * On Error GoTo 0

* * 'show the next in the sequence
* * If myPict Is Nothing Then
* * * * 'at the last one of the group so show the first
* * * * ActiveSheet.Pictures(myPrefix & "_01").Visible = True
* * Else
* * * * myPict.Visible = True
* * End If

End Sub

Powell" wrote:

Hi all,

I want to change the images on a button or other object suitable for a
control. *I neet to create a three or four state "button" and I think
that a changing icon image is the best way.

I have seen where vba can be used to load an image off of storage...
but that seems foolish given that I will have many buttons... and they
will be constantly/regularly changing.

Is there a way that I can somehow imbed the image into my worksheet
and then reference to that so that the image source remains local to
the worksheet? *This will of course make the worksheet far more
portable as opposed to breaking if someone not on my network tries to
use it.

Code snippits appreciated!

Thank you so much.

-Ryder-

--

Dave Peterson- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -