Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Timer Control? (2003 on XP)

    Hi everybody:

    I have written an app in Excel that has a custom menu bar. The app copies and manipulates data from the source workbook into a target workbook, either in one fell swoop or by the user selecting individual menu items for the various steps, and then formats the target book for import into an off the shelf application.

    Problem is, my users like to have multiple Excel files open while this is running, and they like to work on them. I naturally don't want my custom menu bar affecting their other worksheets. Actually, I'd like it hidden whenever they click on a different workbook. I've tried hiding or deleting it programmatically, but when it's needed, the users can still open another Excel file by double-clicking its icon in Explorer or their desktop. So, I thought I'd trap their click events via the API using GetAsyncKeyState, then test which workbook they clicked. However, this code needs to run in the background while my other code is running, so I can't just use the timer function, and I don't see a VB timer control anywhere in the toolbox list.

    Any ideas? Does anyone know if the VB timer control (or something similar) is available for Excel workbooks and, if so, what it's called?

    Thank you in advance for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Timer Control? (2003 on XP)

    You don't need a timer for this. Instead, write code in the Workbook_Activate event in the ThisWorkbook module to make your custom menu bar visible, and in the Workbook_Deactivate event to hide it.

    See <post#=497,804>post 497,804</post: > and <post#=469,295>post 469,295</post: > for code examples.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    Thank you, Hans, but this approach doesn't work, because the source workbook gets deactivated when the target workbook is being formatted, which does require the custom toolbar. In addition, when I was playing with it, I found that code to make the toolbar not visible or not enabled on the workbook or window deactivate event did not prevent having my custom toolbar display when a user opened a different Excel workbook by double-clicking its icon in Explorer.

    Any other ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Timer Control? (2003 on XP)

    It shouldn't matter whether you open a workbook from within Excel or from Windows Explorer - the Deactivate event of the previously active workbook should fire and hide the toolbar. I don't understand why it doesn't for you.

    If you want the toolbar to be visible in several workbooks, you can use the application level WorkbookActivate and WorkbookDeactivate events. See <post#=91,613>post 91,613</post: > for an example of how to write code for application level events. The WorkbookActivate and WorkbookDeactivate events have an argument Wb that refers to the workbook being (de)activated, you can inspect it (or its name) to see if the toolbar should be visible or not.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    Thank you for your input. I've tried the approach you recommend, but other Excel files still open with my toolbar. Could this be because my code is in a custom template instead of just a workbook? Would my template become attached to any other Excel files opening when a workbook created with it is running? If so, how would I prevent this? Just to complicate things, the "book.xlt" template is not contained in my Microsoft Templates folder.

    I'm mostly an Access programmer, so perhaps I need to understand better how Excel application development works.

    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Timer Control? (2003 on XP)

    Excel templates and toolbars work differently than those in Word. A toolbar, once displayed, will remain visible unless explicitly hidden or deleted. So you'll have to use VBA code (as indicated in my previous replies) to control the visibility of your custom toolbars.

    You might want to take a look at Professional Excel Development by Stephen Bullen, Rob Bovey and John Green. Excel Books by John Walkenbach are also good.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    I downloaded the sample Excel workbook on the link you recommended and tried it out. The code only works if I use Excel's "Window" menu item and select a different Excel file from the list. Whenever I open a workbook by double-clicking its icon in Explorer, or if I just click somewhere else on my screen outside the Excel window, the deactivate event does not fire (nothing happens).

    That's why I thought the best approach would be to trap user click events using the API, but I would still need some sort of timer control to put onto my Excel workbook in order to run the code.

    Any other ideas?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Timer Control? (2003 on XP)

    When I open that workbook, the code works whether I switch windows using the Window menu, or open a workbook from Windows Explorer, and that's how it's supposed to work. I don't know why it doesn't work that way for you. Umm, you don't happen to have ticked the check box "Ignore other applications" in the General tab of Tools | Options...?

    BTW, it's normal that nothing happens if you click outside the Excel window - the WorkbookDeactivate event occurs if you switch away from a workbook within Excel

    It shouldn't be necessary to use Windows API functions for this, and frankly, I wouldn't know how.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    Maybe this article gives you a start:

    http://www.jkp-ads.com/Articles/DistributeMacro00.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    no, I don't have anything unusual checked.

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    Thank you for the helpful link. Perhaps I will try the Add-In approach.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Timer Control? (2003 on XP)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hi Hans:

    I figured out how to do it by modifying the VB code at the following link:
    http://www.vbforums.com/showthread.php?p=1888414

    It uses API calls to create a timer in code, without requiring a control, in a dll that you can add as a reference in your VBA project. Just put his calling code from frmMain into your ThisWorkbook_Open and ThisWorkbook_BeforeClose event procs to initialize, start and then stop and kill the timer. The rest of his code goes into your project module.

    Then, use a simple API call to trap the user's click events, whether or not they occur within the Excel application:
    (module level<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private blLeftClick As Boolean
    Private blRightClick As Boolean

    called by:

    Public Function GetClickEvent()
    On Error GoTo err_GetClickEvent
    'To check left mouse button pass 1
    If GetAsyncKeyState(1) = 0 Then
    blLeftClick = False
    Else
    blLeftClick = True
    Debug.Print "left click" 'Your code here

    End If

    'To check right mouse button pass 2
    If GetAsyncKeyState(2) = 0 Then
    blRightClick = False
    Else
    blRightClick = True
    Debug.Print "right click" 'Your code here
    End If

    err_GetClickEvent:
    If Err.Number <> 0 Then
    MsgBox Err.Description
    Exit Function
    End If

    End Function

    And there you have it!

    An additional benefit of this approach is that you can set the timer interval pretty much as long as you want to.

Posting Permissions

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