thomas.szwed
04-22-2008, 06:57 AM
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
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