Hello all
I would like to amend my userform, at present I have a control Add Switcher which takes the data entered by the form user and adds to my spreadsheet this is good and the code I have works on the first row below, however I decided it would easier for users rather than complete one line and the hit Add Switcher they could complete multiple lines at a time. the problem is I do not know how to amend my code. Can anyone suggest how I can amend my code so all lines update tothe sheet at once or suggest a better way of doing this.
I need lines for multiple switchers to be added
Thanks
The code I have at present
I would like to amend my userform, at present I have a control Add Switcher which takes the data entered by the form user and adds to my spreadsheet this is good and the code I have works on the first row below, however I decided it would easier for users rather than complete one line and the hit Add Switcher they could complete multiple lines at a time. the problem is I do not know how to amend my code. Can anyone suggest how I can amend my code so all lines update tothe sheet at once or suggest a better way of doing this.
I need lines for multiple switchers to be added
Thanks
The code I have at present
Code:
Private Sub cmdAddSwitcher_Click()
'''' Add Switcher Command - clear and add new row to Nearest switcher
Dim ctl As Control
RowCount = Worksheets("Nearest Switchers").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Nearest Switchers").Range("A1")
.Offset(RowCount, 0).Value = Me.txtSIMSJN
.Offset(RowCount, 1).Value = Me.txtName1.Value
If Me.chkMobTried1.Value = True Then
.Offset(RowCount, 2).Value = "Yes"
Else
.Offset(RowCount, 2).Value = "No"
End If
If Me.chkHomeTried1.Value = True Then
.Offset(RowCount, 3).Value = "Yes"
Else
.Offset(RowCount, 3).Value = "No"
End If
If Me.chkAttending1.Value = True Then
.Offset(RowCount, 4).Value = "Yes"
Else
.Offset(RowCount, 4).Value = "No"
End If
.Offset(RowCount, 5).Value = Me.cboRole1.Value
.Offset(RowCount, 6).Value = Me.txtETA1.Value
If Me.chkStdby1.Value = True Then
.Offset(RowCount, 7).Value = "Yes"
Else
.Offset(RowCount, 7).Value = "No"
End If
End With
For Each ctl In Me.Controls
Me.txtName1 = Null
Me.chkMobTried1 = False
Me.chkHomeTried1 = False
Me.chkAttending1 = False
Me.cboRole1 = Null
Me.txtETA1 = Null
Me.chkStdby1 = False
Next ctl
End Sub