How does one break out into the code when a modeless userform is
running. When I had it running modal, I would go Ctrl+break and it
would open the editor. Anyone?

tia,
Hughie

Mike wrote:

In modal operation all code execution stops until the userform finishes
compared to non modal where other code (except other user forms) can continue
to execute. Pressing ctrl/break in non modal is therefore unlikely to break
into the userform code.

I see. How does one then continue to work on the code and develop the
app? I open the spreadsheet, it automatically starts my form and now I
can't get at the code.

Hughie

Put this line:

Debug.Assert False

wherever you want your code to break. It's the rough equivalent of a
programmatic breakpoint.


hughie wrote:
Mike wrote:

In modal operation all code execution stops until the userform finishes
compared to non modal where other code (except other user forms) can continue
to execute. Pressing ctrl/break in non modal is therefore unlikely to break
into the userform code.

I see. How does one then continue to work on the code and develop the
app? I open the spreadsheet, it automatically starts my form and now I
can't get at the code.

Hughie

wrote:

Put this line:

Debug.Assert False

wherever you want your code to break. It's the rough equivalent of a
programmatic breakpoint.

Because I can't get to the code I can't add the line!

H

Or click anywhere on your worksheet and press Alt + F11. Then add the
line I told you to and re-open your worksheet.


hughie wrote:
wrote:

Put this line:

Debug.Assert False

wherever you want your code to break. It's the rough equivalent of a
programmatic breakpoint.

Because I can't get to the code I can't add the line!

H

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

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 !?

Heh...

so you've definitely locked yourself out?

Is there no way in Excel to open a file
without auto-macros running like in Word.
Something like holding shift or control
or some other key pressed while opening the file.
Called, I think, secure mode.

If there is absolutely no other way,
I'd try to put an error in the code
by opening the workbook in an hex-editor
and change just one letter in a reserved word in the code.
That is change, not add or delete.

I wonder.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

hold the shift key as you launch excel and see if that works. else

start excel in safe mode. add /s to the end of the shortcut that starts excel
and then change it back when you're done

--


Gary


"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 !?

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 !?

Yes, I'm locked out! Safe mode, /s doesn't change anything, nor does
holding down SHIFT when I open the workbook.

I'm using excel 2003 sp2 on XP Pro 2002 sp2.

It's starting to look like I'll have to go back to having it run
modally so that Ctrl+break does work. Unless anyone has a winner idea.

Weird though aint it?

Hughie

Chip Pearson wrote:

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 !?

for info..I've opened a previous version that was opening the form
modally, going ctrl+break and then finding the later versions available
in the project browser. I've exported the frm and the bas to work on
them like that...

Hughie

hughie wrote:

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 !?

...and adding a 'view worksheet' button that does a
'application.visibile = true'.

hughie wrote:

for info..I've opened a previous version that was opening the form
modally, going ctrl+break and then finding the later versions available
in the project browser. I've exported the frm and the bas to work on
them like that...

Hughie

hughie wrote:

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 !?