+ Reply to Thread
Results 1 to 19 of 19

Closing Immediate window

  1. #1
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Closing Immediate window

    Is there any way to have a macro, that is started from the immediate window, close the immediate window when it's done. My macro closes the immediate window, but as soon as it ends, the immediate window reopens and my cursor is inside it.
    Last edited by foxguy; 04-20-2009 at 02:29 PM. Reason: solved in another thread

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Closing Immediate window

    You can't close or clear the Immediate window from VBA.

    Why are you invoking macros from there?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    I have added VBE menus that call utilitie macros in a dedicated project (VBE.xls). But some of those utilities clear the VBE menus when they are run on a VBE.xls codepane. I then have to re-run the macro that sets up my VBE menus. That can't be done from within the utilitie. It has to be run AFTER the utilitie has ended. By using SendKeys in the Immediate window, I can re-run Menu_Setup, but it leaves the Immediate window open and in my way. I'm trying to find a way to run Menu_Setup and close the immediate window.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Closing Immediate window

    There may be a method using Windows API, but I think none without (other than SendKeys to clear the window).
    Last edited by shg; 04-18-2009 at 10:50 PM.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,934

    Re: Closing Immediate window

    Will this work for you?

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Andy, the problem is that I need to call "CloseImmediateWindow()" from within the immediate window. As soon as it's finished the Immediate Window reopens.

  7. #7
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Closing Immediate window

    hi Foxguy,

    Quote Originally Posted by foxguy View Post
    I have added VBE menus that call utilitie macros in a dedicated project (VBE.xls). But some of those utilities clear the VBE menus when they are run on a VBE.xls codepane. I then have to re-run the macro that sets up my VBE menus. That can't be done from within the utilitie. It has to be run AFTER the utilitie has ended. By using SendKeys in the Immediate window, I can re-run Menu_Setup, but it leaves the Immediate window open and in my way. I'm trying to find a way to run Menu_Setup and close the immediate window.
    I don't know much about using code to work on the VBE but I have a few thoughts...
    -Rather than the approaches suggested in the posts, would it be easier for your utility to test which code pane is active & not to clear the menus if it is working on a VBE.xls codepane?
    -Can you start Menu_Setup directly via:
    • [alt + F8] from Excel
    • pressing [F5] to run the macro from a code pane & having the code flick to the next VBE window before starting (similar to "ActiveWindow.ActivateNext")
    • a "hidden" (.visible = false) or discreet button on the utility UI (or a toolbar) to rerun Menu_Setup?

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  8. #8
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Rob,
    I have no control over whether the VBE menus are cleared. They aren't like the menus in Excel. When a codepane, in the project that has Menu_Setup in it, is modified in certain ways, it clears the VBE menus.

    I have no problem manually rerunning Menu_Setup, it's just one click somewhere (either in a copepane, or the immediate window). I'm trying to get it to do it automatically. It's just irritating to have to manually close the immediate window when I'm updating my utilities.

    Createing a button to rerun Menu_Setup is pointless, because when the menus get cleared ALL the buttons get cleared including the one that would rerun Menu_setup.

  9. #9
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Closing Immediate window

    oh well, it was worth asking but it is well beyond my knowledge - fingers crossed that Andy, Shg or others have more tricks up their sleeves...

    Rob

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Closing Immediate window

    foxguy

    Why are you altering the menus in the VBE in the first place?

    I can't think of any reason you might want/need to do that.

    Am I missing something?

    PS You do realise that programming code with code can have some security issues.

  11. #11
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Norie,
    One of the things I do:
    ALL my macros have the same format
    Please Login or Register  to view this content.
    I got tired of typing all that everytime I created a new function. So I created a macro that inserts that code into the active codepane, and created a button in the VBE menus to trigger it.
    Now all I do is put my cursor where I want a new function, right mouse click, and select "New Function", then change the function name to what I want. It even selects the function name, so I don't even have to select it, I just start typing. Works great.

    But if I insert a new function into the project that contains Menu_Setup, it clears my VBE menus.

    I have several utilities (each with it's own button) to do things. I can comment out an entire section of code, Un-comment it back out, insert common statments like "On Error Resume Next", resize codepanes and more. They don't all clear my VBE menus, but the ones that do are irritating.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Closing Immediate window

    Why are you using Function?

    PS I've got a utility that allows me to comment/uncomment blocks of code - it's on the VBE Edit toolbar, think it's built in.

    There also another feature in the VBE - Import/Export...module.

  13. #13
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Norie,
    I'm at a loss as to your points.

    I use function when I need a function, I use sub when I need a sub. I don't understand the question.

    I don't argue that I can't do these things without writing modifying code and changing my VBE menus. I'm just saying that it's more convenient to do it my way. That may not be true for you.

    I never noticed that there was an Edit toolbar in VBE. The Edit>Indent/Outdent work better than my Edit/Outdent, so I'll have my button call them instead of my Indent/Outdent macros. Thanks

  14. #14
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Closing Immediate window

    hi Foxguy,

    I'll try to make this the last post from me because you may have had enough of me by now...?

    This is getting off topic but ties in with the last few posts - I find the below very useful in my coding development & hopefully they'll help you out too:
    Stephen Bullen's Smart Indenter (I think it is much better than the native VBE version!):
    http://www.oaltd.co.uk/Indenter/Default.htm
    from the VBA Programming section 2/3rds of the way down this sheet: http://www.oaltd.co.uk/Excel/Default.htm
    Rob Bovey's Code Cleaner & Documentor:
    http://www.appspro.com/Utilities/CodeCleaner.htm
    http://www.appspro.com/Utilities/Documentor.htm
    + others available at: http://appspro.com/Utilities/Utilities.htm
    Carlos J. Quintero's mztools (3.0):
    http://www.mztools.com/index.aspx

    Hth
    Rob
    Last edited by broro183; 04-19-2009 at 06:06 PM. Reason: deleted duplicate link

  15. #15
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Rob, thanks for the links. I have generally liked developing these tools myself, but I'm always interested in how other people are doing things.

  16. #16
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Closing Immediate window

    Good as gold, it is the best way to learn - thanks for the feedback

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,934

    Re: Closing Immediate window

    To stop the IM reopening try calling DelayedClose from the immediate window,

    Please Login or Register  to view this content.

  18. #18
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: Closing Immediate window

    Nicely done, Andy.

  19. #19
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Closing Immediate window

    Andy, clever.
    It works, but now I have another problem, that I thought was solved. But that's another thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1