Trigger Click Events Via Vba

    OzGrid / Et. Al.



    Is it possible, that while running code that the code can say initiate the click event on a command button on another sheet.



    Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"


    Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?

    Re: Trigger Click Events Via Vba


    mikerickson,


    I've tried that and here's the problem.


    1. The routine that is called when the control is clicked is on the module page for an object.
    2. Routines that are on this module page are not accessible ( as far as I know ) outside that module page. ( Even if you take the key word "PRIVATE" away from the routine declaration ).
    3. However, I did try your code and it could not find the routine. So the help was less than helpful. So if there is a format for the app.run command in which you have to spell out book/module sheet/routine name could you please show the formatting of the string.



    4. My current workaround has been to add "Checkboxes" that I leave invisible. Those I can check from anywhere at run time. It's just that I've added some "PERSONAL" enhancements to my apps and I didn't want to have to add a new check box to over a couple dozen projects.

    Re: Trigger Click Events Via Vba


    If the Click routine is moved to a normal module, then your other subs can reference it.
    If the _Click routine must stay in the Sheet module, then modify it so

    Code
    Sub Button1_Click()
Call buttonRoutine
End Sub

    is in the Sheets codemodule and the sub buttonRoutine is in a normal module.
    Your other sub can then call buttonRoutine.

    Re: Trigger Click Events Via Vba


    mikerickson,


    That's what I originally did...


    However, at some point what I began to do was make a worksheet that "DID" something and it did IT from beginning to end. The benefit of this was that if I created a new application that required that particular functionality I could simply copy that sheet into a new workbook, and viola the functionality was ported in.


    When I made one of these "LIBRARY" worksheets everything was controlled either through and event or a button on the sheet ( mostly buttons ). So when I moved the worksheet to a new application and I wanted to be able to execute the functionality of said worksheet I had to find some way to execute the code.


    So....


    At first I did use your suggestion. However, that meant that I had to create a seperate module sheet for each worksheet and then import 2 things into a workbook. That wasn't accomplishing what I wanted. So I looked and looked even asked a couple of times, but no luck. I then saw that via code I could change the status of a CheckBox or Optionbutton which when changed it activated it's default event ( click ) which I then used to then do a simple "CALL" to the routine on that sheets module page.



    This week at work I started using "ONKEY" functionality to assist me when working with workbooks. You know running some code to apply a basic worksheet format or to insert a new worksheet after the current sheet or to toggle the "ShowAllData" and even set a Filters Criteria automatically....


    This lead to that in the workbooks that I create I have a hidden page that contains some information that I don't want general users mucking with. These pages are set to "VeryHidden" and on the front sheet of every workbook I have a button that will unhide these sheets if I'm logged into the PC. So I was wanting the ONKEY routines that I was writting in my Personal.xls workbook to click this button ( as I hadn't included a related checkbox ) for remote accessing....


    Whew...... long explanation, don't know if you got all that.....



    Thanks anyway.

    Re: Trigger Click Events Via Vba


    Add a control toolbox button to a worksheet with the following event code.
    Changing the Private keyword to public


    Code
    Public Sub CommandButton1_Click()
MsgBox "Hello World"
End Sub


    From you standard code module you would use


    Code
    Sub Test()
Sheet1.CommandButton1_Click
End Sub


    Changing the sheet1 reference to what ever the code name for the sheet is.

    [h4]Cheers
    Andy    [/h4]

    Edited once, last by Carim: Added Code Tags ().

    Re: Trigger Click Events Via Vba


    Ivan / Andy,


    Well at first I thought, no luck. But played with Ivan's example and the macro recorder and came up with this line of code.



    So just for clarity. Had to add the workbook name as the macro was contained in my Personal.xls workbook. Used the "Sheet1" because it worked here. Though I will need to find out how to reference the sheet with it's tab name.... As I can't be sure that Sheet1 is always correct.


    Did not need to change the Private to Public on the routine. So Mike if this is what you were tyring to say then bud I missed it.





    Code
    Application.Run ("GpsgctInfo_Database.xls!Sheet1.wkscmd_DisplayDEI_Click")



    Thanks:music:

    Re: Trigger Click Events Via Vba


    This does the trick:




    Code
    For Each wks In ActiveWorkbook.Worksheets
    If wks.Name = "User Application Interface" Then
        Application.Run (ActiveWorkbook.Name & "!Sheet" & wks.Index & ".wkscmd_DisplayDEI_Click")
        Exit For
    End If
Next wks

    Re: Trigger Click Events Via Vba


    If you use the .Run method then there is no need to worry about public/private renaming.


    Use CodeName to get the correct sheet reference.


    Code
    For Each wks In ActiveWorkbook.Worksheets
If wks.Name = "User Application Interface" Then
Application.Run (ActiveWorkbook.Name & "!" & wks.Codename & ".wkscmd_DisplayDEI_Click")
Exit For
End If
Next wks

    [h4]Cheers
    Andy    [/h4]

    Edited once, last by Carim: Added Code Tags ().

    Re: Trigger Click Events Via Vba


    Nice team effort!


    iwrk4dedpr, I think this is a useful enough result (it's now in my Oz bag o' tricks) that it should be in the Hey That's Cool forum. Would you consider summarizing and posting there?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Trigger Click Events Via Vba


    Ok I've come across a problem.


    The code snippet below should be fine. However.....



    Most of my workbooks have 3 Identical worksheets...
    One named "[COLOR="Red"]U[/COLOR]ser [COLOR="Red"]A[/COLOR]pplication [COLOR="Red"]I[/COLOR]nterface"
    One named "Developer Extended Interface"
    One named "Application Data"


    Each sheet has the EXACT same controls from workbook to workbook...



    So on the UAI sheet I always have a button ( form the Control Toolbox menu ) named "wkscmd_DisplayDEI" It always has a macro call "wkscmd_DisplayDEI_Click"



    Now in some workbooks the code snippet below works just fine. In others I get a run time error.


    Quote

    Run-time error '1004':


    The macro 'HHC-HOT_LotTracker.xls!Sheet1.wkscmd_DisplayDEI_Click' cannot be found.



    Wish I could put up some screen shots but alas they are too big and I don't have a web server where I can put a link to....




    Any ideas on why this will work in some workbooks and not others?

    Re: Trigger Click Events Via Vba


    Mike,


    Well this is wierd this is the mail that I got...


    Quote

    That sometimes happens to me. The odd thing is, after I reset, the button works fine.
    One workaround might be to set the OnAction property in a Workbook_Open routine.



    But that's not what is in the post and I don't see that it was edited....


    However, what do you mean by "after I reset..." reset what?

    Re: Trigger Click Events Via Vba


    Posts only show as edited if they're edited after some certain time following entry (a minute or two?). I reckon the email goes out immediately.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Trigger Click Events Via Vba


    Shg,,


    Yeah but the post used to show that there was an edit to the post..... No biggie though just thought is was odd.

    Re: Trigger Click Events Via Vba


    I remembered that toolbox controls don't take OnAction (forgive the Mac user). So, I edited out my bad idea.


    I don't recall exactly how I handle it, but returning to normal control of the spreadsheet the easiest way (maybe by clicking OK on the alert??) is what I meant by reset.

    Re: Trigger Click Events Via Vba


    Mike,


    Sorry, ( about being a MAC ) you're going the way of WIN anyway.... :)



    Well that doesn't make the issue of it not work. I'm just confused as to why in some of my workbooks it works. At first I thought it might be because the code is protected on opening. I wonder if it might be the "Trust Access" to visual studio project but it's all ready checked as being trusted....


    Though that it might be because I have a "~" in some of my workbooks names but nope. That's not a unique problem.


    It's just confusing because I haven't been able to spot a pattern in the denial of use.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login