How keep user form constant

[alexdetrano]

Hi I recently posted a question about a user form and got it working
and figured out how to get it into excel...however, the only problem
is it only runs as soon as excel starts up and then waits for the user
to exit out of the box. Also, while it is up, it restricts the spread
sheet from being edited. Is there a way I could have the box always
be there and be able to simultaneously edit the spreadsheet? Sorry if
it is a bit unclear.

thank you!

 

[matt]

Hi I recently posted a question about a user form and got it working
and figured out how to get it into excel...however, the only problem
is it only runs as soon as excel starts up and then waits for the user
to exit out of the box. Also, while it is up, it restricts the spread
sheet from being edited. Is there a way I could have the box always
be there and be able to simultaneously edit the spreadsheet? Sorry if
it is a bit unclear.

thank you!

You should be able to make the form Modeless and it will "float" in
the foreground until you unload or hide the form. It will allow you
to interact with Excel while the form is open.

Sub CommandButton1_Click()
frmExample.Show vbModeless
End Sub

Matt

 

[Guest]

the userform seems to be in modal.
you can get the userform show modal or modeless.
how about using "userform.show vbModeless"
when you write userform.show without vbModeless,
it shows in modal(vbModal is defalut)


msn (e-mail address removed)

 

[alexdetrano]

Hi I recently posted a question about a user form and got it working
and figured out how to get it into excel...however, the only problem
is it only runs as soon as excel starts up and then waits for the user
to exit out of the box. Also, while it is up, it restricts the spread
sheet from being edited. Is there a way I could have the box always
be there and be able to simultaneously edit the spreadsheet? Sorry if
it is a bit unclear.

thank you!

Thank you for your response! I have one more question...is there a
way I could either make the box stay on just one worksheet (say one
called rev and exp) rather than having it stay open while looking at
other worksheets? Or could I directly embed it into a worksheet?

 

[Guest]

I think it's almost impossible to embed it to the worksheet.
for many years, i've tried but failed.

if you can make the userform show only on a specific worjsheet,
you would have better use event procedure.

for example,
Private Sub Worksheet_Activate()
userform1.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
userform1.Hide
End Sub

 

[Guest]

Hi Alex -

One way would be to hide the form when the worksheet of interest is
deactivated and show the form when it is activated. Here's some code to do
that.

1. Procedure to load the form (copy to standard module):

Sub alex()
If ActiveSheet.Name = "YourWorksheetNameHere" Then
frmAlex.Show vbModeless
Else
Load frmYourForm
End If
End Sub

2. Procedures to automatically show or hide the form (copy to the worksheet
module):

Private Sub Worksheet_Activate()
frmYourForm.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
frmYourForm.Hide
End Sub