Userform disappears after pressing a button VBA

Question

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:

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

Try stepping through the code with F8 to determine where the problem is. Good info from the amazing Chip Pearson here. — ashleedawg
– ashleedawg, Commented Feb 24, 2018 at 4:49

DisplayName · Accepted Answer · 2018-02-24 09:59:17Z

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

I believe the Exit Sub should be under the Case Else statement?

Collectives™ on Stack Overflow

Userform disappears after pressing a button VBA

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related