Excel Timer Control? (2003 on XP)

Topic

New Reply

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.

Reply | Quote

Replies

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 and post 469,295 for code examples.

Reply | Quote

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?

Reply | Quote

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 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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

no, I don’t have anything unusual checked.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Hi Hans:

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

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
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.

Reply | Quote

Maybe this article gives you a start:

http://www.jkp-ads.com/Articles/DistributeMacro00.htm%5B/url%5D

Reply | Quote

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

Reply | Quote