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

jeff.j.griff...@gmail.com 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:
> jeff.j.griff...@gmail.com 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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 !?
>