Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Microsoft ISV Community Center Forums >> Visual Basic for Applications (VBA)

Control Break Trap will not work whilst Userform is displayed

Hello All,

In Excel I have statement

Application.OnKey "^{BREAK}", "Sub_Trap_Break"

This is okay at all times when there is no Userform being displayed but when there is a form this does not work.

Any ideas how to overcome this?

I am trying to stop users get to the spreadsheet.

To stop this I have:

1) Form event Query Close

2) The sheet only becomes visible when macros are enabled otherwise it is xlVeryHidden

3) The cells font and background colours are the same so nothing can be seen ! and it is protected at the appropriate times

4) Menus are disabled

But that fact that on control Break (when a form is displayed) the VBE window appears is irritatting, any ideas? (No code is displayed as that is protected too!)

Thanks for any ideas

Chas

PS This is only a temporary measure whilst I wait for Access to be installed on my PC at work, (probably will end up being my 2007 New Year's present from the Administrators!)

[1044 byte] By [ChasAA] at [2007-12-24]
«« Animating a canvas?
»» Release notes for MF in RC1
# 1

Hi Chas,

How about creating an Excel addin?

Excel Addins are like hidden spreadsheets where you can store code and access cells etc but all in a fairly hidden way. The macros of the workbook would be available through toolbars or menu items while the sheets of the addin sit hidden in the background. If you password protect the addin there isn't an easy way to display the sheets.

DerekSmyth at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 2

p.s. the reason your Application.OnKey isn't working for Userforms is because the OnKey event is only been handled for key presses when Excels main window (the application) has focus. Once the focus switches to a Userform any key presses on the form are not handled at the Excel's application level but are instead handled within the user form.

DerekSmyth at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 3

Derek,

How are you?.

I knew I could count on you for an explanation!

Thanks again.

I might wait to get Access and start writing it in that.

Thanks again

Chas

ChasAA at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 4

Hi Chas,

I'm doing very well thanks...

I've created a small example approach you can use until you get Access. The approach I've used uses an Excel Addin to insert data into a backend and shared (I think) spreadsheet. I've done it in such a way that when you get Access there will be very little to do to update the backend.

post your email address and I'll send it on along with a better explaination.

DerekSmyth at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 5

Hello Derek,

That will be great thanks.

My email is charleschand@aol.com

Regards

Chas

ChasAA at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
# 6
Chas, edit your last post there and change your email, you might get spam if you leave it in the name@domain.com format. In fact I've got your address now so you could delete it.
DerekSmyth at 2007-10-7 > top of Msdn Tech,Microsoft ISV Community Center Forums,Visual Basic for Applications (VBA)...
Microsoft ISV Community Center Forums
Visual Basic for Applications (VBA)

Microsoft ISV Community Center Forums Hot Topic

Microsoft ISV Community Center Forums New Topic

Microsoft ISV Community Center Forums

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified