SOLVED

Running VBA code after clicking on a UserForm 'image'

%3CLINGO-SUB%20id%3D%22lingo-sub-2292371%22%20slang%3D%22en-US%22%3ERunning%20VBA%20code%20after%20clicking%20on%20a%20UserForm%20'image'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2292371%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20currently%20trying%20to%20write%20some%20VBA%20code%20that%20will%20initialize%20once%20I%20left%20click%20on%20a%20UserForm%20image%20I%20have%20uploaded.%20In%20my%20specific%20situation%2C%20I%20am%20using%20a%20hovering%20effect%20for%20a%20'Login'%20button%20that%20will%20highlight%20a%20white%20'Login'%20button%2Fimage%20green%20when%20hovered%20over%20with%20the%20mouse.%20This%20is%20done%20by%20hiding%20the%20white%20image%20and%20only%20showing%20the%20green%20image.%20See%20below%2C%20where%20I%20just%20have%20the%20white%20image%20over%20top%20the%20green%20one.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ryanbarton324_1-1619446327598.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275586i8285ED96429FFBCB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ryanbarton324_1-1619446327598.png%22%20alt%3D%22ryanbarton324_1-1619446327598.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20part%20works%20fine%20right%20now%2C%20but%20the%20goal%20after%20this%20green%20highlight%20is%20to%20select%20the%20green%20button%20with%20the%20mouse%2C%20and%20after%20left%20clicking%2C%20some%20code%20will%20run.%20This%20is%20not%20working%20right%20now.%20I%20looked%20up%20on%20other%20forums%20and%20it%20looked%20like%20all%20I%20needed%20to%20do%20was%20have%20the%20code%20run%20with%20a%20'_Click()'%20sub%20in%20the%20code%2C%20but%20when%20I%20run%20a%20_Click%20for%20the%20image%2C%20an%20error%20returns%20saying%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ryanbarton324_0-1619446248533.png%22%20style%3D%22width%3A%20349px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275585iF2ADD6AF2AF059FB%2Fimage-dimensions%2F349x143%3Fv%3Dv2%22%20width%3D%22349%22%20height%3D%22143%22%20role%3D%22button%22%20title%3D%22ryanbarton324_0-1619446248533.png%22%20alt%3D%22ryanbarton324_0-1619446248533.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhenever%20I%20right%20click%20on%20the%20image%20itself%20and%20do%20'View%20Code'%2C%20it%20creates%20a%20new%20Private%20Sub%20for%20'_BeforeDragOver()'%20and%20when%20I%20look%20in%20the%20top%20right%20drop%20down%20menu%2C%20there%20is%20no%20option%20for%20'Click'.%20When%20I%20have%20the%20below%20code%2C%20the%20same%20error%20from%20above%20is%20returned.%20And%20when%20using%20the%20'_BeforeDragOver()'%20created%20Sub%2C%20nothing%20would%20happen%20when%20I%20do%20anything%20with%20the%20image.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ryanbarton324_2-1619446567571.png%22%20style%3D%22width%3A%20919px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275587i708AB5DF8EB6C8BA%2Fimage-dimensions%2F919x80%3Fv%3Dv2%22%20width%3D%22919%22%20height%3D%2280%22%20role%3D%22button%22%20title%3D%22ryanbarton324_2-1619446567571.png%22%20alt%3D%22ryanbarton324_2-1619446567571.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20new%20way%20to%20do%20this%20in%20the%20most%20recent%20Excel%3F%20Or%20is%20this%20not%20feasible%20anymore%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20other%20forums%20seemed%20to%20point%20to%20using%20a%20_Click%20sub%20for%20the%20image%20but%20I%20can't%20seem%20to%20make%20it%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERyan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2292371%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2292492%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20VBA%20code%20after%20clicking%20on%20a%20UserForm%20'image'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2292492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036947%22%20target%3D%22_blank%22%3E%40ryanbarton324%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20Image%20control%20does%20not%20have%20a%20click%20event.%20you%20can%20use%20the%20MouseUp%20(or%20MouseDown)%20event%20instead%2C%20with%20syntax%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Image1_MouseUp(ByVal%20Button%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20X%20As%20Single%2C%20ByVal%20Y%20As%20Single)%0A%20%20%20%20'%20your%20code%20here%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewith%20the%20name%20of%20your%20control%20instead%20of%20Image1%2C%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293797%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20VBA%20code%20after%20clicking%20on%20a%20UserForm%20'image'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply!%20Very%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20did%20work%20for%20me%20to%20be%20able%20to%20click%20on%20the%20button%20after%20the%20highlight.%20Now%2C%20I%20have%20a%20different%20problem%2C%20hopefully%20minor.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20code%20that%20will%20run%20when%20the%20MouseUp%20sub%20is%20run%20after%20I%20left%20click%20on%20the%20'Login'%20button%2C%20and%20it%20should%20be%20checking%20the%20login%20credentials%20and%20verifying%20that%20they%20are%20correct%20with%20a%20username%20and%20password.%20If%20the%20username%20and%2For%20password%20is%20incorrect%2C%20it%20will%20return%20a%20message%20saying%20'ATTENTION%3A%20Wrong%20Login%20Information'.%20If%20not%2C%20the%20main%20menu%20UserForm%20will%20pop%20up%20and%20the%20login%20form%20will%20be%20unloaded.%20See%20code%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20OKButton_MouseUp(ByVal%20Button%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20X%20As%20Single%2C%20ByVal%20Y%20As%20Single)%0A%0AIf%20Me.Username.Value%20%3D%20%22fhwa%22%20Then%0AIf%20Me.Password.Value%20%3D%20%221234%22%20Then%0AUnload%20Me%0AfrmMainMenu.Show%0A%20%20%20%20%20%20%20%20Exit%20Sub%0A%20%20%20%20End%20If%0AEnd%20If%0AMsgBox%20%22ATTENTION%3A%20Wrong%20Login%20Information%22%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20testing%2C%20it%20works%20correctly%20when%20the%20wrong%20info%20is%20inputted%2C%20but%20it%20also%20returns%20the%20error%20message%20when%20I%20input%20the%20correct%20login%20credentials%20as%20well.%20See%20below%20(Password%20is%201234)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ryanbarton324_0-1619456725499.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275611i10905C52DCBBB000%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ryanbarton324_0-1619456725499.png%22%20alt%3D%22ryanbarton324_0-1619456725499.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20where%20in%20the%20code%20I%20might%20be%20going%20wrong%3F%20Are%20there%20some%20extra%20steps%20that%20need%20to%20be%20taken%20in%20order%20to%20have%20another%20UserForm%20open%20up%20based%20on%20user%20inputs%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20the%20Me.Username.Value%20have%20to%20be%20directly%20referenced%20from%20the%20login%20form%20now%20that%20I'm%20using%20pictures%20instead%20of%20command%20buttons%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERyan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293806%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20VBA%20code%20after%20clicking%20on%20a%20UserForm%20'image'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293806%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20will%20note%20that%20I%20tested%20out%20the%20below%20code%2C%20and%20it%20worked%20as%20intended.%20The%20main%20menu%20UserForm%20was%20opened%20up%20when%20the%20green%20Login%20button%20was%20selected.%20So%20it%20seems%20to%20me%20that%20its%20related%20to%20the%20Username%2FPassword%20user%20inputs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20OKButton_MouseUp(ByVal%20Button%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20X%20As%20Single%2C%20ByVal%20Y%20As%20Single)%0A%0A%0AUnload%20Me%0AfrmMainMenu.Show%0A%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERyan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2293810%22%20slang%3D%22en-US%22%3ERe%3A%20Running%20VBA%20code%20after%20clicking%20on%20a%20UserForm%20'image'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2293810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036947%22%20target%3D%22_blank%22%3E%40ryanbarton324%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20version%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20OKButton_MouseUp(ByVal%20Button%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20X%20As%20Single%2C%20ByVal%20Y%20As%20Single)%0A%20%20%20%20If%20Me.UserName.Value%20%3D%20%22fhwa%22%20And%20Me.Password.Value%20%3D%20%221234%22%20Then%0A%20%20%20%20%20%20%20%20Unload%20Me%0A%20%20%20%20%20%20%20%20frmMainMenu.Show%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20MsgBox%20%22ATTENTION%3A%20Wrong%20Login%20Information%22%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIt%20shouldn't%20make%20a%20difference%20whether%20you%20use%20an%20image%20or%20a%20command%20button.%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20to%20refer%20to%20the%20username%20elsewhere%2C%20you%20could%20store%20the%20value%20of%20Me.UserName%20in%20a%20public%20variable%20that%20has%20been%20declared%20in%20a%20standard%20module.%20It%20will%20then%20be%20available%20to%20all%20code%20in%20the%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

 

I am currently trying to write some VBA code that will initialize once I left click on a UserForm image I have uploaded. In my specific situation, I am using a hovering effect for a 'Login' button that will highlight a white 'Login' button/image green when hovered over with the mouse. This is done by hiding the white image and only showing the green image. See below, where I just have the white image over top the green one.

ryanbarton324_1-1619446327598.png

 

That part works fine right now, but the goal after this green highlight is to select the green button with the mouse, and after left clicking, some code will run. This is not working right now. I looked up on other forums and it looked like all I needed to do was have the code run with a '_Click()' sub in the code, but when I run a _Click for the image, an error returns saying below.

ryanbarton324_0-1619446248533.png

 

Whenever I right click on the image itself and do 'View Code', it creates a new Private Sub for '_BeforeDragOver()' and when I look in the top right drop down menu, there is no option for 'Click'. When I have the below code, the same error from above is returned. And when using the '_BeforeDragOver()' created Sub, nothing would happen when I do anything with the image.

 

ryanbarton324_2-1619446567571.png

 

 

Is there a new way to do this in the most recent Excel? Or is this not feasible anymore?

 

All other forums seemed to point to using a _Click sub for the image but I can't seem to make it work.

 

Any help is appreciated.

 

Thank you,

 

Ryan

 

11 Replies

@ryanbarton324 

The Image control does not have a click event. you can use the MouseUp (or MouseDown) event instead, with syntax

 

Private Sub Image1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    ' your code here
End Sub

 

with the name of your control instead of Image1, of course.

@Hans Vogelaar 

 

Thank you for the reply! Very much appreciated!

 

That did work for me to be able to click on the button after the highlight. Now, I have a different problem, hopefully minor. 

 

So I have code that will run when the MouseUp sub is run after I left click on the 'Login' button, and it should be checking the login credentials and verifying that they are correct with a username and password. If the username and/or password is incorrect, it will return a message saying 'ATTENTION: Wrong Login Information'. If not, the main menu UserForm will pop up and the login form will be unloaded. See code below.

 

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

If Me.Username.Value = "fhwa" Then
If Me.Password.Value = "1234" Then
Unload Me
frmMainMenu.Show
        Exit Sub
    End If
End If
MsgBox "ATTENTION: Wrong Login Information"

End Sub

 

When testing, it works correctly when the wrong info is inputted, but it also returns the error message when I input the correct login credentials as well. See below (Password is 1234)

ryanbarton324_0-1619456725499.png

 

Do you know where in the code I might be going wrong? Are there some extra steps that need to be taken in order to have another UserForm open up based on user inputs? 

 

Does the Me.Username.Value have to be directly referenced from the login form now that I'm using pictures instead of command buttons?

 

Thanks,

 

Ryan

 

@Hans Vogelaar 

 

I also will note that I tested out the below code, and it worked as intended. The main menu UserForm was opened up when the green Login button was selected. So it seems to me that its related to the Username/Password user inputs.

 

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


Unload Me
frmMainMenu.Show


End Sub

 

Ryan

@ryanbarton324 

Try this version:

Private Sub OKButton_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Me.UserName.Value = "fhwa" And Me.Password.Value = "1234" Then
        Unload Me
        frmMainMenu.Show
    Else
        MsgBox "ATTENTION: Wrong Login Information"
    End If
End Sub

It shouldn't make a difference whether you use an image or a command button.

If you need to refer to the username elsewhere, you could store the value of Me.UserName in a public variable that has been declared in a standard module. It will then be available to all code in the workbook.

@Hans Vogelaar

Unfortunately, that other code returned the same problem.

The fact that I was able to get to the other menu through this method, barring the username/password input, leaves me optimistic, but still unsure on why this isn't working. Not a lot of extra code to be making problems. Although, I'm not too experienced with coding in UserForm Images.

I may try and find some other way to code out the username/password check, but seeing that your revision returned the same error, I don't really know where to start. That would've been my guess, doing an AND rather than a nested IF loop.

Any thoughts?

And thanks again for the help on this.

Ryan

@ryanbarton324 

Could you attach a sample workbook without sensitive information that demonstrates the problem?

@Hans Vogelaar 

 

Apologies for the delayed response, thanks for the reply as always.

 

I've gone ahead and created a simplified Excel file that has both of the UserForms, 'frmLogin' (One giving me issues) and then one 'frmMainMenu' that should pop up when the correct login information is given and 'Login' is selected in the login userform. I annotated all of the buttons within the code if that helps find the issue.

 

I removed the functionality of every other button from the main menu, so there should be minimal code to hopefully find a solution.

 

All I need is to somehow go from entering in the username: fhwa and password: 1234 in the frmLogin, then selecting the 'Login' green button/picture, then the login form will close itself and the main menu will pop up.

 

Thanks again for the help.

 

Ryan

best response confirmed by ryanbarton324 (Occasional Contributor)
Solution

There is a textbox named TextBox1 on top of the Password text box.

So you actually enter the password in TextBox1, and the Password text box remains empty. Hence the message that the login information is incorrect.

@Hans Vogelaar

Oh my, how embarrassing.

I deleted that extra TextBox and now it works as intended. Not sure how that extra TextBox got there but that definitely explains the problems.

Thank you for all the help on this! Hopefully I won't make the same mistake in the future.

Best Regards,

Ryan
As a matter of fact, you can have a click event for an image control. It appears as a deprecated item in the Object Browser if you show hidden members. You can't add it using the usual dropdown in the code module header, but you can write a sub named "Image1_Click()" (or whatever the image's name is), and it will work as expected.

I don't know why it wasn't working for this user. I'd suggest it was a typo, but I'm the only bad typist in this forum :).
Sometimes that happens if I hold Ctrl to select multiple controls. If I move the cursor slightly while clicking on a control, I can end up with duplicate controls that line up perfectly with the originals but obscure them.