Excel VBA: USERFORM simulate button click on image

Hi all.

VBA has the ugliest UI options, no news to us there. I am looking to create rounded buttons. And the only way to solve this without getting hardcore into API is using shapes (spreadsheet) or images (userform).

Thats great... If you want your button to not actuate. At that point i would rather just use an ugly Windows 95 button than a shape that doesn't... click. The point of a button is to actuate. To press and depress.

One source provided a great method for shape buttons (worksheets), where he flashes the button on click. Though I haven't tried it personally.
See here: https://wellsr.com/vba/2015/excel/si...ctangle-shape/


Still, that doesn't solve the issue with forms because shapes are not a form control. Cool, we can use an image. The button will actuate with an image on it. The problem here is the truly genius software designers at Microsoft decided to not allow a transparent border command button border property. Actually, there is no 'border' property at all. So now this entire method is a waste. Regardless of how much time you take to design the perfect button with your favorite online button generator or in photoshop, your time will be wasted.

Which leaves me to resulting to the forum for questions... Has anyone been able to design some sort of sequence using mutiple images where a series of custom buttons (userform image control) simulates a single button pressed and depressed? This would be the userform/image equivalent to what the guy has done above. If not, where might i start? I am good with photoshop and graphic design so no issues there.


Thank you

.
This may be one way of accomplishing the goal :

Private Sub ToggleButton1_Click()
 
 Dim name As String
 
 If ToggleButton1.Value = True Then
     Label1.Caption = "GO Clicked"
    name = "Picture 1"
 ElseIf ToggleButton1.Value = False Then
    Label1.Caption = "GO Unclicked"
    name = "Picture 2"
 End If

    Worksheets("Sheet1").Shapes(name).CopyPicture xlScreen, xlBitmap
    Set ToggleButton1.Picture = PastePicture(xlBitmap)
End Sub

There is more code within the workbook that is too lengthy to post here.

.
Some other idea :

https://www.youtube.com/watch?v=UTFnERi9T30

Logit,

Thanks for the reply. The example workbook isn't what I am looking for. It still has a border around the image because its an image placed on a button.

As for the video, he doesn't explain anything about actually creating a button with images/code. He just messed with those sheep images.

I've made a quick attempt with:

Private Sub Image2_Click()
Image2.SpecialEffect = fmSpecialEffectFlat
End Sub

But i don't really know how to change it back to fm_specialEffectRaised or whatever when the mouse click is released. Is there really not an event for mouse pointer being held?

Welcoming more ideas for anyone who has attempted to make a button work just like a command button from an image.

Thanks for your help.

.
Other suggestions :

https://wellsr.com/vba/2015/excel/si...ctangle-shape/



Here is something else :

Sub TogglePicSize()
    Dim shp As Shape
     Set shp = ActiveSheet.Shapes(Picture2)
    With shp
        .LockAspectRatio = msoTrue
        If .Width = 75 Then .Width = 80 Else .Width = 75
    End With
End Sub

Maybe you can edit the image to meet your expectations.

Originally Posted by rorya

A couple of small observations:
1. I bet that if I picked apart a program you made 20 years ago I could probably make fun of your decisions too with the benefit of a lot of hindsight. (Now if you want to mock their decision not to support VBA properly, I'll join you. )

2. I personally (and I know this is true of others) am put off answering questions by people who complain about the tools all the time. Just something you may want to consider. Or not. Entirely up to you. But I pretty much guarantee those programmers were smarter than you or me.

Oh, no dispute there on your guarantee they are and were smarter than us. I mean, office developers probably don't need to rely on the Excel Forum for questions on basic programming tasks like I do

However, mocking their lack of support is no more right, wrong, or indifferent than complaints of their (lack of) tools. Regardless, both aren't suited for this thread. I'll try to bite my tongue in the future.

Thanks

Hello Everyone,

I know this thread is a few years old but I also needed an Image on a Userform to appear as a button.

This is how I solved it.

Firstly, when you create the Image on the Userform, in the properties window of the Image, set the SpecialEffect to 'fmSpecialEffectRaised'

Next simulate the click

Private Sub Image1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Me.Image1.SpecialEffect = fmSpecialEffectFlat
Me.Image1.BorderStyle = fmBorderStyleSingle

End Sub

Private Sub Image1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Me.Image1.SpecialEffect = fmSpecialEffectRaised

End Sub

Finally, the actual Click event

Private Sub Image1_Click()

Debug.Print "Image Clicked"

End Sub

Jay, I'm not sure exactly how your code would work since there is no Click event for an image. Care to elaborate?

Hi DanNatCorning, agreed there is no Click event available from the drop down menu, but if you manually type it, it works perfectly.