Userform VBA Check Box when checke and command button selected Copy and paste Userform data to worksheet

M

MRN227

New Member
Joined
Dec 18, 2018
Messages
15
Apologies for the lengthy title. I have never done anything using a check box. What needs to be accomplished is when the check box is selected all the data that is in the userform will be copied and redirected to a worksheet. Currently when the command button is selected it copies all the data and pastes into two different worksheets, but I'm trying to make it where if the box is checked it copies only to a completely different tab. Current userform code is below:

Code: 
Private Sub CommandButton1_Click()
Dim lg As Worksheet
Set lg = Sheets("Log")
Set bol = Sheets("BOL")
Set Slog = Sheets("Seal Log_Make Changes Here")

bol.Cells(4, 14) = Me.DoorCB
bol.Cells(5, 2) = CDate(Me.DateTB)
bol.Cells(11, 10) = Me.SortTB
bol.Cells(12, 10) = Me.TrailerCB
bol.Cells(13, 3) = Me.AddressTB
bol.Cells(14, 10) = Me.SealTB
bol.Cells(15, 10) = Me.CarrierCB
bol.Cells(13, 10) = Me.VRIDTB
bol.Cells(36, 1) = Me.QtyTB
bol.Cells(36, 5) = Me.WeightTB
bol.Cells(50, 7) = Me.CPTTB
bol.Cells(50, 2) = Me.ArrivalTB
bol.Cells(51, 2) = Me.DepartTB

lg.Select
lg.Range("A2:J2").Select
Selection.ListObject.ListRows.Add (1)
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

lg.Cells(2, 1) = CDate(Me.DateTB)
lg.Cells(2, 2) = Me.SortTB
lg.Cells(2, 3) = Me.CarrierCB
lg.Cells(2, 4) = Me.TrailerCB
lg.Cells(2, 5) = Me.VRIDTB
lg.Cells(2, 6) = Me.SealTB
lg.Cells(2, 7) = Me.QtyTB
lg.Cells(2, 8) = Me.WeightTB
lg.Cells(2, 9) = Me.DoorCB
lg.Cells(2, 10) = Me.CaseTB
lg.Cells(2, 12) = Me.ArrivalTB
lg.Cells(2, 13) = Me.DepartTB
lg.Cells(2, 15) = UCase(Me.InitalsTB)

Slog.Select
Slog.Range("G1:M1").Select
Selection.Insert Shift:=xlDown
Slog.Cells(7, 7) = Me.SealTB
Slog.Cells(7, 8) = "KN"
Slog.Cells(7, 9) = CDate(Me.DateTB)
Slog.Cells(7, 10) = Me.VRIDTB
Slog.Cells(7, 11) = Me.SortTB & "                        " & Me.TrailerCB
Slog.Cells(7, 12) = UCase(Me.InitalsTB)
Slog.Cells(7, 13) = Me.UsernameTB

Range("G7:L197").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Add(Range("Table5[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Add Key:=Range("Table5[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Add(Range("Table57[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Add Key:=Range("Table57[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Add(Range("Table578[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Add Key:=Range("Table578[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Add(Range("Table5789[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Add Key:=Range("Table5789[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Unload Me
bol.Select

End Sub
 
Last edited by a moderator:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Which sheet do you want to change depending on the checkbox?
 
Upvote 0
Is that as well as the other sheets, or instead of one of them?
 
Upvote 0
I'd like it to ignore all other sheets and paste only into Sheet6 (ADHOC) if check box is selected.
 
Upvote 0
How about
Code: 
Private Sub CommandButton1_Click()
Dim lg As Worksheet, bol As Worksheet, slog As Worksheet
If Me.CheckBox1 Then
   Set bol = Sheets("ADHOC")
Else
   Set lg = Sheets("Log")
   Set bol = Sheets("BOL")
   Set slog = Sheets("Seal Log_Make Changes Here")
End If
bol.Cells(4, 14) = Me.DoorCB
bol.Cells(5, 2) = CDate(Me.DateTB)
bol.Cells(11, 10) = Me.SortTB
bol.Cells(12, 10) = Me.TrailerCB
bol.Cells(13, 3) = Me.AddressTB
bol.Cells(14, 10) = Me.SealTB
bol.Cells(15, 10) = Me.CarrierCB
bol.Cells(13, 10) = Me.VRIDTB
bol.Cells(36, 1) = Me.QtyTB
bol.Cells(36, 5) = Me.WeightTB
bol.Cells(50, 7) = Me.CPTTB
bol.Cells(50, 2) = Me.ArrivalTB
bol.Cells(51, 2) = Me.DepartTB
If Not lg Is Nothing Then
   lg.Select
   lg.Range("A2:J2").Select
   Selection.ListObject.ListRows.Add (1)
       With Selection.Interior
           .Pattern = xlNone
           .TintAndShade = 0
           .PatternTintAndShade = 0
       End With
   
   lg.Cells(2, 1) = CDate(Me.DateTB)
   lg.Cells(2, 2) = Me.SortTB
   lg.Cells(2, 3) = Me.CarrierCB
   lg.Cells(2, 4) = Me.TrailerCB
   lg.Cells(2, 5) = Me.VRIDTB
   lg.Cells(2, 6) = Me.SealTB
   lg.Cells(2, 7) = Me.QtyTB
   lg.Cells(2, 8) = Me.WeightTB
   lg.Cells(2, 9) = Me.DoorCB
   lg.Cells(2, 10) = Me.CaseTB
   lg.Cells(2, 12) = Me.ArrivalTB
   lg.Cells(2, 13) = Me.DepartTB
   lg.Cells(2, 15) = UCase(Me.InitalsTB)
End If
If Not slog Is Nothing Then
   slog.Select
   slog.Range("G1:M1").Select
   Selection.Insert Shift:=xlDown
   slog.Cells(7, 7) = Me.SealTB
   slog.Cells(7, 8) = "KN"
   slog.Cells(7, 9) = CDate(Me.DateTB)
   slog.Cells(7, 10) = Me.VRIDTB
   slog.Cells(7, 11) = Me.SortTB & "                        " & Me.TrailerCB
   slog.Cells(7, 12) = UCase(Me.InitalsTB)
   slog.Cells(7, 13) = Me.UsernameTB
   
   Range("G7:L197").Select
   Selection.Copy
   Range("A7").Select
   ActiveSheet.Paste
End If
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Add(Range("Table5[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort. _
    SortFields.Add Key:=Range("Table5[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Add(Range("Table57[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort. _
    SortFields.Add Key:=Range("Table57[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table57").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Add(Range("Table578[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort. _
    SortFields.Add Key:=Range("Table578[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table578").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Clear
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Add(Range("Table5789[SEAL NUMBER]"), xlSortOnFontColor, xlAscending, , _
    xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort. _
    SortFields.Add Key:=Range("Table5789[SEAL NUMBER]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Seal Control Log").ListObjects("Table5789").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Unload Me
bol.Select

End Sub
 
Upvote 0
VERY NICE! So I learn from this, you plugged in the if statement at the top? It works great, I just have to define the cell location now. Thank you so much for your help!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,162
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