Forum Discussion
Solved
Use Form to update data
When the command button New Record is clicked the user enters a value into a Form which will then be entered into F7 (or the next available empty cell in F) When the user clicks OK E7 is then updat...
Your command button is a Form Control, not an ActiveX Control, so the macro should be in a standard module, and it should not be private.
Also, delete the instructions in F19:F23, or move them to another column.
See the attached version.
Assign the following macro to the command button:
Sub NewRecord()
Dim v As Double
Dim r As Range
On Error Resume Next
v = InputBox("Enter the amount")
If Err Then
Beep
Exit Sub
End If
On Error GoTo 0
Application.ScreenUpdating = False
Set r = Range("F" & Rows.Count).End(xlUp)
r.Offset(1, -1).Value = Date
r.Offset(1, 0).Value = v
r.Offset(0, 3).Resize(2, 2).FillDown
r.Offset(1, 5).Value = Range("J2").Value
Range("J2").Formula = "=AVERAGE(J4:J" & r.Row + 1 & ")"
Application.ScreenUpdating = True
End SubSave the workbook as a macro-enabled workbook (*.xlsm)
I am getting an error when I run the code
Please find macro enabled workbook. Thanks
https://1drv.ms/x/c/7f92b59c367af02d/EfEFrAZ-lUpGplcdw1AfhTsB2jIMorvc-e3si1B5D1IboQ?e=JpvsEn
Your command button is a Form Control, not an ActiveX Control, so the macro should be in a standard module, and it should not be private.
Also, delete the instructions in F19:F23, or move them to another column.
See the attached version.