How to close a form(using the top right X) without entering the data

[Moreco]

Hi everyone,

I am not even sure how to explain what I need. I have 6 forms that are linked to a general form called frmDevices. Here is just a portion of the coding for the dependent userform (frmDevices) where each Case is for a different form:

Private Sub cmbDevice_Change()
Dim r As Range, r1 As Range, X As Variant
Dim Other As String

Set r = Worksheets("Devices").Range("B2").CurrentRegion
  ioffset = ioffset + 1
   Select Case cmbDevice.ListIndex


      Case 1 'DPB
        
        frmDPB.Show
        
        Set r1 = r.Offset(13, ioffset - 1)
        
        'To enter the values of textbox
        r1.Cells(1).Value = frmDPB.txtCoefFric1.Value
        r1.Cells(2).Value = frmDPB.txtCoefFric2.Value
        r1.Cells(3).Value = frmDPB.txtRCurvature1.Value
        r1.Cells(4).Value = frmDPB.txtRCurvature2.Value
        r1.Cells(5).Value = frmDPB.txtBearingD2.Value
        r1.Cells(6).Value = frmDPB.txtSliderDd1.Value
        r1.Cells(7).Value = frmDPB.txtSliderHeight.Value
        r1.Cells(8).Value = frmDPB.txtDispCapa.Value
        
        'To give color and borders to the input cells
        For X = 1 To 8
        r1.Cells(X).Interior.Color = RGB(255, 255, 204)
        r1.Cells(X).Borders.LineStyle = xlContinuous
        Next X
        
        Me.EnterData

Please note that the above code is just for Case 1.

I also have the following code that enters the data from the frmDevices userform into the spreadsheet:

Public Sub EnterData()
Dim r As Range, r1 As Range
Dim X As Variant
    
  If Me.txtDevCount = "" Then
    Me.txtDevCount.Value = 0
    ioffset = Me.txtDevCount.Value
  Else
    ioffset = Me.txtDevCount.Value
  End If

    Set r = Worksheets("Devices").Range("B2").CurrentRegion
    ioffset = ioffset + 1
    Me.txtDevCount.Value = ioffset
    
    Set r1 = r.Offset(5, ioffset)

    r1.Cells(1).Value = txtLabel.Value
    r1.Cells(2).Value = txtSerNum.Value
    r1.Cells(3).Value = txtDevManuf.Value
    r1.Cells(4).Value = txtDevSupplier.Value
    r1.Cells(5).Value = txtConnType.Value
    r1.Cells(6).Value = cmbDevCateg.Value
    r1.Cells(7).Value = cmbDevice.Value
    
    For X = 1 To 7
    r1.Cells(X).Interior.Color = RGB(255, 255, 204)
    r1.Cells(X).Borders.LineStyle = xlContinuous
    Next X

End Sub

And finally this is the code for the independent form for Case 1 called frmDPB:

Private Sub cmdDPBOK_Click()
Dim wdR1 As Double, wdR2 As Double, wdH As Double
Dim wdD1 As Double, wdD2 As Double, wdU As Double

  wdR1 = Me.txtRCurvature1
  wdR2 = Me.txtRCurvature2
  wdH = Me.txtSliderHeight
  wdD2 = Me.txtBearingD2
  wdD1 = Me.txtSliderDd1

  wdU = ((wdR2 - wdH / 2) / wdR2 + (wdR1 - wdH / 2) / wdR1) * (wdD2 - wdD1) / 2
   
  Me.txtDispCapa = wdU
  
 Me.UnitsOutput
 Me.Hide
 frmDevices.cmbDevice.ListIndex = 0
End Sub

The problem that I am having is that when I exit the independent form (frmDPB) by clicking on the X on the right top corner of the userform, the data from the frmDevices is getting entered and I don't want that to happen. How can I exit a userform and completly exit all subs that are dependent of that form?

Thanks for taking the time to read my question and helping me!!

Moreco

 

[SkipVought]

What application?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Skip,

I am sorry, I am working with Excel 2007 using VBA and userforms.

Thanks

 

[SkipVought]

"when I exit the independent form (frmDPB) by clicking on the X on the right top corner of the userform.... How can I exit a userform and completly exit all subs that are dependent of that form?"

Use the userform Terminate event to unload all your other forms.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Moreco]

Skip,

Thank you so much but I could not make it to work using the Terminate event. I was able to make it working using the following code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    Me.Hide
    frmDevices.cmbDevice.ListIndex = 0
  End If
End Sub

Moreco