ComboBox start condition
 

I have a ComboBox on a UserForm giving a single column
choice of 5 user options generated from an Array. All
works fine. I would like the ComboBox to display the last
user selected value (which is stored in a named cell on
the worksheet,) when the program is started. I have written
ComboBox1.Value = Range("Somename").Value in the
"Private Sub UserForm_Initialize()" module but although
the program reads this, it has no effect.
Any help would be much appreciated.

ComboBox start condition
 

that should work, but if it doesn't

Private Sub Userform_Initialize()
Dim i as Long
for i = 0 to Combobox1.ListCount - 1
if instr(1,combobox1.List(i),Trim(Range("Somename"),v bTextCompare) then
combobox1.ListIndex = i
exit for
end if
Next
End Sub

Make sure your Initialize event is named as above regardless of the name of
the userform. Make sure it is in the userform module.

This also assumes that the List has already been initialized.

--
Regards,
Tom Ogilvy


"Don" wrote in message
...
I have a ComboBox on a UserForm giving a single column
choice of 5 user options generated from an Array. All
works fine. I would like the ComboBox to display the last
user selected value (which is stored in a named cell on
the worksheet,) when the program is started. I have written
ComboBox1.Value = Range("Somename").Value in the
"Private Sub UserForm_Initialize()" module but although
the program reads this, it has no effect.
Any help would be much appreciated.

ComboBox start condition
 

Very many thanks Tom. Your solution works fine. I didn't
realise that you have to pull what you want out of the
ComboBox's list. Thought you could just put it straight in
from my Range("Somename"). Anyway, thanks again
Regards Don


-----Original Message-----
that should work, but if it doesn't

Private Sub Userform_Initialize()
Dim i as Long
for i = 0 to Combobox1.ListCount - 1
if instr(1,combobox1.List(i),Trim(Range
("Somename"),vbTextCompare) then
combobox1.ListIndex = i
exit for
end if
Next
End Sub

Make sure your Initialize event is named as above
regardless of the name of
the userform. Make sure it is in the userform module.

This also assumes that the List has already been
initialized.

--
Regards,
Tom Ogilvy


"Don" wrote in
message
...
I have a ComboBox on a UserForm giving a single column
choice of 5 user options generated from an Array. All
works fine. I would like the ComboBox to display the
last
user selected value (which is stored in a named cell on
the worksheet,) when the program is started. I have
written
ComboBox1.Value = Range("Somename").Value in the
"Private Sub UserForm_Initialize()" module but although
the program reads this, it has no effect.
Any help would be much appreciated.


.