ControlSource Errors with 2-Column ComboBox

[Justin]

I have been having the same issue for a while now, and it is now time
for me to conquer this problem, so I made a new simple spreadsheet with
ONLY the combobox and the appropriate code to try to single out the
problem.

I have a list of numbers in cells A1:A12, and the cell that stores the
ComboBox value is C1.

I have a UserForm with 1 combobox on it. The combobox has 2 columns,
the first column shows formatted numbers, the 2nd column is invisible
and contains the numbers without any formatting. The 2nd column is the
bound column.

The code below is all the code I have in my entire worksheet:


Private Sub ComboBox1_Change()

Worksheets("Sheet1").Cells(1, 3).Value = UserForm1.ComboBox1.Value

End Sub


Private Sub UserForm_Initialize()

If UserForm1.ComboBox1.ListCount > 0 Then
Else

With UserForm1.ComboBox1
.ControlSource = "C1"
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = "-1;0"

For j = 1 To 12
.AddItem Format((Worksheets("Sheet1").Cells(j, 1).Value),
"#0.000")
.List(.ListCount - 1, 1) = Worksheets("Sheet1").Cells(j,
1).Value
Next j
End With
End If

End Sub


THE ISSUE: There is a flaw with the design of my code where when you
open the UserForm, it clears the "ControlSource" value in the
spreadsheet and shows a blank value until I select a new value. I need
it to just show the value that was in "C1" and not erase it...

Does anybody see any design flaws in my code that could be attributing
to this problem?

Thanks in advance!!!
Justin

PS -- The ComboBox has NO properties set other than what comes default
in Excel 2000.

 

[Tom Ogilvy]

Private Sub UserForm_Initialize()
Dim v

v = Worksheets("sheet1").Range("C1").Value
With UserForm1.ComboBox1
.ControlSource = "Sheet1!C1"
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = "-1;0"

For j = 1 To 12
.AddItem Format((Worksheets("Sheet1").Cells(j, 1).Value),
"#0.000")
.List(.ListCount - 1, 1) = Worksheets("Sheet1").Cells(j,
1).Value
Next j
End With
Userform.Combobox1.Value = v


End Sub

get rid of the change event. You already use the controlsource property to
update the cell.

 

[Justin]

You got it! Thanks so much Tom!

Private Sub UserForm_Initialize()
Dim v

v = Worksheets("sheet1").Range("C1").Value
With UserForm1.ComboBox1
.ControlSource = "Sheet1!C1"
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = "-1;0"

For j = 1 To 12
.AddItem Format((Worksheets("Sheet1").Cells(j, 1).Value),
"#0.000")
.List(.ListCount - 1, 1) = Worksheets("Sheet1").Cells(j,
1).Value
Next j
End With
Userform.Combobox1.Value = v


End Sub

get rid of the change event. You already use the controlsource property to
update the cell.