Data Transfer from UserForm using Radio Buttons in Frames

J

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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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)
JaesunB said:
If Pri1.Value = "True" Then
Click to expand...
True, You must put it without the quotes.

2)
JaesunB said:
'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
Click to expand...
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)
JaesunB said:
Worksheets("Procedures").Cells(lastrow + 1, 2).Value = Pri1.Caption
Click to expand...
You are using the lastrow variable, but at the beginning of the code you are not filling the variable, so it is 0.

4)
JaesunB said:
'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
Click to expand...
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
DanteAmor said:
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
--------------
Click to expand...
Thank you Dante Amor, this solution worked perfectly. Much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
285
Domenic
D
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
51
Skyybot
Skyybot
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
116
RoryA
RoryA
I
  • Question Question
Add ID number next to customer row from userform transfer
Replies
2
Views
324
Joe4
Joe4
C
  • Question Question
How to automatically paste the data from one sheet to another using VBA
Replies
4
Views
291
Alex Blakenburg
A

Forum statistics

Threads
1,224,814
Messages
6,181,127
Members
453,021
Latest member
Justyna P

Share this page

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