Data Transfer from UserForm using Radio Buttons in Frames

JaesunB

New Member
Joined
Aug 13, 2016
Messages
4
1684362644488.png
1684362766578.png

VBA Code:
Private Sub cmdClose_Click()
    End
End Sub

Private Sub cmdSubmit_Click()

     
    'Priority Options
    If Pri1.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri1.Caption
    End If
    
    If Pri2.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri2.Caption
    End If
    
    If Pri3.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri3.Caption
    End If
    
    If Pri4.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri4.Caption
    End If
    
    If Pri5.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri5.Caption
    End If
    
    If Pri6.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri6.Caption
    End If
    
    If Pri7.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri7.Caption
    End If
    
    If Pri8.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri8.Caption
    End If
    
    'ICAO Input
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = txticao.Text
    
    'Tabs Options
    If TabsGreen.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TabsGreen.Caption
    End If
    
    If TabsRed.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TabsRed.Caption
    End If
    
    If TabsDOD.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TabsDOD.Caption
    End If
    
    'Title Input
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = txtTitle.Text
    
    'Document Name Input
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = txtDocumentName.Text
    
    'Types Options
    If TypesInt.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TypesInt.Caption
    End If
    
    If TypesMain.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TypesMain.Caption
    End If
    
    'Status Option
    If StatusAssi.Value = "True" Then
    Worksheets("Procedures").Cells(lastrow + 1, 2).Value = StatusAssi.Caption
    End If
    
    Call clearForm
           
End Sub


Sub clearForm()

    Pri1.Value = "False"
    Pri2.Value = "False"
    Pri3.Value = "False"
    Pri4.Value = "False"
    Pri5.Value = "False"
    Pri6.Value = "False"
    Pri7.Value = "False"
    Pri8.Value = "False"
    txticao.Value = ""
    TabsGreen.Value = "False"
    TabsRed.Value = "False"
    TabsDOD.Value = "False"
    txtTitle.Value = ""
    txtDocumentName.Value = ""
    TypesInt.Value = "False"
    TypesMain.Value = "False"
    StatusAssi.Value = "False"
    
End Sub

This code does not kick back any errors but does not place the data into the table as I need it to. The button for the Form is located on worksheet "Dashboard". The data needs to be input to the corresponding cells/columns in worksheet "Procedures". I have a different version of this form working with text and drop downs but I am trying to switch to radio buttons to speed up the input process.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi @JaesunB.
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Before I show you the solution, let me show you the problems in your code.
1)
If Pri1.Value = "True" Then
True, You must put it without the quotes.

2)
'ICAO Input Worksheets("Procedures").Cells(lastrow + 1, 2).Value = txticao.Text
'Tabs Options
If TabsGreen.Value = "True" Then
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = TabsGreen.Caption End If
All the data you are putting in column 2.
Better use the letter of the column so you can identify where the data goes.

3)
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri1.Caption
You are using the lastrow variable, but at the beginning of the code you are not filling the variable, so it is 0.

4)
'Priority Options
If Pri1.Value = "True" Then
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri1.Caption
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri2.Caption
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri3.Caption
You repeatedly use the Worksheets("Procedures") statement, you can simplify that.
--------------------------

I show you the corrected and simplified code:
Change your code to the following:
VBA Code:
Private Sub cmdClose_Click()
    End
End Sub

Private Sub cmdSubmit_Click()
  Dim sh As Worksheet
  Dim lastrow As Long, i As Long
 
  'Set sheet and last row
  Set sh = Sheets("Procedures")
  lastrow = sh.Range("B" & Rows.Count).End(3).Row + 1
 
  'Priority Options
  For i = 1 To 8
    If Controls("Pri" & i).Value = True Then
      sh.Range("A" & lastrow).Value = Controls("Pri" & i).Caption
    End If
  Next
 
  'ICAO Input
  sh.Range("B" & lastrow).Value = txticao.Text
 
  'Tabs Options
  If TabsGreen.Value = True Then sh.Range("C" & lastrow).Value = TabsGreen.Caption
  If TabsRed.Value = True Then sh.Range("C" & lastrow).Value = TabsRed.Caption
  If TabsDOD.Value = True Then sh.Range("C" & lastrow).Value = TabsDOD.Caption
 
  'Title Input
  sh.Range("D" & lastrow).Value = txtTitle.Text
 
  'Document Name Input
  sh.Range("E" & lastrow).Value = txtDocumentName.Text
 
  'Types Options
  If TypesInt.Value = True Then sh.Range("F" & lastrow).Value = TypesInt.Caption
  If TypesMain.Value = True Then sh.Range("F" & lastrow).Value = TypesMain.Caption
 
  'Status Option
  If StatusAssi.Value = True Then sh.Range("H" & lastrow).Value = StatusAssi.Caption
 
  Call clearForm
End Sub


Sub clearForm()

    Pri1.Value = "False"
    Pri2.Value = "False"
    Pri3.Value = "False"
    Pri4.Value = "False"
    Pri5.Value = "False"
    Pri6.Value = "False"
    Pri7.Value = "False"
    Pri8.Value = "False"
    txticao.Value = ""
    TabsGreen.Value = "False"
    TabsRed.Value = "False"
    TabsDOD.Value = "False"
    txtTitle.Value = ""
    txtDocumentName.Value = ""
    TypesInt.Value = "False"
    TypesMain.Value = "False"
    StatusAssi.Value = "False"
   
End Sub
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
 
Upvote 0
Hi @JaesunB.
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Before I show you the solution, let me show you the problems in your code.
1)

True
, You must put it without the quotes.

2)

All the data you are putting in column 2.
Better use the letter of the column so you can identify where the data goes.

3)

You are using the lastrow variable, but at the beginning of the code you are not filling the variable, so it is 0.

4)

You repeatedly use the Worksheets("Procedures") statement, you can simplify that.
--------------------------

I show you the corrected and simplified code:
Change your code to the following:
VBA Code:
Private Sub cmdClose_Click()
    End
End Sub

Private Sub cmdSubmit_Click()
  Dim sh As Worksheet
  Dim lastrow As Long, i As Long
 
  'Set sheet and last row
  Set sh = Sheets("Procedures")
  lastrow = sh.Range("B" & Rows.Count).End(3).Row + 1
 
  'Priority Options
  For i = 1 To 8
    If Controls("Pri" & i).Value = True Then
      sh.Range("A" & lastrow).Value = Controls("Pri" & i).Caption
    End If
  Next
 
  'ICAO Input
  sh.Range("B" & lastrow).Value = txticao.Text
 
  'Tabs Options
  If TabsGreen.Value = True Then sh.Range("C" & lastrow).Value = TabsGreen.Caption
  If TabsRed.Value = True Then sh.Range("C" & lastrow).Value = TabsRed.Caption
  If TabsDOD.Value = True Then sh.Range("C" & lastrow).Value = TabsDOD.Caption
 
  'Title Input
  sh.Range("D" & lastrow).Value = txtTitle.Text
 
  'Document Name Input
  sh.Range("E" & lastrow).Value = txtDocumentName.Text
 
  'Types Options
  If TypesInt.Value = True Then sh.Range("F" & lastrow).Value = TypesInt.Caption
  If TypesMain.Value = True Then sh.Range("F" & lastrow).Value = TypesMain.Caption
 
  'Status Option
  If StatusAssi.Value = True Then sh.Range("H" & lastrow).Value = StatusAssi.Caption
 
  Call clearForm
End Sub


Sub clearForm()

    Pri1.Value = "False"
    Pri2.Value = "False"
    Pri3.Value = "False"
    Pri4.Value = "False"
    Pri5.Value = "False"
    Pri6.Value = "False"
    Pri7.Value = "False"
    Pri8.Value = "False"
    txticao.Value = ""
    TabsGreen.Value = "False"
    TabsRed.Value = "False"
    TabsDOD.Value = "False"
    txtTitle.Value = ""
    txtDocumentName.Value = ""
    TypesInt.Value = "False"
    TypesMain.Value = "False"
    StatusAssi.Value = "False"
  
End Sub
--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
Thank you Dante Amor, this solution worked perfectly. Much appreciated.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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