Using Multiple userforms

[Firefly]

I have a userform to input search criteria, which then generates another that displays the result allowing it to be deleted, changed or displayed. I use the Case call in form1 to determine which option it is. I'm having a problem with the change (which is the first section of code) - it completes the code, jumps out of the case select (delete & display follow change) to the userform_Terminate, & terminates the macro, never accessing form2. In order for terminate to actually terminate on request, I have to put code in to force it. When I do that, my macro always terminates. Only when I take that section out does it get to form2; but then the X on the user form doesn't work properly? Any suggestions? (Other than disabling the X?)

 

[Mark O'Brien]

Argh!!! My head hurts from reading that. j/k

Can you paste your code up on the board to make troubleshooting a little easier?

Multiple userforms are a pain in the testes, but once you get used to them, there's seldom a problem that can't be easily fixed.

 

[Firefly]

I've been experiencing this headache for 2 days now - trying to get a work around. I can either get the X to terminate, but not access the change function, or use the change function & the X doesn't end the macro properly. Any help is appreciated. Attached is the code section:

Dim textBoxes As New Collection ' Holds the form's text boxes
Dim totalFields As Integer      ' The total number of fields
Dim textboxNames() As String    ' Holds the names of the text boxes
Dim editname As String, dbrow As Integer, c As String
Dim ctrl As Control, Lstfld As Integer
Dim Prog As String, key As String
On Error GoTo Errorhandler
 ' Set up the custom textBoxes collection
 '
    For Each ctrl In Me.Controls
        If Left(ctrl.Name, 3) = "txt" Then
            textBoxes.Add Item:=ctrl, key:=ctrl.Name
        End If
    Next 'ctrl
 ' Get the total number of fields in the list
 ' and then redimension the array of text box names
 '
 totalFields = Range("Database").Columns.Count
 ReDim textboxNames(totalFields)
 '
 ' Initialize the array of text box names in the
 ' order they appear in the list.
 '
    textboxNames(0) = "txtNhood"
    textboxNames(1) = "txtPhase"
    textboxNames(2) = "txtLot"
    textboxNames(3) = "txtBlock"
    editname = txtNhood & txtPhase & txtLot & txtBlock
    If editname = "" Then
    Unload Me
    progend
    End If
   Select Case cmdAction.Caption
   Case "OK"
        Prog = "EditLot"
        Range("aw1").Select
        Cells.Find(What:=editname, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        dbrow = ActiveCell.Row
      Unload Me
      Case "Delete"
           editname = txtNhood & txtPhase & txtLot & txtBlock
           Prog = "DeleteLot"
           Range("aw1").Select
           Cells.Find(What:=editname, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
           False).Activate
           dbrow = ActiveCell.Row
           Unload Me
     Case "Display"
           editname = txtNhood & txtPhase & txtLot & txtBlock
           Prog = "Showlot"
           Range("aw1").Select
           Cells.Find(What:=editname, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
           False).Activate
           dbrow = ActiveCell.Row
           Unload Me
        End Select
Errorhandler:
    erchg editname, Prog
    Unload Me
End Sub
Private Sub cmdCancel_Click()
    Unload Me
    progend
End Sub

Private Sub UserForm_Terminate()
   Set textBoxes = Nothing
   progend
End Sub
Sub erchg(editname, Prog)
MsgBox editname & " is not a valid lot ID.  Please try again."
Unload Me
Application.Run (Prog)
End Sub

This message was edited by Juan Pablo G. on 2002-08-27 15:05

 

[Mark O'Brien]

I'm puzzled as to why you are using the Select CAse at all. Wouldn't it be easier to just use the "Click" events for each one of your buttons that are supplied on the forms? This would help to keep your code separate. Or am I missing something here?

EDIT:: Did you leave out the first line of cod ein your example? What event is raised to run the first bit of your code?
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-08-27 16:00

 

[Firefly]

Mark - you're talking to a relative novice here. I found a piece of code & am trying to modify it to meet my purposes (doesn't if it doesn't work). The first line of code is: Private Sub cmdAction_Click(). The source program assigns the caption on the form and the two user buttons, because the names change with the Change, Delete, or Display source program. Only the "X", Cancel , and input fields remain the same. If the Click would be simpler - can you show me how I'd tailor it to one of the separate functions? Really appreciate your help.