User Form adding adding rows starting from below table and checkbox not clearing

lydia.davison · Apr 3rd 2020

Hi,

I'm having 2 issues with this code.

1) When the data is loading into the worksheet, its not starting in the table - its beginning from the row below the table. I want the data to be loaded into the first row of the table every time a new row is added.

2) The checkbox will not clear so when opening the "Add Tenant" form via a command button - the checkbox is already clicked.

Any help on this would be appreciated.

Code

Private Sub AddTenant_Click()

' Copy input values to sheet.

Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Tenant List")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 1).Value = Me.Tenant.Value
.Cells(lRow, 2).Value = Me.DOB.Value
.Cells(lRow, 3).Value = Me.Address.Value
.Cells(lRow, 4).Value = Me.House.Value
.Cells(lRow, 5).Value = Me.Flat.Value
.Cells(lRow, 6).Value = Me.InDate.Value
.Cells(lRow, 7).Value = Me.OutDate.Value
.Cells(lRow, 8).Value = Me.PayDay.Value
.Cells(lRow, 10).Value = Me.Deposit.Value
If CheckBox1.Value Then
.Cells(lRow, 9).Value = "Yes"
Else: .Cells(lRow, 9).Value = "No"

End If


End With

'Clear input controls.
Me.Tenant.Value = ""
Me.DOB.Value = ""
Me.Address.Value = ""
Me.House.Value = ""
Me.Flat.Value = ""
Me.InDate.Value = ""
Me.OutDate.Value = ""
Me.PayDay.Value = ""
Me.CheckBox1.Value = False
Me.Deposit.Value = ""


End Sub

Display More

**royUK** · Apr 3rd 2020

Welcome to Ozgrid Forum. Please take a few minutes to read our Forum Rules, then you will see why I have added Code Tags for you.

When you refer to a Table is this a proper range formatted as a Table. Attach a small workbook example and it will help get an answer.

This minor amendment will add data to the next empty row

Code

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1

**Jack in the UK** · Apr 4th 2020

You need to untick the checkbox at the end of its use line 19 is the check and code is to line 26

So many add a forced change statement to change the checkbox value = FALSE

Code

UserForm1.CheckBox1.Value = False

This code you can play and test before adding to your work, test on a separate spreadsheet first

Code

If UserForm1.CheckBox1.Value = True Then
       MsgBox "Checked", , "Jack in the UK"
    Else
       MsgBox " UnChecked", , "Jack in the UK"
    End If

UserForm1.CheckBox1.Value = False

jiuk

**royUK** · Apr 4th 2020

Place this code in a standard module. It can be called by any UserForm in the WorkBook - see the userform code below

Code

Public Function ClearAll(frm As MSForms.UserForm) As Boolean
''///clear out all controls
Dim oCtrl As Control
''/// if any error occurs, just exit
On Error GoTo ExitFunc


''/// loop through controls, figure out type and
''/// use appropriate method to clear it
For Each oCtrl In frm.Controls
If oCtrl.Enabled = True Then
Select Case TypeName(oCtrl)
Case "TextBox": oCtrl = Empty
Case "CheckBox", "OptionButton": oCtrl.Value = False
Case "ComboBox", "ListBox": oCtrl.ListIndex = -1
Case Else:
End Select
End If
Next oCtrl


ExitFunc:
Set oCtrl = Nothing
End Function

Display More

Amend the code in the UserForm to this

Code

Private Sub AddTenant_Click()
' Copy input values to sheet.
Dim lRow As Long
Dim ws As Worksheet


Set ws = Worksheets("Tenant List")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + 1


With ws
.Cells(lRow, 1).Value = Me.Tenant.Value
.Cells(lRow, 2).Value = Me.DOB.Value
.Cells(lRow, 3).Value = Me.Address.Value
.Cells(lRow, 4).Value = Me.House.Value
.Cells(lRow, 5).Value = Me.Flat.Value
.Cells(lRow, 6).Value = Me.InDate.Value
.Cells(lRow, 7).Value = Me.OutDate.Value
.Cells(lRow, 8).Value = Me.PayDay.Value
.Cells(lRow, 10).Value = Me.Deposit.Value
If CheckBox1.Value Then
.Cells(lRow, 9).Value = "Yes"
Else: .Cells(lRow, 9).Value = "No"
End If
End With
''///Clear input controls.
ClearAll Me
End Sub

Display More

lydia.davison · Apr 14th 2020

Hi Roy,

Thank you for your reply. I have attached the file here for you to have a look at.

**royUK** · Apr 14th 2020

Did you try the code that I posted?

User Form adding adding rows starting from below table and checkbox not clearing

Participate now!

Share

Tags