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

MRN227

New Member
Joined
Dec 18, 2018
Messages
10
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:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,029
Office Version
365
Platform
Windows
Which sheet do you want to change depending on the checkbox?
 

MRN227

New Member
Joined
Dec 18, 2018
Messages
10
Thank you for replying. I want to take all the info and move it to; Sheet6 (ADHOC)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,029
Office Version
365
Platform
Windows
Is that as well as the other sheets, or instead of one of them?
 

MRN227

New Member
Joined
Dec 18, 2018
Messages
10
I'd like it to ignore all other sheets and paste only into Sheet6 (ADHOC) if check box is selected.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,029
Office Version
365
Platform
Windows
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
 

MRN227

New Member
Joined
Dec 18, 2018
Messages
10
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,029
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,096,246
Messages
5,449,232
Members
405,559
Latest member
Chelseytx

This Week's Hot Topics

Top