Make form active (set focus)

[Satish]

Hi all,

This might be probably a very stupid question -

I have a spreadsheet with a command button on one of the sheets. When
the user clicks the button, I show the user a form, where he does his
stuff. When the user clicks a button on the form, I do some processing
and show a message "blah blah done successfully". User clicks OK and
after that, the active window is the Excel spreadsheet and not the
form.

How can I make the form the active window?

Thanks for reading/replying
Satish

 

[acampbell012]

Sounds like you want to give the user the option to continue data entry
after the "process successful..." message. Try changing the style of
your message box to a yes/no.

Below is a section of code I use in one of my spreadsheets that reacts
to the yes/no response from the user. Just have the yes response loop
back to the form.

Dim Msg, Style, Title2, Help, Ctxt, Response, MyString
Msg = "Starting number entered appears to have been used already.
Continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title2 = "Confirm Starting Control Number"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
GoTo Continue2:
Else ' User chose No.
GoTo Handle:
End If
End If

 

[Satish]

No...actually, I just want the window to be active...

I have seen macros where a form appears on the click of a button
(button is on the spreadsheet), and the user cannot select anything on
the spreadsheet without closing the form. In my case, when the form is
shown to the user, the user can still select cells on the spreadsheet
behind the form.

Something to do with Modal/Modeless?

The way I show the form is UserForm1.Show

I think default is Modeless?

-Satish

 

[acampbell]

In the userform properties window set ShowModal to TRUE

 

[Satish]

Yes, but still the user is select the spreadsheet cells when the form
is shown. How to disable that?

 

[acampbell]

This should prevent the user from making any changes in the sheet
behind the form until it is closed.

 

[Satish]

Yes it does. But the problem is after I show message with a msgbox, I
lose focus on the form and the user can select the spreadsheet behind.

I dont know why that is happening.
Any ideas?

Thanks
Satish

 

[Satish]

Any ideas why after displaying a msgbox, the user is able to select
Excel spreadsheet? When the form is shown for the first time, the user
cannot select the spreadsheet. It is only after I display some message
on a msgbox, that the user is able to select the spreadsheet...

Is it some kind of bug??
or I must be doing something wrong? :s

-Satish

 

[NickHK]

This code works in XL2K and 2002 and expected - the user cannot interact
with the WS until the userform is dismissed.

<WS code>
Private Sub CommandButton1_Click()
UserForm1.Show vbModal
End Sub
</WS code>

<Userform code>
Private Sub CommandButton1_Click()
MsgBox "Show MsgBox"
End Sub
</Userform code>

Unless you have some other code or maybe a RefEdit control (???)

NickHK