VBA UserForm ComboBox Problem

[fuzzywuzzy]

Hi!

I am relatively new to VBA Programming and am pulling the remaining fe
hair on my head... I have created a UserForm in VBA in which I include
a ComboBox. I want to type in data in that ComboBox and update th
list that is already on my worksheet (by automatically adding a row o
using a dynamic range, or another method which you may suggest...) an
return the selected (or newly entered) value in a cell, say a1.

I also would like the previous value selected with the ComboBox to b
shown when I re-load the UserForm.

Some advice would be very appreciated.

Thanks!
Fuzzywuzz

 

[Bob Phillips]

More than advice, the code

Private Sub ComboBox1_Change()
With Me.ComboBox1
Range(.RowSource)(Range(.RowSource).Count + 1, 1).Value = .Value
.RowSource = Range(.RowSource).Resize(Range(.RowSource).Count + 1,
1).Address
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Wrong event, try

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.ComboBox1
Range(.RowSource)(Range(.RowSource).Count + 1, 1).Value = .Value
.RowSource = Range(.RowSource).Resize(Range(.RowSource).Count + 1,
1).Address
Debug.Print .RowSource
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[fuzzywuzzy]

Hi Bob.

Thanks for your help. However, I've tried to use the code without an
success.

I've tried to enter the initial range of my list (a4:a24) in th
rowsource property, which didnt work.

I've also tried to define ComboBox1.RowSource
ActiveSheets.Range(a4:a24)

And finally, I've tried to call Private Sub ComboBox1_Exit() in m
Private Sub UserForm_Activate() with no success (I'm sure I'm doin
something totally wrong here but have no clue... )

Bob, when I get it to work, will it also return the selected or entere
value in a specific cell?

Thanks in advance!

Kim (Fuzzywuzzy

 

[Bob Phillips]

Hi Kim,

You say you are not having any success, but you don't describe what is/ what
is not happening.

I've tried to enter the initial range of my list (a4:a24) in the
rowsource property, which didnt work.

What exactly does this mean, this should be a fundamental easy bit. You
could try this code in your form as well to pre-load it.

Private Sub UserForm_Activate()
Me.ComboBox1.RowSource = "Sheet1!A4:A24"
End Sub

This of course assumes that your list is on Sheet1

And finally, I've tried to call Private Sub ComboBox1_Exit() in my
Private Sub UserForm_Activate() with no success (I'm sure I'm doing
something totally wrong here but have no clue... )

I am not sure what you are doing here, but don't <vbg>. You should not call
the click event from the activate event, it makes no logical sense, click
should be triggered by clicking the combobox. only call it in exceptional
circumstances, and there are none here.

I have already changed the event I am using once, and I am going to suggest
another change to that event. Remove my Combobox1_Exit event code, and add
this instead

Private Sub ComboBox1_AfterUpdate()
With Me.ComboBox1
Range(.RowSource)(Range(.RowSource).Count + 1, 1).Value = .Value
.RowSource = Range(.RowSource).Resize(Range(.RowSource).Count + 1,
1).Address
End With
End Sub

What might have been happening was that you were typing the value in and
staying in the combobox so the exit event didn't trigger. If you use this
event and enter a value and Enter key, you should see your list update

Bob, when I get it to work, will it also return the selected or entered
value in a specific cell?

Yes it should do, as I am adding the value typed in to the Rowsource list,
and then re-setting rowsource.

It does work as I understand your requirement, so keep plugging at it, and
let us know how you get on.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[fuzzywuzzy]

Bob!

Thanks you so much! It works almost perfectly. There are however
minor glitches.

1) When I choose an item from the list (as opposed to manually typin
a new one in the combo box), the item appears at a row just below th
end of the range (ie. a25), which makes a duplicate in the list an
seems to confuse my lookup tables a little... Would there be a way t
avoid this duplication?

2)Whenever I subsequently enter a new item (or choose an existing one
it always replaces the one at a25 (ie. does not get entered in a26
a27, etc). I'm sure there's an easy way to fix this, right?

Thanks again for your kind help.

Ki

 

[Bob Phillips]

Hi Kim,

Try these amended versions of the 2 procedures. Hopefully this will cure
those 2 problems

Private Sub ComboBox1_AfterUpdate()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("A4:A" &
Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
If rng.Find(ComboBox1.Value) Is Nothing Then
With Me.ComboBox1
Range(.RowSource)(Range(.RowSource).Count + 1, 1).Value = .Value
.RowSource = Range(.RowSource).Resize(Range(.RowSource).Count +
1, 1).Address
Debug.Print .RowSource
End With
End If
End Sub

Private Sub UserForm_Activate()
Me.ComboBox1.RowSource = "Sheet1!A4:A" &
Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Sub

Real users are good at finding the bugs<vbg>!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[fuzzywuzzy]

Bob,

Thanks again for your help. Now, when I enter

Me.ComboBox1.RowSource = "Sheet1!A4:A" &
Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

I get the annoying error: Run-Time error '380' Could not set the
rowsource property. Invalid Property Value.

I then gave the range a4:a24 a name "ClientRange" in the worksheet and
then typed in
Me.ComboBox1.RowSource = ClientRange

which only worked partially, that is, only recording a new entry at the
last spot on the list, as before (the duplication problem was fixed
though --- thanks!).

I then tried to make ClientRange as a dynamic range in the Define Name
window:

ClientRange = OFFSET(Sheet1!$A$4,1,MATCH("Client Name",
Sheet1!$1:$1,0)-1,COUNTA(Sheet1!$A:$A)-1,1)

which gave me a Run-Time error '380' when I activated the userform.

Thanks again for your help!!!

cheers

Kim

 

[fuzzywuzzy]

Bob.

I managed to work things by getting the dynamic range to work properly
Everything works beautifully. I would like to thank you for takin
time to anser my questions. Now, a new world of possibilities i
open....

Best regards,

Ki

 

[Bob Phillips]

Hi Kim,

Sorry I missed your post yesterday, but glad to see you have cracked it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)