Excel 97 - code hanging in userform.show method

[Katerine]

Hi all (me again). I apologize if this is very elementary.. I'm still learning Excel VBA.

I'm converting a paper survey to an Excel file with userforms for adding data, and I'm running into a problem.

I have the main form (the "Me" form in the code below), and four minor forms, that are opened if and when the user clicks on a corresponding checkbox. At the same time, a button appears for each corresponding checkbox, so the user can open the form at a later time.

So each time a checkbox is clicked Yes (or the corresponding command button is clicked), I'd like to have the main form disappear from view, and the minor form appears. Then when the user exits the minor form, the main form should reappear.

--- Begin Code ---
' Note: my original code has error handling,
' but it just clutters the example, so I removed it.

Private Sub SurveySections(chk As _
MSForms.CheckBox, ByVal cmd As MSForms.CommandButton)

cmd.Visible = chk.Value

If chk.Value = True Then
Select Case cmd.Name
Case "cmdFlightSurvey"
cmdFlightSurvey_Click
Case "cmdCarRentalSurvey"
cmdCarRentalSurvey_Click
Case "cmdHotelSurvey"
cmdHotelSurvey_Click
Case "cmdClassRegistrationSurvey"
cmdClassRegistrationSurvey_Click
Case Else
End Select
End If
End Sub

Private Sub chkAirTravel_AfterUpdate()
Call SurveySections(chkAirTravel, cmdFlightSurvey)
End Sub

Private Sub chkCarRental_AfterUpdate()
Call SurveySections(chkCarRental, cmdCarRentalSurvey)
End Sub

Private Sub chkClass_AfterUpdate()
Call SurveySections(chkClass, _
cmdClassRegistrationSurvey)
End Sub

Private Sub chkHotel_AfterUpdate()
Call SurveySections(chkHotel, cmdHotelSurvey)
End Sub

Private Sub cmdCarRentalSurvey_Click()
Load frmCarRental
Me.Hide
frmCarRental.Show
Me.Show
End Sub

Private Sub cmdClassRegistrationSurvey_Click()
Load frmClass
Me.Hide
frmClass.Show
Me.Show
End Sub

Private Sub cmdFlightSurvey_Click()
Load frmFlightPlan
Me.Hide
frmFlightPlan.Show
Me.Show
End Sub

Private Sub cmdHotelSurvey_Click()
Load frmHotel
Me.Hide
frmHotel.Show
Me.Show
End Sub
--- End Code ---

This works for the first checkbox checked. But then if I try to check another checkbox, it lags horribly. I stepped through the code and discovered that each time it runs into "Me.Show," the code just stops. That is, it shows the form and then doesn't proceed to the next line of code.

Which makes sense.. but what can I do instead? I've tried replacing "Me.Hide" with "Me.Visible=False", and "Me.Show" with "Me.Visible=True", but then I get errors.

Many many thanks in advance!

 

[LoNeRaVeR]

Katerine

I have two suggestions. Hide your current form and show your next form allowing the user action to call the subroutines.

Main Form Module Code
[tt]
If chk.Value = True Then
Select Case cmd.Name
Case "cmdFlightSurvey"
Me.Hide
frmFlightPlan.Show
Case...
[/tt]

Sub Form Module Code
[tt]
Private Sub cmdFlightSurvey_Click()
' Perform some action
frmFlightPlan.Hide
Me.Show
End Sub[/tt]

Initialize your forms. Each form should be initialized with a subroutine within it's own Module by using:
[tt]
Private Sub UserForm_Initialize()
' Initialize Values
End Sub
[/tt]
This initialization process will be run each time that form is called with [tt].Show[/tt].

Try that and let me know how it works.

Regards,

LoNeRaVeR