Forms/Forumula/Macro

jregan

New Member
Joined
Aug 28, 2014
Messages
4
Hello, I've created a form that populates a worksheet with personal health metrics such as systolic and diastolic blood pressure. However, I cannot find a way to calculate the ratio between systolic and diastolic when the command button is clicked and the data populates the next empty row. Please help!

Jordan
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Best to post a spreadsheet with dummy data.

Here is a print screen. When I complete the form the data populates the next empty row. I would like the F3 to populate with =D3/E3 once the data is submitted. You will find the form code below. Thanks for your help and let me know if you need any other information.


-- removed inline image ---


Form code:
Private Sub cmdsubmit_Click()
Dim RowCount As Long
'Range("A3").Value = txtdate.Text
'Range("B3").Value = txttemps.Text
'Range("C3").Value = txtpériode.Text
'Range("D3").Value=txtsystolic.Text
'Range("E3").Value=txtdiastolic.Text
'Range("G3").Value=txtheartrate.Text
'Range("H3").Value=txtbloodsugar.Text
'Range("J3").Value=txtweight.Text
'Range("K3").Value=txtmusclemass.Text
'Range("L3").Value=txtbodyfat.Text
'Range("M3").Value=txttbw.Text
'Range("N3").Value=txtbmr.Text
RowCount = Worksheets("JBR").Range("A1").CurrentRegion.Rows.Count
With Worksheets("JBR").Range("A1")
.Offset(RowCount, 0) = Me.txtdate.Value
.Offset(RowCount, 1) = Me.txttemps.Value
.Offset(RowCount, 2) = Me.txtpériode.Value
.Offset(RowCount, 3) = Me.txtsystolic.Value
.Offset(RowCount, 4) = Me.txtdiastolic.Value
.Offset(RowCount, 6) = Me.txtheartrate.Value
.Offset(RowCount, 7) = Me.txtbloodsugar.Value
.Offset(RowCount, 9) = Me.txtweight.Value
.Offset(RowCount, 10) = Me.txtmusclemass.Value
.Offset(RowCount, 11) = Me.txtbodyfat.Value
.Offset(RowCount, 12) = Me.txttbw.Value
.Offset(RowCount, 13) = Me.txtbmr.Value
End With
End Sub
Private Sub lbldate_Click()

End Sub
Private Sub lbltime_Click()

End Sub

Private Sub lblmedicalconditions_Click()

End Sub

Private Sub lblpériode_Click()

End Sub

Private Sub lblrife_Click()

End Sub

Private Sub UserForm_Activate()
txtdate.Text = Format(Now(), "MM/DD/YY")
End Sub
Private Sub txtdate_Change()
txttemps.Text = Format(Now(), "hh:mm")
End Sub
Private Sub txtsystolic_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtdiastolic_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtheartrate_txtheartrate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtbloodsugar_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtweight_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtmusclemass_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtbodyfat_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txttbw_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub txtbmr_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 46 Or KeyAscii = 32 Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox "Invalid Key"
End If
End Sub
Private Sub UserForm_Click()

End Sub
Private Sub ComboBox1_Change()

End Sub
 
Upvote 0
Sorry, I don't know how to post my worksheet so I created the table with only the pertinent information. To recap, the information in row 3 populated from a form. I would like a formula macro to calculate the systolic/diastolic and input into F3.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Temps[/TD]
[TD]Period[/TD]
[TD]Systolic[/TD]
[TD]Diastolic[/TD]
[TD]Ratio[/TD]
[/TR]
[TR]
[TD]26/08/2014[/TD]
[TD]13:39[/TD]
[TD]Dejeuner[/TD]
[TD]120[/TD]
[TD]80[/TD]
[TD]=D3/E3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top