Populate Worksheet Rows Based on Userform Checkbox selection

T

TellM1955

New Member
Joined
Apr 8, 2021
Messages
44
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I'm looking to populate a worksheet depending on the checkboxes selected on the userform. In my search for a solution, I've found the following code which I've adapted . However, it doesn't provide the solution what I'm looking for. If checkbox AM is selected, then it writes the values required. However, if I select 2 or more checkboxes then the ID on all of the rows are the same. If the selection is either checkbox PM or Sat then a blank row is put in. Any suggestion or help would be appreciated. Thanks

VBA Code: 
Private Sub cmdRouteDetailAdd()
    ' Declare variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim IDVal As String, RouteVal As String, LocateVal As String
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("RouteData") ' Ensure the sheet "RouteData" exists
    
    IDVal = TextBoxID.Text
    RouteVal = TextBoxRoute.Text
    LocateVal = TxtPickDropLocation
    
    ' Find the last used row in the sheet and calculate the next empty row
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row + 1
    
  
    If CheckBoxAM.Value = True Then
        
    ' Add the entries
    ' First Entry: AM
       
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "AM" ' Column B: "AM"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
    
    End If
    
    If CheckBoxPM.Value = True Then
    ' Second Entry: PM
        lastRow = lastRow
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "PM" ' Column B: "PM"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
   End If
   
   
   If CheckBoxSat.Value = True Then
   
    ' Third Entry: PM
        lastRow = lastRow + 1
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "Sat" ' Column B: "Sat"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
    End If
    
    ' Clear the text boxes for new input
    TextBoxRoute.Text = ""
    CheckBoxAM = False
    CheckBoxPM = False
    CheckBoxSat = False
    TxtPickDropLocation.Text = ""
    TextBoxRoute.SetFocus ' Set focus back to the first TextBox
    
    Call ReSequenceRouteOrder
    Unload Me
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
TellM1955 said:
I'm looking to populate a worksheet depending on the checkboxes selected on the userform. In my search for a solution, I've found the following code which I've adapted . However, it doesn't provide the solution what I'm looking for. If checkbox AM is selected, then it writes the values required. However, if I select 2 or more checkboxes then the ID on all of the rows are the same. If the selection is either checkbox PM or Sat then a blank row is put in. Any suggestion or help would be appreciated. Thanks

VBA Code: 
Private Sub cmdRouteDetailAdd()
    ' Declare variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim IDVal As String, RouteVal As String, LocateVal As String
  
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("RouteData") ' Ensure the sheet "RouteData" exists
  
    IDVal = TextBoxID.Text
    RouteVal = TextBoxRoute.Text
    LocateVal = TxtPickDropLocation
  
    ' Find the last used row in the sheet and calculate the next empty row
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row + 1
  
 
    If CheckBoxAM.Value = True Then
      
    ' Add the entries
    ' First Entry: AM
     
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "AM" ' Column B: "AM"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
  
    End If
  
    If CheckBoxPM.Value = True Then
    ' Second Entry: PM
        lastRow = lastRow
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "PM" ' Column B: "PM"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
   End If
 
 
   If CheckBoxSat.Value = True Then
 
    ' Third Entry: PM
        lastRow = lastRow + 1
        ws.Cells(lastRow, "A").Value = IDVal
        ws.Cells(lastRow, "B").Value = RouteVal ' Column B: Route
        ws.Cells(lastRow, "C").Value = "Sat" ' Column B: "Sat"
        ws.Cells(lastRow, "D").Value = LocateVal ' Column D: Location
        lastRow = lastRow + 1
    End If
  
    ' Clear the text boxes for new input
    TextBoxRoute.Text = ""
    CheckBoxAM = False
    CheckBoxPM = False
    CheckBoxSat = False
    TxtPickDropLocation.Text = ""
    TextBoxRoute.SetFocus ' Set focus back to the first TextBox
  
    Call ReSequenceRouteOrder
    Unload Me
End Sub
Click to expand...
Always be careful when you take existing code.

What are you trying to achieve?

Do you want the user to only select one of the check boxes at a time?
Maybe option buttons in a frame may be more appropriate.

Data will be written to a new row after this line is executed.
lastRow = lastRow + 1

If you don't want a blank row then only set the next row number before data is written to the worksheet.
 
Upvote 0
Firstly High, thanks for getting back on this.
The data I am trying to gather is for routes and their times. So you can have a route that runs "AM", "PM, OR "Sat", which is why the are Checkboxes and not Option Buttons.
The reason for writing all three is simple because the individual who is travelling can be on the same route at each of these times. However, it is equally possible that the individual could be on a completely different route for any one or more of these times.
Regards
 
Upvote 0
TellM1955 said:
Firstly High, thanks for getting back on this.
The data I am trying to gather is for routes and their times. So you can have a route that runs "AM", "PM, OR "Sat", which is why the are Checkboxes and not Option Buttons.
The reason for writing all three is simple because the individual who is travelling can be on the same route at each of these times. However, it is equally possible that the individual could be on a completely different route for any one or more of these times.
Regards
Click to expand...
So do you expect a new row for each of the check boxes checked?
 
Upvote 0
TellM1955 said:
High, that is correct. I need a new row for each of the times. Thank you.
Click to expand...

Try this.

I've tidied it up a bit but mostly sorted out the row reference.

VBA Code: 
Private Sub cmdRouteDetailAdd_Click()
' Declare variables
Dim Ws As Worksheet
Dim lngNextRow As Long
Dim IDVal As String, RouteVal As String, LocateVal As String

  ActiveWorkbook.Save
    
  ' Ensure the sheet "RouteData" exists.
  If Not (ActiveWorkbook.Sheets("RouteData").Index > 0) Then
    Exit Sub
  End If
    
  ' Set the worksheet.
  Set Ws = ActiveWorkbook.Sheets("RouteData")
    
  IDVal = TextBoxID.Text
  RouteVal = TextBoxRoute.Text
  LocateVal = TxtPickDropLocation
      
  If CheckBoxAM.Value = True Then
    ' Add the entries
    ' First Entry: AM
    Ws.Cells(Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row + 1, "A").Resize(1, 4). _
      Value = Array(IDVal, RouteVal, "AM", LocateVal)
  End If
    
  If CheckBoxPM.Value = True Then
    ' Second Entry: PM
    Ws.Cells(Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row + 1, "A").Resize(1, 4). _
      Value = Array(IDVal, RouteVal, "PM", LocateVal)
  End If
   
  If CheckBoxSat.Value = True Then
    ' Third Entry: PM
    Ws.Cells(Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row + 1, "A").Resize(1, 4). _
      Value = Array(IDVal, RouteVal, "Sat", LocateVal)
  End If
    
  ' Clear the text boxes for new input.
  TextBoxID.Text = ""
  TextBoxRoute.Text = ""
  CheckBoxAM = False
  CheckBoxPM = False
  CheckBoxSat = False
  TxtPickDropLocation.Text = ""
  TextBoxRoute.SetFocus ' Set focus back to the first TextBox
    
  Call ReSequenceRouteOrder
  
  Unload Me
  
End Sub
 
Upvote 0
TellM1955 said:
I need a new row for each of the times.
Click to expand...

Untested but another way perhaps

VBA Code: 
Private Sub cmdRouteDetailAdd_Click()
    ' Declare variables
    Dim ws          As Worksheet
    Dim LastRow     As Long
    Dim ChkBox      As Variant
    Dim IDVal       As String, RouteVal As String, LocateVal As String
    
    On Error GoTo myerror
    ' Set variable to worksheet object
    Set ws = ThisWorkbook.Worksheets("RouteData")
    ' Find the last used row in the sheet and calculate the next empty row
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    IDVal = TextBoxID.Text
    RouteVal = TextBoxROUTE.Text
    LocateVal = TxtPickDropLocation
    
    'loop each checkbox & post selected values to next row in range
    For Each ChkBox In Array(CheckBoxAM, CheckBoxPM, CheckBoxSAT)
        If ChkBox.Value = True Then
            ws.Cells(LastRow, "A").Resize(, 4).Value = Array(IDVal, RouteVal, Mid(ChkBox.Name, 9), LocateVal)
            LastRow = LastRow + 1
        End If
    Next ChkBox
    
    Call ReSequenceRouteOrder
    Unload Me
    
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Note: As you are unloading the userform, I omitted all the reset code at the end as controls will return to their default state but you can, if prefer, include if wanted.

Hope Helpful

Dave
 
Upvote 0
Dave, thank you for all your efforts. It is nearly perfect for what I need. Is it possible that when you write the IDVAL it is unique for each row?
Regards
Terry
 
Upvote 0
High, my apologies as I meant to write yo both of you on my previous reply. Both solution work well except I would like that the IDVAL is unique on each row.
Regards
Terry
 
Upvote 0
TellM1955 said:
Both solution work well except I would like that the IDVAL is unique on each row.
Click to expand...

Hi,
Without an example of your IDVAL can only make a guess but assuming that the value user enters is just numeric and you want each line to increment + 1 from the starting value entered?

Then see if updates shown in red help

Rich (BB code): 
Private Sub cmdRouteDetailAdd_Click()
    ' Declare variables
    Dim ws          As Worksheet
    Dim LastRow     As Long
    Dim ChkBox      As Variant
    Dim IDVal       As String, RouteVal As String, LocateVal As String
    
    On Error GoTo myerror
    ' Set variable to worksheet object
    Set ws = ThisWorkbook.Worksheets("RouteData")
    ' Find the last used row in the sheet and calculate the next empty row
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    IDVal = Val(TextBoxID.Text)
    RouteVal = TextBoxROUTE.Text
    LocateVal = TxtPickDropLocation
    
    'loop each checkbox & post selected values to next row in range
    For Each ChkBox In Array(CheckBoxAM, CheckBoxPM, CheckBoxSAT)
        If ChkBox.Value = True Then
            ws.Cells(LastRow, "A").Resize(, 4).Value = Array(IDVal, RouteVal, Mid(ChkBox.Name, 9), LocateVal)
            LastRow = LastRow + 1
            IDVal = IDVal + 1
        End If
    Next ChkBox
    
    Call ReSequenceRouteOrder
    Unload Me
    
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

If this is not what you are looking for then please provide a copy of your worksheet using MrExcel Addin XL2BB with dummy data

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help Needed
2
Replies
13
Views
324
kwavic
K
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
98
RoryA
RoryA
D
  • Question Question
Using Unique function in excel
Replies
2
Views
64
ThatOneDude
T
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
838
Joe4
Joe4
D
  • Solved
Remove Absolute References
Replies
3
Views
168
HighAndWilder
H

Forum statistics

Threads
1,224,272
Messages
6,177,631
Members
452,786
Latest member
k3calloway

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