Names of cells linked to Control Sources

[outclassed]

Hi, Sorry if this is a bad question or improperly worded but its driving me insane.

Im trying to make a userform that has several input boxes, combo boxes, and checkboxes that then stores these values into an excel spreadsheet.

I tried linking the items in the userform to the cells in the excel sheet by matching the names of the cells in the excel sheet to the control sources of the items in the userform.

But it doesnt seem to work 100 percent. Some of the fields work great. Others will just not seem to work. I cant seem to figure out what the default values should be. Should I set values in the excel sheet before opening the userform? Should I set the value field in the userform for each control? Will this override the value already in the excel sheet?

IE,
I have a column in the userform of checkboxes. The name of the cells in an excel column each match the control source of a different checkbox in the userform. I want the user to see the userform, check some of the checkboxes and then have there value stored in the excel colum as true or false. The problem is I cant seem to get it to work, sometimes it does sometimes it doesnt. If I set several of the excel cells to True or False when I open the userform in the vba editor the changes are reflected so I know the cells are linked. But when i do userform.show and then make changes they are not always reflected in the excel sheet.
Do i need to set default values to the cells in the userform by modifiying the value field of each checkbox?
Do i need to have default values in the cells in the excel sheet or leave them blank?
Sometimes I even get crashes.

Sorry if this sounds confusing but its driving me nuts because I know my cells and controls are linked they just are working only part of the time. Is there some function to resync the cell in the excel file to the value in the userform when it is closed?

EDIT
Ok I think the thing that was messing me up was setting the values field inside of the userform controls. Values already in the spreadsheet seem to work fine.

Thanks for your time,
Drew.

[mdmackillop]

Hi Drew
If you still need assistance, can you post your workbook. Use Manage Attachments in the Go Advanced section.
Regards
MD

[Jan Karel Pieterse]

What you should do is disconnect the checkboxes on the userform from the cells and control the passing of values in VBA completely. Say you have named your boxes Checkbox1...Checkbox4.
Assume you have two buttons: cmbOK and cmbCancel.

This code goes behind the form:

Option Explicit
Public OK As Boolean
Public Sub LoadData()
    Dim lCt As Long
    For lCt = 1 To 4
        Me.Controls("CheckBox" & lCt).Value = ThisWorkbook.Worksheets("Sheet1").Range("A" & lCt)
    Next
End Sub
Public Sub WriteData()
    Dim lCt As Long
    For lCt = 1 To 4
        ThisWorkbook.Worksheets("Sheet1").Range("A" & lCt) = Me.Controls("CheckBox" & lCt).Value
    Next
End Sub
Private Sub cmbCancel_Click()
    OK = False
    Me.Hide
End Sub
Private Sub cmbOK_Click()
    OK = True
    Me.Hide
End Sub

And this is how you use the form (code in a normal module):

Option Explicit
Sub FormDemo()
    Dim oForm As UserForm1
    Set oForm = New UserForm1
    oForm.LoadData
    oForm.Show
    If oForm.OK Then
        oForm.WriteData
    Else
        MsgBox "Cancelled"
    End If
    Unload oForm
    Set oForm = Nothing
End Sub