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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

E
  • Solved
Copying data from 1 sheet to another in vba
Replies
7
Views
185
Kaper
Kaper
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
64
VanGirl
V
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
287
Gajendran Yadhav
G
S
  • Solved
Display data of Specific Sheets on Listbox Userform
2
Replies
11
Views
863
Akuini
Akuini
R
  • Solved
How do I extract the data label of a trendline in VBA?
2
Replies
14
Views
904
RockandGrohl
R

Forum statistics

Threads
1,214,945
Messages
6,122,397
Members
449,081
Latest member
JAMES KECULAH

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