User Forms: Can I have something between Modal and non-Modal?

[plh]

What I want to do is, have a form come up with information, but allow the user
to interact with the worksheet to the extent of being able to use the scroll
bars. Is there some way to do this? I've tried:
With frmMessage
.txtMessage = intNumLate
.Show (0)
End With
and
With frmMessage
.txtMessage = intNumLate
.Show (1)
End With
In the former case it leaves the form on the screen & blows right by it, in the
latter, the user cannot use the scroll bars.
Thanx,
-plh

 

[OssieMac]

If you only want the user to use the scroll bars but not change data then
protect the worksheet.

ActiveSheet.Protect Password:="OssieMac"
With frmMessage
.txtMessage = intNumLate
.Show (0)
End With

Following code Unprotects
ActiveSheet.Unprotect Password:="OssieMac"

 

[plh]

OssieMac,
I appreciate your reply but the problem still remains that with .Show (0)
it continues to execute code while leaving the form on the screen.
What I want to do is have it pause there AND allow the user to interact with the
worksheet, then have to code execution continue when the user closes the form.
In the case of msgbox and .Show (1) the execution pauses but the user can't
interact the worksheet.
Thanx,
-plh

 

[Chip Pearson]

When you display a user form modally,
UserForm1.Show vbModal ' =1
the code shows the form and then pauses execution on that line and
will not continue execution until the form is hidden or unloaded.
While the form is visible, the user can have no interaction with the
worksheet. Only controls on the user form can be used.

When you display a form modelessly,
UserForm1.Show vbModeless ' =0
the form is displayed but code execution continues on to whatever
follows the Show method. (I would get in the habit of using the
constant names vbModal and vbModeless rather than the numeric
equivelants of 1 and 0. It makes reading the code easier.) When a
modeless form is visible, the user has full access to all elements of
the worksheet.

There really isn't any way to individually enable or disable specific
elements of the Excel UI.

If all you want to do is allow the user to scroll around the worksheet
without any other interaction, put two scroll bars on the user form,
one oriented vertically and name it VScroll. The other scroll bar
should be oriented horizontally and named HScroll. Then, in your
userform's code, use

Private Sub UserForm_Initialize()
Me.VScroll.Min = 1
Me.VScroll.Max = 1000
Me.HScroll.Min = 1
Me.HScroll.Max = 100
End Sub

Private Sub HScroll_Change()
Static PrevCol As Long
Dim N As Long
If PrevCol = 0 Then
PrevCol = ActiveWindow.VisibleRange.Cells(1, 1).Column
End If
N = Me.HScroll.Value - PrevCol
ActiveWindow.SmallScroll toright:=N
PrevCol = ActiveWindow.VisibleRange.Cells(1, 1).Column
End Sub

Private Sub VScroll_Change()
Static PrevRow As Long
Dim N As Long
If PrevRow = 0 Then
PrevRow = ActiveWindow.VisibleRange.Cells(1, 1).Row
End If
N = Me.VScroll.Value - PrevRow
ActiveWindow.SmallScroll down:=N
PrevRow = ActiveWindow.VisibleRange.Cells(1, 1).Row
End Sub

Then, show the form modally:

UserForm1.Show vbModal

While the form is displayed, the user can use the scroll bars on the
user form to move about the worksheet. He won't have access to the
worksheet's scrollbars, but the scrollbars on the user form will mimic
the behavior of the worksheet's scrollbars well enough to serve the
purpose.

I can't think of any other way to display a modal form and still allow
interaction of any sort with the worksheet while the form is visible.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Andrew]

What if you display the form modelessly and in you calling sub have a
loop which waits for you to push the 'Continue' button, eg.
Do While Not blnContinue
blnContinue = frm.Continue
DoEvents
Loop
where Continue is a property within the form updated when user clicks
the button

You'd probably want to disable the form close 'x' to avoid the user
closing the form and getting stuck in the loop forever. Let me know
if you want to go here and I can try and dig some code out for that.

I'm not completely sure if you want the user to be able to edit the
worksheet or not but if not a trick could be to create an image of the
relevant sheet on a second dummy sheet (either protect cells or use
create a picture of it (View/Toolbars/Customise/Tools/Camera)). While
your code is running you can hide the real sheet and just show the
dummy mirror sheet.

Hope this is of some help.
Andrew