Looking for some assistance with specifying where user form data is entered on a sheet.
I would like the below code to enter the data from the user form into the active sheet as follows. (I will be using the User form on several sheets so I don’t want to specify the sheet name in the code if I can avoid, would like to use active sheet)
When a cell is selected in column “C” the user form code will enter the data in column L, M and M in the same row as the selected/active cell. No data will be entered into column C at all.
I’ve made several attempts but failed your assistance is appreciated.
I would like the below code to enter the data from the user form into the active sheet as follows. (I will be using the User form on several sheets so I don’t want to specify the sheet name in the code if I can avoid, would like to use active sheet)
When a cell is selected in column “C” the user form code will enter the data in column L, M and M in the same row as the selected/active cell. No data will be entered into column C at all.
I’ve made several attempts but failed your assistance is appreciated.
VBA Code:
Option Explicit
Private Sub CommandButton1_Click()
Dim iRow As Long
iRow = ActiveSheet.Range
'.Range("A" & iRow).Value = Me.txtName.Value
'Hour
If Me.OptionButtonH1.Value Then .Range("L" & iRow).Value = "1"
If Me.OptionButtonH2.Value Then .Range("L" & iRow).Value = "2"
If Me.OptionButtonH3.Value Then .Range("L" & iRow).Value = "3"
If Me.OptionButtonH4.Value Then .Range("L" & iRow).Value = "4"
If Me.OptionButtonH5.Value Then .Range("L" & iRow).Value = "5"
If Me.OptionButtonH6.Value Then .Range("L" & iRow).Value = "6"
If Me.OptionButtonH7.Value Then .Range("L" & iRow).Value = "7"
If Me.OptionButtonH8.Value Then .Range("L" & iRow).Value = "8"
If Me.OptionButtonH9.Value Then .Range("L" & iRow).Value = "9"
If Me.OptionButtonH10.Value Then .Range("L" & iRow).Value = "10"
If Me.OptionButtonH11.Value Then .Range("L" & iRow).Value = "11"
If Me.OptionButtonH12.Value Then .Range("L" & iRow).Value = "12"
'Minute
If Me.OptionButtonM00.Value Then .Range("M" & iRow).Value = "00"
If Me.OptionButtonM15.Value Then .Range("M" & iRow).Value = "15"
If Me.OptionButtonM30.Value Then .Range("M" & iRow).Value = "30"
If Me.OptionButtonM45.Value Then .Range("M" & iRow).Value = "45"
'Minute
If Me.OptionButtonAM.Value Then .Range("N" & iRow).Value = "AM"
If Me.OptionButtonPM.Value Then .Range("N" & iRow).Value = "PM"
End With
'Reset the controls after submitting
Me.OptionButtonH1.Value = False
Me.OptionButtonH2.Value = False
Me.OptionButtonH3.Value = False
Me.OptionButtonH4.Value = False
Me.OptionButtonH5.Value = False
Me.OptionButtonH6.Value = False
Me.OptionButtonH7.Value = False
Me.OptionButtonH8.Value = False
Me.OptionButtonH9.Value = False
Me.OptionButtonH10.Value = False
Me.OptionButtonH11.Value = False
Me.OptionButtonH12.Value = False
Me.OptionButtonM00.Value = True
Me.OptionButtonM15.Value = False
Me.OptionButtonM30.Value = False
Me.OptionButtonM45.Value = False
Me.OptionButtonAM.Value = False
Me.OptionButtonPM.Value = False
MsgBox "Data submitted Successfully!"
End Sub