ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Problems with Multiple Instances of User From (https://www.excelbanter.com/excel-programming/316124-problems-multiple-instances-user.html)

DJ

Problems with Multiple Instances of User From
 
I have an Excel macro that displays a user form to capture user input. When
the OK command button is clicked the code assigns values from the form's
objects (text boxes and combo boxes) to various variables, then performs
error checks on the input data. If an error is found, I hide the user form,
execute a Select Case statement to set the focus to the form object
containing the invalid data, then redisplay the form using the following
code:

If Not InputError = Empty Then

frmAirbillInput.Hide
Select Case InputError

Case "InvalidZIPCode"
With tbZIPCode
.SelStart = 0
.SelLength = Len(.Text)
frmAirbillInput.tbZIPCode.SetFocus
End With

Case ...

Case ...

End Select

frmAirbillInput.Show
End if

Unload frmAirbillInput
Application.ScreenUpdating = True
frmProgress.Show

End Sub

If there are no errors in the input data, all works well. However, when
invalid input data is entered that causes the form to be redisplayed by one
of the Select statements, the program acts as though there are multiple
instances of the same form opened. For example, when I'm stepping through
the program and there are no input errors, control is returned from
frmProgress, flows to the End Sub statement and the program exits. When
there are input errors causing the form to be redisplayed one or more times
by a Select statement, when I step through the program control returns from
frmProgress, flows to the End Sub, then flows to the End if statement.

What am I missing or doing wrong? All assistance is highly appreciated!

Thanks,

DJ



Stephen Bullen[_4_]

Problems with Multiple Instances of User From
 
Hi Dj,

What am I missing or doing wrong? All assistance is highly appreciated!


Don't hide-then-show the form. Assuming this is in the btnOK_Click event:

If Not InputError = Empty Then

'An Error, so set the focus to that control
Select Case InputError

Case "InvalidZIPCode"
With tbZIPCode
.SelStart = 0
.SelLength = Len(.Text)
frmAirbillInput.tbZIPCode.SetFocus
End With

Case ...

Case ...

End Select

Else
'No Error, so unload the form and carry on
Unload frmAirbillInput
Application.ScreenUpdating = True
frmProgress.Show
End if

End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk




All times are GMT +1. The time now is 11:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com