I am trying to do the following.

The user enters a date (StopDate) in a UserForm text box.
The program checks the date , then asks the User if she wants to change
the date
The User says Yes, then changes the date in the text box, and the
program continues with the new value.

I have the following

UserForm_Initialize( )

UserForm.Show vbModeless

and in the main program I have:

res = MsgBox("Stop Date: " & Dates(iLastDay) & " Yes to Continue, No to
Change Date", vbYesNo + vbCritical)

If res = vbNo Then
UserForm1.Show 'Expecting the UserForm to become active to allow the
User to make the changes
MsgBox "Stop Date Changed ?"
TCStopDate = txtStopDate.Value 'The program picks up the new date
and continues

However this does not work.. The UserForm does not activate to allow the
user to make changes.

Appreciate any suggestions thank you

Hi

Don't put show in the initialize event, call it whan you need it from the main code. Try
hide-show like this:

Sub test()
MsgBox "Starting modeless"
UserForm1.Show vbModeless
If MsgBox("Modal ?", vbYesNo + vbQuestion) = vbYes Then
UserForm1.Hide
UserForm1.Show
End If
End Sub

A modeless form does no good combined with messageboxes, since those are /very/ modal. You
should imo use labels and buttons on the userform for information and interaction, not
those annoying msgboxes. But you may of course have your reasons.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
I am trying to do the following.

The user enters a date (StopDate) in a UserForm text box.
The program checks the date , then asks the User if she wants to change
the date
The User says Yes, then changes the date in the text box, and the
program continues with the new value.

I have the following

UserForm_Initialize( )

UserForm.Show vbModeless

and in the main program I have:

res = MsgBox("Stop Date: " & Dates(iLastDay) & " Yes to Continue, No to
Change Date", vbYesNo + vbCritical)

If res = vbNo Then
UserForm1.Show 'Expecting the UserForm to become active to allow the
User to make the changes
MsgBox "Stop Date Changed ?"
TCStopDate = txtStopDate.Value 'The program picks up the new date
and continues

However this does not work.. The UserForm does not activate to allow the
user to make changes.

Appreciate any suggestions thank you

Hello

Thanks. My problem seems to be that when the MsgBox message is displayed the UserForm is
disabled although the it's visible.

Is there another to way to tell the User that the date is invalid and to submit another one.

Thanks

Harald Staff wrote:

Hi

Don't put show in the initialize event, call it whan you need it from the main code. Try
hide-show like this:

Sub test()
MsgBox "Starting modeless"
UserForm1.Show vbModeless
If MsgBox("Modal ?", vbYesNo + vbQuestion) = vbYes Then
UserForm1.Hide
UserForm1.Show
End If
End Sub

A modeless form does no good combined with messageboxes, since those are /very/ modal. You
should imo use labels and buttons on the userform for information and interaction, not
those annoying msgboxes. But you may of course have your reasons.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
I am trying to do the following.

The user enters a date (StopDate) in a UserForm text box.
The program checks the date , then asks the User if she wants to change
the date
The User says Yes, then changes the date in the text box, and the
program continues with the new value.

I have the following

UserForm_Initialize( )

UserForm.Show vbModeless

and in the main program I have:

res = MsgBox("Stop Date: " & Dates(iLastDay) & " Yes to Continue, No to
Change Date", vbYesNo + vbCritical)

If res = vbNo Then
UserForm1.Show 'Expecting the UserForm to become active to allow the
User to make the changes
MsgBox "Stop Date Changed ?"
TCStopDate = txtStopDate.Value 'The program picks up the new date
and continues

However this does not work.. The UserForm does not activate to allow the
user to make changes.

Appreciate any suggestions thank you

There are many ways, which one to choose is a question of style and taste. Here's one
solution with Textbox1, Label1 and CommandButton1 (the "OK" button, "Next" button,
whatever). In this demo the user must enter a valid future date in the date format of her
own choice. Note that "datevalue" says "this year" if no year is entered, so Jan 1. is
interpreted as Jan 1. 2003.

Private Sub CommandButton1_Click()
Dim D As Date
If IsDate(TextBox1.Text) Then
D = DateValue(TextBox1.Text)
If D < Date Then
Label1.Caption = "That's the past!"
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
TextBox1.SetFocus
Else
'date is fine, whatever ordinary button actions here
Unload Me 'close form, if desired
End If
Else
Label1.Caption = "Please enter a proper date."
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
TextBox1.SetFocus
End If
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
Hello

Thanks. My problem seems to be that when the MsgBox message is displayed the UserForm
is
disabled although the it's visible.

Is there another to way to tell the User that the date is invalid and to submit another
one.

Thanks

Harald Staff wrote:

Hi

Don't put show in the initialize event, call it whan you need it from the main code.
Try
hide-show like this:

Sub test()
MsgBox "Starting modeless"
UserForm1.Show vbModeless
If MsgBox("Modal ?", vbYesNo + vbQuestion) = vbYes Then
UserForm1.Hide
UserForm1.Show
End If
End Sub

A modeless form does no good combined with messageboxes, since those are /very/ modal.
You
should imo use labels and buttons on the userform for information and interaction, not
those annoying msgboxes. But you may of course have your reasons.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
I am trying to do the following.

The user enters a date (StopDate) in a UserForm text box.
The program checks the date , then asks the User if she wants to change
the date
The User says Yes, then changes the date in the text box, and the
program continues with the new value.

I have the following

UserForm_Initialize( )

UserForm.Show vbModeless

and in the main program I have:

res = MsgBox("Stop Date: " & Dates(iLastDay) & " Yes to Continue, No to
Change Date", vbYesNo + vbCritical)

If res = vbNo Then
UserForm1.Show 'Expecting the UserForm to become active to allow the
User to make the changes
MsgBox "Stop Date Changed ?"
TCStopDate = txtStopDate.Value 'The program picks up the new date
and continues

However this does not work.. The UserForm does not activate to allow the
user to make changes.

Appreciate any suggestions thank you

Thank you

Unfortunately I am still having problems. I used the Do Until command to pause the program
while the User inputs the new date. However, the UserForm disappears (or partially visible and
stuck) so its not possible to make the change. I tried UserForm1.Show as shown in the coding
below, but this yields an error (the Userform is already showing). Appreciate any suggestions.

If DateFound = 0 Then 'The date provided is not valid
LabelMessage.Caption = "Database does not contain specified Stop Date. Please enter new
Stop Date"

txtStopDate.Value = ""
txtStopDate.SelStart = 0
txtStopDate.SelLength = Len(txtStopDate.Text)
txtStopDate.SetFocus

Do Until Len(txtStopDate) = 8
UserForm1.Show
Loop
GoTo StopDate 'This validates the newly provided

End If


Harald Staff wrote:

There are many ways, which one to choose is a question of style and taste. Here's one
solution with Textbox1, Label1 and CommandButton1 (the "OK" button, "Next" button,
whatever). In this demo the user must enter a valid future date in the date format of her
own choice. Note that "datevalue" says "this year" if no year is entered, so Jan 1. is
interpreted as Jan 1. 2003.

Private Sub CommandButton1_Click()
Dim D As Date
If IsDate(TextBox1.Text) Then
D = DateValue(TextBox1.Text)
If D < Date Then
Label1.Caption = "That's the past!"
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
TextBox1.SetFocus
Else
'date is fine, whatever ordinary button actions here
Unload Me 'close form, if desired
End If
Else
Label1.Caption = "Please enter a proper date."
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
TextBox1.SetFocus
End If
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
Hello

Thanks. My problem seems to be that when the MsgBox message is displayed the UserForm
is
disabled although the it's visible.

Is there another to way to tell the User that the date is invalid and to submit another
one.

Thanks

Harald Staff wrote:

Hi

Don't put show in the initialize event, call it whan you need it from the main code.
Try
hide-show like this:

Sub test()
MsgBox "Starting modeless"
UserForm1.Show vbModeless
If MsgBox("Modal ?", vbYesNo + vbQuestion) = vbYes Then
UserForm1.Hide
UserForm1.Show
End If
End Sub

A modeless form does no good combined with messageboxes, since those are /very/ modal.
You
should imo use labels and buttons on the userform for information and interaction, not
those annoying msgboxes. But you may of course have your reasons.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"K" wrote in message ...
I am trying to do the following.

The user enters a date (StopDate) in a UserForm text box.
The program checks the date , then asks the User if she wants to change
the date
The User says Yes, then changes the date in the text box, and the
program continues with the new value.

I have the following

UserForm_Initialize( )

UserForm.Show vbModeless

and in the main program I have:

res = MsgBox("Stop Date: " & Dates(iLastDay) & " Yes to Continue, No to
Change Date", vbYesNo + vbCritical)

If res = vbNo Then
UserForm1.Show 'Expecting the UserForm to become active to allow the
User to make the changes
MsgBox "Stop Date Changed ?"
TCStopDate = txtStopDate.Value 'The program picks up the new date
and continues

However this does not work.. The UserForm does not activate to allow the
user to make changes.

Appreciate any suggestions thank you