thomas.szwed
04-23-2008, 02:50 AM
Hi,
The code below updates details on a recrord in my spreadsheet from the information the user has entered on my userform.
Private Sub btnEdit_Click()
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.ddlSurname.Value ' write amendments to database
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 = "C" = If Me.CheckBox1.Value End If
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.ddlSurname.Value = vbNullString
.ddlForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.ddlStartDate.Value = vbNullString
.ddlEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.ddlLineManager.Value = vbNullString
.ddlVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
.CheckBox1.Value = vbNullString
End With
Application.ScreenUpdating = True
End Sub
I have added a checkbox to my userform. When it is ticked, it enters a value of C in the corresponding cell. I cant work out how to code an update function for this. i.e. the user unticks the checkbox therefore the C must be removed from the spreadsheet and vica versa.
I tried as you can see in bold but it doesnt work!!!!
Any help on how to do this is much appreciated.
THanks
The code below updates details on a recrord in my spreadsheet from the information the user has entered on my userform.
Private Sub btnEdit_Click()
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.ddlSurname.Value ' write amendments to database
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 = "C" = If Me.CheckBox1.Value End If
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.ddlSurname.Value = vbNullString
.ddlForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.ddlStartDate.Value = vbNullString
.ddlEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.ddlLineManager.Value = vbNullString
.ddlVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
.CheckBox1.Value = vbNullString
End With
Application.ScreenUpdating = True
End Sub
I have added a checkbox to my userform. When it is ticked, it enters a value of C in the corresponding cell. I cant work out how to code an update function for this. i.e. the user unticks the checkbox therefore the C must be removed from the spreadsheet and vica versa.
I tried as you can see in bold but it doesnt work!!!!
Any help on how to do this is much appreciated.
THanks