I have an Excel VBA routine that controls another application (AutoCAD) via
Excel userforms. While userforms are shown, you cannot use Excel cells --
it's frozen. I like the fact that the user has full access to AutoCAD while
my Excel VBA routine is controlling it, but I'd also like them to have access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb (which I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a 3rd VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather than on
a userform?

Thanks,
-Tony

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application (AutoCAD) via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb (which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a 3rd VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather than
on
a userform?

Thanks,
-Tony

Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but not with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application (AutoCAD) via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb (which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a 3rd VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather than
on
a userform?

Thanks,
-Tony

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application (AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a 3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather
than
on
a userform?

Thanks,
-Tony

I'm looping because I don't really understand how a modeless form with
controls works. I'm used to having the code exit after the user form is
dismissed.
When I don't loop the program shows the form but then "continues on"
executing code, which means that it proceeds to exit the program. I don't
understand what I should put after

user.show

that will enable the program to still respond to the userform and yet not
exit.

Thanks,
Tony
"Tom Ogilvy" wrote:

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application (AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a 3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather
than
on
a userform?

Thanks,
-Tony

If you want the code to wait for the form to drop, then divide the code into
two parts.

end the first by showing the form. Start the second from the form.

If the code is running, generally it wouldn't be appropriate for the user to
be accessing the worksheet, so it isn't clear what you are trying to
accomplish,.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
I'm looping because I don't really understand how a modeless form with
controls works. I'm used to having the code exit after the user form is
dismissed.
When I don't loop the program shows the form but then "continues on"
executing code, which means that it proceeds to exit the program. I don't
understand what I should put after

user.show

that will enable the program to still respond to the userform and yet not
exit.

Thanks,
Tony
"Tom Ogilvy" wrote:

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate
things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure
how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application
(AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to
AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to
have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a
3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather
than
on
a userform?

Thanks,
-Tony

Tom,
I think your last response is getting me closer, but I'm still not quite
there yet. Within my code module I put:
user.show vbModeless
If user.goAwayClick Then Unload user
Exit Sub
while in my userform code I put the following at the end of my initialize sub
me.show
This did allow the form code to control the form, and prevented the code in
the main module from causing the program to exit. But since the "me.show"
did not include vbModeless, I still could not access the Excel sheet (it
remained frozen). So, I then tried using
me.show vbModeless
but this again caused the code in the userform to continue, which meant that
the program exited again.

Without using a loop (which you advised against), I don't see what the code
should be right after the
me.show vbModeless
line.

To answer your question about why I would want to do this: I'm manipulating
drawing entities in AutoCAD based upon data in the Excel cells. While I can
do this with the form, using the features in Excel (such as dragging to
change a series of cells, the ability to see many cells and directly
manipulate them, etc...) is a much more efficient way to do things.

Thanks again,
Tony
"Tom Ogilvy" wrote:

If you want the code to wait for the form to drop, then divide the code into
two parts.

end the first by showing the form. Start the second from the form.

If the code is running, generally it wouldn't be appropriate for the user to
be accessing the worksheet, so it isn't clear what you are trying to
accomplish,.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
I'm looping because I don't really understand how a modeless form with
controls works. I'm used to having the code exit after the user form is
dismissed.
When I don't loop the program shows the form but then "continues on"
executing code, which means that it proceeds to exit the program. I don't
understand what I should put after

user.show

that will enable the program to still respond to the userform and yet not
exit.

Thanks,
Tony
"Tom Ogilvy" wrote:

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate
things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure
how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application
(AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to
AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to
have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using a
3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet rather
than
on
a userform?

Thanks,
-Tony

There should only be one show command. I wouldn't have a show command in
the initialize event.

I really can't advise you on how to implement. I understand what you are
saying, but don't know what the role of userform is or specifically what is
going on.

user.show vbModeless
If user.goAwayClick Then Unload user
Exit Sub

doesn't make sense to me for a modeless form. You would show the form and
forget about it. Handle events related to actions taken in the form with the
form events. I think you are going to have to figure how to implement what
you want to do.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,
I think your last response is getting me closer, but I'm still not quite
there yet. Within my code module I put:
user.show vbModeless
If user.goAwayClick Then Unload user
Exit Sub
while in my userform code I put the following at the end of my initialize
sub
me.show
This did allow the form code to control the form, and prevented the code
in
the main module from causing the program to exit. But since the "me.show"
did not include vbModeless, I still could not access the Excel sheet (it
remained frozen). So, I then tried using
me.show vbModeless
but this again caused the code in the userform to continue, which meant
that
the program exited again.

Without using a loop (which you advised against), I don't see what the
code
should be right after the
me.show vbModeless
line.

To answer your question about why I would want to do this: I'm
manipulating
drawing entities in AutoCAD based upon data in the Excel cells. While I
can
do this with the form, using the features in Excel (such as dragging to
change a series of cells, the ability to see many cells and directly
manipulate them, etc...) is a much more efficient way to do things.

Thanks again,
Tony
"Tom Ogilvy" wrote:

If you want the code to wait for the form to drop, then divide the code
into
two parts.

end the first by showing the form. Start the second from the form.

If the code is running, generally it wouldn't be appropriate for the user
to
be accessing the worksheet, so it isn't clear what you are trying to
accomplish,.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
I'm looping because I don't really understand how a modeless form with
controls works. I'm used to having the code exit after the user form
is
dismissed.
When I don't loop the program shows the form but then "continues on"
executing code, which means that it proceeds to exit the program. I
don't
understand what I should put after

user.show

that will enable the program to still respond to the userform and yet
not
exit.

Thanks,
Tony
"Tom Ogilvy" wrote:

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate
things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure
how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the
program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but
not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application
(AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to
AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to
have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using
vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using
a
3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet
rather
than
on
a userform?

Thanks,
-Tony

Tom,
I see what I was doing wrong now, and your help was just what was needed.
Thanks.

The problem I was having was that I'd placed an "EndProgram" routine that
would execute after my original (modal) form was dismissed. With the new
modeless form what I needed to do was place the "EndProgram" routine within
the form code so that it could execute it instead. My mental block was in
not realizing that a modeless form could continue to control things after all
the code to be executed in the regular modules was finished.

Thanks again, Tom.

-Tony
"Tom Ogilvy" wrote:

There should only be one show command. I wouldn't have a show command in
the initialize event.

I really can't advise you on how to implement. I understand what you are
saying, but don't know what the role of userform is or specifically what is
going on.

user.show vbModeless
If user.goAwayClick Then Unload user
Exit Sub

doesn't make sense to me for a modeless form. You would show the form and
forget about it. Handle events related to actions taken in the form with the
form events. I think you are going to have to figure how to implement what
you want to do.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
Tom,
I think your last response is getting me closer, but I'm still not quite
there yet. Within my code module I put:
user.show vbModeless
If user.goAwayClick Then Unload user
Exit Sub
while in my userform code I put the following at the end of my initialize
sub
me.show
This did allow the form code to control the form, and prevented the code
in
the main module from causing the program to exit. But since the "me.show"
did not include vbModeless, I still could not access the Excel sheet (it
remained frozen). So, I then tried using
me.show vbModeless
but this again caused the code in the userform to continue, which meant
that
the program exited again.

Without using a loop (which you advised against), I don't see what the
code
should be right after the
me.show vbModeless
line.

To answer your question about why I would want to do this: I'm
manipulating
drawing entities in AutoCAD based upon data in the Excel cells. While I
can
do this with the form, using the features in Excel (such as dragging to
change a series of cells, the ability to see many cells and directly
manipulate them, etc...) is a much more efficient way to do things.

Thanks again,
Tony
"Tom Ogilvy" wrote:

If you want the code to wait for the form to drop, then divide the code
into
two parts.

end the first by showing the form. Start the second from the form.

If the code is running, generally it wouldn't be appropriate for the user
to
be accessing the worksheet, so it isn't clear what you are trying to
accomplish,.

--
Regards,
Tom Ogilvy

"T_o_n_y" wrote in message
...
I'm looping because I don't really understand how a modeless form with
controls works. I'm used to having the code exit after the user form
is
dismissed.
When I don't loop the program shows the form but then "continues on"
executing code, which means that it proceeds to exit the program. I
don't
understand what I should put after

user.show

that will enable the program to still respond to the userform and yet
not
exit.

Thanks,
Tony
"Tom Ogilvy" wrote:

Why are you looping?


Use the click event of the goAwayClick box to run code to terminate
things.
(although I don't know why you wouldn't use a commandbutton for this).

--
Regards,
Tom Ogilvy






"T_o_n_y" wrote in message
...
Tom,

I have tried vbModeless, but not sucessfully for this. I'm not sure
how I
would modify my code to make this work, and simply adding vbModeless
resulting in the form being shown and then the program exiting.

My original code showing the form was like this.
Set user = New fLayout
response = vbNo
While response = vbNo
user.show
'exit program entirely when user clicks go away box
If user.goAwayClick Then
response = MsgBox("Are you sure you want to exit the
program
now?", vbYesNo)
Else
response = vbYes
End If
Wend
Unload user
Exit Sub

I also tried this, but it sent the program into a cpu loop.
Do
user.show vbModeless
Loop Until user.goAwayClick
Unload user

I have used vbModeless for userforms to display brief messages, but
not
with
controls on them. Could you provide some more help?

Thanks,
Tony

"Tom Ogilvy" wrote:

Userform1.show vbModeless

allows you to do what you describe. (xl2000 and later)

--
Regards,
Tom Ogilvy


"T_o_n_y" wrote in message
...
I have an Excel VBA routine that controls another application
(AutoCAD)
via
Excel userforms. While userforms are shown, you cannot use Excel
ells --
it's frozen. I like the fact that the user has full access to
AutoCAD
while
my Excel VBA routine is controlling it, but I'd also like them to
have
access
to Excel worksheets.

Please tell me if I'm right. I'm guessing that:
1) This cannot be done
2) I could do it if I was controlling both Excel & AutoCAD using
vb
(which
I
don't have)
3) I could do it if I was controlling both Excel & AutoCAD using
a
3rd
VBA
application such as Word (which I do have).
4) I might be able to do it if I put controls on a worksheet
rather
than
on
a userform?

Thanks,
-Tony