PDA

View Full Version : How to update a list to fill combobox



ioncila
11-19-2009, 04:21 AM
Hi
Maybe this question has been answered in this forum but, behind the search I made, I didnt get a solution to this issue:

I have a combobox that is filled with data from "Sheet1!A2:A20".
This data list may grow in two ways:
- If I add new itens manually to data list or
- If the user enters a new item directly in combobox.

For the first option I tried this code (without luck - my knowledge ends here)

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim LastRow As Long

Set ws = Worksheets("Lists")
Set LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.ListFillRange = "ws!A2:A & LastRow"

End Sub

Is there an easy and fast way to do those two options?
Are they compatible in the same code?

Thank you for your help in advance.

Jan Karel Pieterse
11-19-2009, 04:29 AM
The first problem can be solved by juggling with the quotes a bit:

ComboBox1.ListFillRange = "ws!A2:A" & LastRow

ioncila
11-19-2009, 04:43 AM
The first problem can be solved by juggling with the quotes a bit:

ComboBox1.ListFillRange = "ws!A2:A" & LastRow

Thank you for the reply.
Still, nothing happens. In other words, combobox doesnt show anything

mdmackillop
11-19-2009, 05:45 AM
This uses a dynamic range name MyData
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
The code should insert the new item in (Excel) alphabetical order


Option Explicit
Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = "MyData"
End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Rw As Long, c As Range
If KeyCode = 13 Then
Set c = Range("MyData").Find(ComboBox1, lookat:=xlWhole)
If c Is Nothing Then
Rw = Application.Match(ComboBox1, Range("MyData")) + 2
With Cells(Rw, 1)
.Insert Shift:=xlDown
.Offset(-1).Formula = ComboBox1
End With
End If
End If
End Sub

p45cal
11-19-2009, 06:01 AM
The first problem can be solved by juggling with the quotes a bit:

ComboBox1.ListFillRange = "ws!A2:A" & LastRow Shouldn't that be:
ComboBox1.ListFillRange = "Lists!A2:A" & LastRow

or in case the Lists sheet name changes and so you only have to change one part of the code:
ComboBox1.ListFillRange = ws.name & "!A2:A" & LastRow

ioncila
11-19-2009, 08:46 AM
Thank very much for the replies. but as you can see in the attached, it doesnt work.

The attached file is similar to the one I'm trying to create.

Those lists in Lists Sheet must fill the comboboxes in userform (something that I can do using RowSource in Combo Properties). But lists may grow by introducing new itens directly in the list or entering a new item in any combobox and then auto update each own list.

I hope this is possible.

Thanks

Jan Karel Pieterse
11-19-2009, 08:51 AM
Shouldn't that be:
ComboBox1.ListFillRange = "Lists!A2:A" & LastRow

or in case the Lists sheet name changes and so you only have to change one part of the code:
ComboBox1.ListFillRange = ws.name & "!A2:A" & LastRow
Of course. I didn't read all of the message obviously!

mdmackillop
11-19-2009, 09:21 AM
I've created the dynamic range "rLOC", and set this as the Combobox1 RowSource in the Combobox properties. You'll need to do similarly for the other combos.

mdmackillop
11-19-2009, 09:49 AM
A better code for the userform

Option Explicit

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
SearchAdd Me.ComboBox1, Range(Me.ComboBox1.RowSource)
End If
End Sub

Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
SearchAdd Me.ComboBox2, Range(Me.ComboBox2.RowSource)
End If
End Sub

Private Sub SearchAdd(Data As String, Rng As Range)
Dim C As Range
Set C = Rng.Find(Data, lookat:=xlWhole)
If C Is Nothing Then
Rng.End(xlDown)(2) = Data
Set Rng = Rng.Resize(Rng.Cells.Count + 1)
Rng.Sort Key1:=Rng(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

ioncila
11-19-2009, 10:14 AM
Thank you for your help

However there's something I dont understand:

If I try to match your code to other combos or try it in my master work and when i write "r..." in RowSource entry, it returns an error message:
"Not possible to define RowSource Property. Invalid Property Value" (My Translation)

What am I doing wrong?

PS: I wrote this before trying post 9. I will try it now

ioncila
11-19-2009, 10:29 AM
Sorry, cant understand where do i dentify the data for each combobox.
Does this code complete the one in post 8?

ioncila
11-20-2009, 03:21 AM
I've created the dynamic range "rLOC", and set this as the Combobox1 RowSource in the Combobox properties. You'll need to do similarly for the other combos.

Hi

I've tried to adapt code to othe other combos but code is not accepted.
Dont hnow why but the code you suggested only works in the attached file and only to combobox1. If I change parameters to combobox2, for instance, RowSource property returns "Invalid Property Value" message.

I'm sure the problem is me and my unsuficient knowledge, but it's too desperate not find the logical solution that I'm sure must be under my eyes.

mdmackillop
11-20-2009, 03:49 AM
1. You need to create dynamic range names for each set of data. See Insert/Name/Define for the 2 I have created
2. You need to insert this name in the RowSource property of the relevant combobox.
3. You need to add this code, suitably modified for each combobox

Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
SearchAdd Me.ComboBox2, Range(Me.ComboBox2.RowSource)
End If
End Sub


This sample contains 2 working combos.

ioncila
11-20-2009, 05:37 AM
Thank you very much

Now I understood the method and learnt very important steps for other things that I have in mind.

It works fine.

How ever, two more "little" things:

1. As I mentioned in first post, one of my wishes is the possibility of adding to combos lists any new item entered by the user directly in combobox. Is this possible?

2. This issue is a new thought: in the attached file I add a new combobox (ARQ) which list is the alphabetic order (A, B, C, ..., Z). What I would like to do is when I choose, for instance, "A" in ARQ combo, MARCA combo would show itens beginning by "A" (AMSI, AVK,...). Is this possible too?

Thank you very much again

mdmackillop
11-20-2009, 10:49 AM
1. The code does this.
2. This functionality does not appear to be available, and I don't see the need for it here. A combo goes to the first item based on the character typed.