VBA - what has been cliked?

    Hi,


    In VBA, is there anyway of determining what "button" has been clicked e.g. a command on the Quick Access Toolbar?


    Thanks,


    Emmy

    Re: VBA - what has been cliked?


    Just off the top of my head, I doubt it but have been known to be wrong.


    Why do you want to do this? If you explain, there may be another way to get the end result you're looking for.

    Re: VBA - what has been cliked?


    Thanks Cytop for looking at this.


    I'm trying to make the user use the Close Window button I've added to the QAT rather than the close button in the top right hand corner.

    Re: VBA - what has been cliked?


    Can't see why unless you just want to make it more difficult to close the workbook/application. Excel makes no distinction as to how a workbook is being closed so you can't check if the QAT button, Red button or even the File/Close menu was selected/clicked


    You can probably remove the Close button from the window but it will need VBA code.
    To be honest, unless there's a very compelling reason it's more bother than what it's worth, in my opinion.

    Re: VBA - what has been cliked?


    To try and explain briefly - in Excel 2013 if you have the Personal workbook hidden and close another workbook using the normal close button Excel closes too. I know that this is a change Microsoft made to the behaviour in Excel 2013 but I'm trying to find a way round it. I noticed that when you click the Close Window button which I've added to the QAT it only closes the active workbook and leaves the Excel session open with the Personal workbook still hidden - this is the behaviour I want. So I was hoping there was a way of distinguishing between clicking the QAT button and the normal close button but apparently not.


    Another route I tried was to use the Application close workbook event but came across other issues...


    Anyway I appreciate your time but I will give up for now. Thanks anyway!

    Re: VBA - what has been cliked?


    OK - If you haven't already tried this, there's another Close command you can add to the QAT - 'Close Window'. You can find that listed under 'Commands Not In The Ribbon' ...

    Re: VBA - what has been cliked?


    Thanks for thinking on this some more. This is the button that I'm using and refer to above (apologies if I wasn't clear). This gives me my desired behaviour in that it just closes the active workbook but leaves Excel open, with the Personal workbook still hidden. The only issue is that users can still click the normal close button.


    Not to worry - I guess I'll need to make it up to the user...

Participate now!

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

Register Yourself Login