Populate a combo box

[damorrison]

I am using the add item way to populate a combobox in a userform

Private Sub Userform_Initialize()
With Me.ComboBox1
.AddItem "A"
.AddItem "B"
.AddItem "C"
..AddItem ""

End With
End Sub

How does one add an Item when the user types in a name that is not in
the list

 

[Jim Cone]

Huh?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"damorrison"
<[email protected]>
wrote in message
I am using the add item way to populate a combobox in a userform

Private Sub Userform_Initialize()
With Me.ComboBox1
.AddItem "A"
.AddItem "B"
.AddItem "C"
..AddItem ""

End With
End Sub

How does one add an Item when the user types in a name that is not in
the list

 

[damorrison]

If the item is not on the list, the user is able to type in the entry
Is it possible for the new entry to be added to the list?

 

[Jim Cone]

I just invented this, so there is probably a better way.
Using Range C4:C10 as list of entries with the last entry blank,
the code goes in the form module...

Private Sub ComboBox1_DropButtonClick()
If IsError(Application.Match(ComboBox1.Value, ComboBox1.List, 0)) Then
ActiveSheet.Range("C10").Value = ComboBox1.Value
End If
End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = ActiveSheet.Range("C4:C10").Address
End Sub
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"damorrison" <[email protected]>
wrote in message
If the item is not on the list, the user is able to type in the entry
Is it possible for the new entry to be added to the list?

 

[Dave Peterson]

Can you try to add it when the user moves off the combobox?

Option Explicit
Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Resp As Long
With Me.ComboBox1
If .Value = "" Then
'do nothing
Else
If .ListIndex = -1 Then
Resp = MsgBox(Prompt:="do you want to add: " & .Value, _
Buttons:=vbYesNo)
If Resp = vbYes Then
.AddItem .Value
Else
'leave the combobox or stay in it?????
'Cancel = True
End If
End If
End If
End With
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
With Me.ComboBox1
.Style = fmStyleDropDownCombo
For iCtr = 1 To 4
.AddItem "A" & iCtr
Next iCtr
End With
End Sub

 

[damorrison]

Thanks dave I am now populating the combo box with a dynamic range, the
range is in a hidden sheet and I can't add items to the range when
this is the case,


Private Sub CoBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Resp As Long
With Me.CoBox
If .Value = "" Then
'do nothing
Else
If .ListIndex = -1 Then
Resp = MsgBox(Prompt:="This is a new Name, Do you want
to save it? " & .Value, _
Buttons:=vbYesNo)
If Resp = vbYes Then
Dim varNbRows As Double
Sheets("Menus").Select
Range("F2").Select
varNbRows = Selection.CurrentRegion.Rows.Count
If Selection.Value = "" Then
Exit Sub
ElseIf Selection.Offset(1, 0).Value = "" Then
Selection.Offset(1, 0).Select
Else
Selection.Offset(varNbRows, 0).Select
End If
Range("F1")(ActiveCell.Row).Value = CoBox

Else
'leave the combobox or stay in it?????
'Cancel = True
End If
End If
End If
End With
End Sub

 

[Dave Peterson]

You don't have to select a worksheet or range to work with it. In fact, working
with Selections makes the code much more difficult to understand (for me,
anyway).

Maybe this will get you closer:

Option Explicit
Dim BlkProc As Boolean
Private Sub CoBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Resp As Long
Dim DestCell As Range

If BlkProc = True Then Exit Sub
With Me.CoBox
If .Value = "" Then
'do nothing
Else
If .ListIndex = -1 Then
Resp = MsgBox(Prompt:="This is a new Name, " _
& "Do you want to save it? " & .Value, _
Buttons:=vbYesNo)
If Resp = vbYes Then
With Worksheets("Menus")
Set DestCell = .Cells(.Rows.Count, "F") _
.End(xlUp).Offset(1, 0)
End With
DestCell.Value = .Value
Call LoadCoBox
End If
End If
End If
End With
End Sub

Private Sub Commandbutton1_Click()
BlkProc = True
Unload Me
End Sub

Private Sub UserForm_Initialize()
BlkProc = False
Call LoadCoBox
With Me.CoBox
.Style = fmStyleDropDownCombo
End With
'for the cancel button
Me.Commandbutton1.TakeFocusOnClick = False

End Sub
Sub LoadCoBox()
Me.CoBox.List = Worksheets("menus").Range("Mylist").Value
End Sub

 

[damorrison]

Hi Dave


I get permission denied and then highlites this line of code

Me.CoBox.List = Worksheets("Menus").Range("Company").Value

 

[Dave Peterson]

I populated the combobox in code. You populated it via the properties window.

One way around it is to use code and drop the properties window assignment:

Sub LoadCoBox()
Me.CoBox.RowSource = ""
Me.CoBox.List = Worksheets("menus").Range("Mylist").Value
End Sub

 

[damorrison]

Incredible......

 

[Dave Peterson]

I hope that means good!

Incredible......

 

[damorrison]

Yes,
you were correct,
As I was pasting your code, I told myself that I was going to have to
delete that row source from the properties window, and I forgot to do
that

Thanks Again