Display initial value in combobox

[mworth01]

I've spent some time with the Search feature but couldn't find my answe
so here goes... I have a combobox that reads a list of names. When th
userform opens and the user sees this combobox, I would like to displa
text (such as "Select Name") in the combobox prior to the user clickin
the down arrow. Upon selecting a name, the name would appear in th
combobox instead of the original text. I realize a label woul
accomplish the same thing, but I would like to have it integral to th
combobox. I thought I figured it out when I turned ColumnHeads t
true, but that only displays the header row in the list upon clickin
on the down arrow. I was hoping this would be a property of th
combobox but so far I couldn't find it reading through all of th
options in the help file. Is this even possible to do? Thanks i
advance

 

[Bob Flanagan]

I'm certain someone will take on the challenge of your post, but the simple
approach would be check the combobox after the user closes the form to
confirm that there is an entry. (and you would need to anyway in case they
don't know how to read and follow directions)

do
userform1.show
If userform1.combobox1.value="" then
msgbox "You forgot to select something"
else
exit do
end if
loop

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Tom Ogilvy]

Private Sub Userform_Initialize()
Dim rng As Range
With Worksheets("Data")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
ComboBox1.List = rng.Value
ComboBox1.Value = "Select Name"
End Sub

will work as long as you don't configure the combobox to only allow
selection of values in the list.

 

[mworth01]

Thanks for the replies. Tom, your code is almost what I was lookin
for. It does work, but it makes one additional feature that I wante
more difficult to use. Since the database consists of thousands o
names, I want the user to be able to start typing the last name of th
person they are looking for and the combobox would then jump to tha
name. Obviously the more letters of the name you type the closer yo
get to that name in the list. If I use your code, then the user firs
has to delete "Select Name" before typing. So I guess what I reall
want is some initial text to be displayed in the combobox but to hav
it be automatically replaced once the user begins typing. It seem
that the original text would have to be automatically highlighted, s
that as the user hits the first key that letter replaces the origina
text. Again, I may be too greedy in what I want. If you have any mor
suggestions that would be great. Otherwise I'll decide on a compromis
and move forward. Thanks again

 

[Tom Ogilvy]

Possibly remove the text in the enter event. (or highlight it)

 

[nick]

I've spent some time with the Search feature but couldn't find my
answe so here goes... I have a combobox that reads a list of names.
When th userform opens and the user sees this combobox, I would like
to displa text (such as "Select Name") in the combobox prior to the
user clickin the down arrow. Upon selecting a name, the name would
appear in th combobox instead of the original text. I realize a label
woul accomplish the same thing, but I would like to have it integral
to th combobox. I thought I figured it out when I turned ColumnHeads
t true, but that only displays the header row in the list upon clickin
on the down arrow. I was hoping this would be a property of th
combobox but so far I couldn't find it reading through all of th
options in the help file. Is this even possible to do? Thanks i
advance

You can use the following in the userform code:

Private Sub UserForm_Activate()
ComboBox1.Text = "test message"
End Sub

Hope this helps

Nick