default drop box to a value?

[juan]

Hello,
I have a drop down box. Can I default it to a value? I
have a macro and I have added a drop box, but would like
it to default to a value. Right now, it leaves it blank.

Please advise any feedback.

Thanks,

Juan

 

[Robin Hammond]

Juan,

set the listindex property, or less reliably, set the value property of the
combobox. e.g. if the combo is on a form:

Private Sub UserForm_Initialize()
Dim lCount As Long
With ComboBox1
For lCount = 0 To 99
.AddItem lCount
Next lCount
.ListIndex = 0
'or not as reliable
'.Value = 99
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[Juan]

Hell Robin,
thanks for the info, but I'm not using in a form. I
created a macro to put the drop box on a sheet. Here's the
macro portion:

ActiveSheet.DropDowns.Add(111, 23.25, 75, 15.75).Select
With Selection
.Placement = xlFreeFloating
.PrintObject = True
End With
With Selection
.ListFillRange = "Sheet1!$P$2:$P$13"
.LinkedCell = "$D$1"
.DropDownLines = 8
.Display3DShading = False
End With

What I want is to default it to let say Cell P2 from my
list range.

Please advise.
Thank u for your time.

Juan

 

[Robin Hammond]

I've never actually done this before, but it looks like the value property

Sub Test()
ActiveSheet.DropDowns.Add(111, 23.25, 75, 15.75).Select
With Selection
.Placement = xlFreeFloating
.PrintObject = True
End With
With Selection
.ListFillRange = "Sheet1!$P$2:$P$13"
.LinkedCell = "$D$1"
.DropDownLines = 8
.Display3DShading = False
'*************add this
.Value = 1
'*******************
End With
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[Juan]

Hello Robyn,
it seems to work, but now my problme is that it stays the
drop box seems to stay active. Have Squares around it, so
my stops doesn't perform the rest. Is there a way around
this?

really appreciate all the help you are providing.

Thanks,

Juan

 

[Tom Ogilvy]

Sub Test()
With ActiveSheet.DropDowns.Add(111, 23.25, 75, 15.75)

.Placement = xlFreeFloating
.PrintObject = True
.ListFillRange = "Sheet1!$P$2:$P$13"
.LinkedCell = "$D$1"
.DropDownLines = 8
.Display3DShading = False
'*************add this
.Value = 1
'*******************
End With
End Sub