German III Felisarta
German III Felisarta

Reputation: 21

Userform disappears after pressing a button VBA

I have this program where I have to calculate the BMI. It works well except for one thing, when I press the "New Entry" button to add an entry to the spreadsheet, the userform disappears but the window frame of the userform does not.

Please see attached images:

The Userform before doing anything


After pressing the "New Entry" button

here is the code for the "New Entry" Button:

    Private Sub newEntryButton_Click()

     Dim lastName As String
     Dim firstName As String
     Dim middleName As String 
     Dim birthYear As Integer

     lastName = lastNameText.Text
firstName = firstNameText.Text
middleName = middleNameText.Text
birthYear = birthCmbx.Value
weight = weightText.Text
height = heightText.Value

If maleOpt.Value = True Then
    maleTab.Activate
    Sheets("maleTab").Cells(x, 1) = lastName    'last name'
    Sheets("maleTab").Cells(x, 2) = firstName   'first name'
    Sheets("maleTab").Cells(x, 3) = middleName  'middle name'
    Sheets("maleTab").Cells(x, 4) = birthCmbx.Value 'birth year'
    Sheets("maleTab").Cells(x, 5) = ageDiff(birthYear) 'age'
    Sheets("maleTab").Cells(x, 6) = weight  'weight'
    Sheets("maleTab").Cells(x, 7) = height  'height'
    Sheets("maleTab").Cells(x, 8) = bmiFactorNum.Text
    Sheets("maleTab").Cells(x, 9) = bmiFactorText.Text
    x = x + 1
ElseIf femaleOpt.Value = True Then
    femaleTab.Activate
    Sheets("femaleTab").Cells(x2, 1) = lastName
    Sheets("femaleTab").Cells(x2, 2) = firstName
    Sheets("femaleTab").Cells(x2, 3) = middleName
    Sheets("femaleTab").Cells(x2, 4) = birthCmbx.Value
    Sheets("femaleTab").Cells(x2, 5) = ageDiff(birthYear)
    Sheets("femaleTab").Cells(x2, 6) = weight
    Sheets("femaleTab").Cells(x2, 7) = height
    Sheets("femaleTab").Cells(x2, 8) = bmiFactorNum.Text
    Sheets("femaleTab").Cells(x2, 9) = bmiFactorText.Text
    x2 = x2 + 1
Else
    MsgBox "Please select a gender"
End If

End Sub

Upvotes: 0

Views: 1262

Answers (1)

DisplayName
DisplayName

Reputation: 13386

maybe all that Activate is doing any harm... or there's some event handling that gets triggered at every cell writing

try with this code

Option Explicit

Private Sub newEntryButton_Click()
    Dim targetSht As Worksheet

    Select Case True
        Case maleOpt.Value
            Set targetSht = Worksheets("maleTab")
        Case femaleOpt.Value
            Set targetSht = Worksheets("femaleTab")
        Case Else
            MsgBox "Please select a gender"
            Exit Sub
    End Select

    Application.EnableEvents = False ' disable events    
    With Me 'reference your userform
        'write in targetSht 9 columns-1 row range starting from column A first empty cell after last not empty one
        targetSht.Cells(targetSht.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 9) = Array( _
                 .lastNameText.Text, _
                 .firstNameText.Text, _
                 .middleNameText.Text, _
                 .birthCmbx.Value, _
                 ageDiff(.birthCmbx.Value), _
                 .weightText.Text, _
                 .heightText.Value, _
                 .bmiFactorNum.Text, _
                 .bmiFactorText.Text)
    End With
    Application.EnableEvents = True' turn events handing on
End Sub

Upvotes: 2

Related Questions