Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlSource Errors with 2-Column ComboBox
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlSource Errors with 2-Column ComboBox
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. -- Regards, Tom Ogilvy "Justin" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlSource Errors with 2-Column ComboBox
You got it! Thanks so much Tom!
Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Justin" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox Decimal Errors | Excel Programming | |||
MAX value of a column that has #div/0 errors in it | Excel Discussion (Misc queries) | |||
Controlsource errors | Excel Programming | |||
Setting ComboBox ControlSource in code | Excel Programming | |||
question about the ControlSource of a ComboBox | Excel Programming |