none
Reset button RRS feed

  • Question

  • This si the code i am using for my BMI\Age calculator, i am just wondering how i can add a reset button so i can reset the inputs already placed into the calculator so i can start another calculation entry.  

    Code: 

    Private Sub cmdCalculate_Click()
    'Declare the variables
    Dim AddAge As Long
    Dim myAge As Range
    Dim BMI As Range
    'Set the result range
    Set myAge = Sheet1.Range("G8")
    'Variable for resut
    Set BMI = Sheet1.Range("H11")
    'Check for DOB value
    If IsDate(Me.txtDOB.Value) And Me.txtDOB.Value <> 0 Then
    'Add date of birth to worksheet
    Sheet1.Range("F8") = Me.txtDOB.Value
    'Show formula result in a message box
    Me.txtAge.Value = myAge.Value
    Else
    MsgBox "you need to add a proper date"
    txtDOB.Value = " "
    Exit Sub
    End If
    'check for Weight value and add value
    If IsNumeric(txtWeight) And txtWeight <> 0 Then
    Sheet1.Range("G11") = Format(Me.txtWeight.Value, "###0.0")
    Else
    MsgBox "You must add a weight"
    Exit Sub
    End If
    'Show the BMI result
    Me.txtBMI = BMI
    Me.txtBMI.Value = Format(BMI, "#,##0.0")
    'show the BMI clasification
    If BMI < 18.5 Then
    MsgBox "you are underweight"
    End If

    If BMI >= 18.5 And BMI < 25 Then
    MsgBox "You are in normal weight"
    End If

    If BMI >= 25 And BMI < 30 Then
    MsgBox "you are Overweight"
    End If

    If BMI > 30 Then
    MsgBox "You are Obese"
    End If
    End Sub

    Private Sub cmdClose_Click()
     Unload Me
     
    End Sub

    Monday, November 13, 2017 6:40 PM

All replies

  • Hello,

    This forum is for VB.NET. I can move your question to either a VBA forum or the "Excel for developers" forum. If VB6 or earlier use the following forum.

    http://www.vbforums.com/forumdisplay.php?1-Visual-Basic-6-and-Earlier


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 13, 2017 6:45 PM
  • i would appreciate it, i am currently using excel 2016 so if you could send me to the correct place? 
    Monday, November 13, 2017 7:00 PM
  • i would appreciate it, i am currently using excel 2016 so if you could send me to the correct place? 

    Moving your question now :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 13, 2017 7:17 PM
  • HcirmBMIc
    Re:  clear controls on userform in excel
    Add a "reset" button to the form and have it call the StartNew sub.
    You will have to specify the syntax which will vary depending on the type of control.
    Properly set up the code could be used (as is) in other projects now or in the future.

    Sub StartNew()
     Dim objCtrl As Object
     For Each objCtrl In Me.Controls
      If TypeOf objCtrl Is MSForms.TextBox Then
        objCtrl.Value = vbNullString
      ElseIf TypeOf objCtrl Is MSForms.ComboBox Then
        objCtrl.List.Index...
      ElseIf TypeOf objCtrl Is MSForms.Label Then
        objCtrl...
      ElseIf...

      End If
     Next
    End Sub
    '---
     'msforms.CheckBox
     'msforms.ComboBox
     'msforms.CommandButton
     'msforms.Control
     'msforms.Image
     'msforms.Label
     'msforms.ListBox
     'msforms.OptionButton
     'msforms.ScrollBar
     'msforms.SpinButton
     'msforms.TextBox

    '---
    Another way is to simply unload the form and load it again.
    If your code is for some public access use where it might be executed dozens or hundreds of times, I would clear the form
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2   (Dropbox)

    (free & commercial excel add-ins & workbooks)

    Tuesday, November 14, 2017 2:02 AM