Using Cells while UserForm shown

[Guest]

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

 

[Tom Ogilvy]

Userform1.show vbModeless

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

 

[Guest]

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]

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

 

[Guest]

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]

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,.

 

[Guest]

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]

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.

 

[Guest]

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