Looking for assistance with specifying where user form data is entered on a sheet.

Javi

Active Member
Joined
May 26, 2011
Messages
440
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.


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


1620773499576.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
I think you need a better way to set iRow to the current row number, and you appear to be missing a needed "With Activesheet" statement at the top

VBA Code:
Private Sub CommandButton1_Click()
    
    Dim iRow As Long
    
    'iRow = ActiveSheet.Range
    iRow = ActiveCell.Row
    With ActiveSheet
        '.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"
 
Upvote 0
You can also loop through the controls like
VBA Code:
Private Sub CommandButton1_Click()
    
    Dim iRow As Long, i As Long
    
    'iRow = ActiveSheet.Range
    iRow = ActiveCell.Row
    With ActiveSheet
        '.Range("A" & iRow).Value = Me.txtName.Value
        
      For i = 1 To 12
         If Me.Controls("OptionButtonH" & i) Then
            .Range("L" & iRow) = i
            Exit For
         End If
      Next i
 
Upvote 0
You can also loop through the controls like
VBA Code:
Private Sub CommandButton1_Click()
   
    Dim iRow As Long, i As Long
   
    'iRow = ActiveSheet.Range
    iRow = ActiveCell.Row
    With ActiveSheet
        '.Range("A" & iRow).Value = Me.txtName.Value
       
      For i = 1 To 12
         If Me.Controls("OptionButtonH" & i) Then
            .Range("L" & iRow) = i
            Exit For
         End If
      Next i
Thank you Worked great!!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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