If your form automatically loads when Excel (or the particular workbook)
opens, you need to prevent that from happening if you want to edit your
code. When you open the workbook, hold down the SHIFT key. That will prevent
workbook startup code from running. Now you'll have your workbook open but
will not have the start up form visible. Edit your code normally.

I don't think you can trap the CTRL+BREAK key if the procedure that shows
the form terminates. CTRL+BREAK doesn't make it through to the usual
KeyPress, KeyUp, and KeyDown events of the UserForm object. One way to do it
would be to rewrite the procedure that displays the form as follows:

Sub ShowTheForm()
Dim N As Long '<< Test only. Remove.
Dim Done As Boolean
Const BREAK_KEY_PRESSED = 18

On Error GoTo BreakKeyHandler:
'''''''''''''''''''''''''''''''''''''''''''''
' Configure EnableCancelKey to raise an error
' 18 when the user types CTRL+BREAK.
'''''''''''''''''''''''''''''''''''''''''''''
Application.EnableCancelKey = xlErrorHandler
'''''''''''''''''''''''''''''''''''''''''''''
' Show The Form modelessly
'''''''''''''''''''''''''''''''''''''''''''''
UserForm1.Show vbModeless
'''''''''''''''''''''''''''''''''''''''''''''
' Loop doing nothing. Worksheet and form
' events will continue to work as expected.
'''''''''''''''''''''''''''''''''''''''''''''
Done = False
Do Until Done = True
N = N + 1 ' <<< Testing: Omit this line
DoEvents ' Release to other threads
Loop
''''''''''''''''''''''
' Get out.
''''''''''''''''''''''
Exit Sub
BreakKeyHandler:
Done = True
''''''''''''''''''''''''''''''''''''''''''
' If the user pressed CTRL+BREAK, VBA will
' raise an error 18.
''''''''''''''''''''''''''''''''''''''''''
If Err.Number = BREAK_KEY_PRESSED Then
''''''''''''''''''''''''''''''''''''''
' User type CTRL+BREAK. Hide the
' form.
''''''''''''''''''''''''''''''''''''''
MsgBox "User typed CTRL+BREAK" '<<< Testing Only. Remove
UserForm1.Hide
' Unload UserForm1
Exit Sub
Else
'''''''''''''''''''''''''''''''''''''
' Some other error occurred.
'''''''''''''''''''''''''''''''''''''
MsgBox "An unexpected error occurred: " & _
CStr(Err.Number) & " Description: " & Err.Description
End If

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"hughie" wrote in message
ps.com...

hughie wrote:

Mike wrote:

Access the code by using the VB editor (Tools - Macro - VB Editor). If
the
userform hasn't terminated its a good idea to do this first

When I terminate the userform it closes the whole spreadsheet therefore
I can't get to the editor that way unfortunately. I can open the
editor when I start excel without opening any specific spreadsheet but
then my code is, of course, unavailable as I don't have my xls open.
See what I mean?

This has GOT to be an easy one.... I thought....hehe

the version of the code i was using before, the one that was running
the form modally, was doing an 'application.visible = false', so users
only see the form. Although there is a way to get to were I want by
taking that version forward, I'm now curious to know if there is a way
to get to the code in this situation.

Just to clarify, I can't get to the editor, or the spreadsheet and
ctrl+break is no longer available. The previous suggestions are
welcome but off the mark.

curious..


H !?