Solved: Checkbox correspondence

[thomas.szwed]

Hello,

I have a user form that captures data and records to a spreadsheet. I have just added checkboxes on. Bascially i want the user to be able to tick a checkbox and then a column on the spreadsheet would fill with a letter. At the moment it displays "TRUE". Is there anyway to change this. I also use the form to display records so i need to work the opposite way aswell i.e. the value on the spreadsheet ticks the box on the userform when viewing records.



Private Sub btnAddRecord_Click()
'next empty cell in column A

Set c = Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False 'speed up, hide task

If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personnel Number is already assigned - try another", vbCritical, "Duplicate found"
Else
'update it
'write userform entries to database
c.Value = Me.ddlSurname.Value
c.Offset(0, 1).Value = Me.ddlForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.ddlStartDate.Value
c.Offset(0, 5).Value = Me.ddlEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.ddlLineManager.Value
c.Offset(0, 9).Value = Me.ddlVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
c.Offset(0, 11).Value = Me.CheckBox1.Value

Call ClearForm

End If

Application.ScreenUpdating = True
End Sub


Any help is great. Thanks

[Bob Phillips]

Surely, you check the checkbox value and load if



If Me.CheckBox1.Value Then
c.Offset(0, 11).Value = "X"
End If

[thomas.szwed]

How does this work the other way then. For instance the code below, reads whats on the spreadsheet and displays it on the user form. How would i adapt this for my checkbox and value X?

Private Sub UpdateDetails()
ddlSurname.Text = Cells(lCurrentRow, 1).Value
ddlForename.Text = Cells(lCurrentRow, 2).Value
ddlAssignee.Text = Cells(lCurrentRow, 3).Value
txtPersNum.Text = Cells(lCurrentRow, 4).Value
ddlStartDate.Text = Cells(lCurrentRow, 5).Value
ddlEndDate.Text = Cells(lCurrentRow, 6).Value
ddlDivision.Text = Cells(lCurrentRow, 7).Value
ddlLocation.Text = Cells(lCurrentRow, 8).Value
ddlLineManager.Text = Cells(lCurrentRow, 9).Value
ddlVCS.Text = Cells(lCurrentRow, 10).Value
ddlHealth.Text = Cells(lCurrentRow, 11).Value

Call SetupButtons(False, False, True, False)
End Sub

THanks!!!!

[Bob Phillips]

Off the top



Me.CheckBox1.Value = c.Offset(0, 11).Value = "X"

[thomas.szwed]

Gotchya Thnks

[thomas.szwed]

Actually this does'nt work XLD. It just brings back a tick even when the cell doesn't have a "C" in it?

[david000]

If you put a checkbox on a worksheet you'll see how the event changes the ticks on the checkbox. For a User form to do the same you'll need to tie the control you are using to look at the sheet to the checkbox.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("a1")
If Target.Value = "x" Then
Me.CheckBox1 = True 'ticked
Else
Me.CheckBox1 = False 'unticked
End If
End Sub

[Bob Phillips]

Works fine for me, sets and unsets according to the value of that cell, but it is looking for X not C.