Combobox with a dynamic list

[Arishy]

I created a combobox that accepts a dynamic list. The problem is:
when I program the Case code for the event Combobox_change I am faced
with the missing code for the new cases ( added items in the dynamic
list )

Let me expand:

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case ("item1")
Worksheets("item1").ShowDataForm
Case ("item2")
Worksheets("item2").ShowDataForm
Case Else
End Select

Now I have the new "item3" in the combobox but no code to execute the
selection

The needed code is:

case ("item3")
Worksheets("item3").ShowDataForm

How can I can PROGRAM adding these lines to my CASE section ????

You help will be greatly appreciated ...as allways

 

[Guest]

You don't even need the select case here: as long as you ensure only valid
items (e.g. Worksheet names) are in the combobox you can just do this:

Private Sub ComboBox1_Change()
Worksheets(ComboBox1.Text).ShowDataForm

 

[samir arishy]

That will work if the combobox.txt is the worksheet name but for the
sake of "a friendly user interface!" I chose a descriptive text.
I do have the relation between the combobox.text and the worksheet
names. I can create a table somewhere with
comboxbox.text and name of the worksheet. BUT How can I program that
into your "super" one liner. Let us say I have this relation in sheet(x)
range A1:B10.

Can I use a vlookup and put the result in your one liner? If yes can you
help ??

The vlookup is an excel rather than VBA as you definetly know!

 

[Guest]

Yes, you can use your lookup within the VBA code -
Worksheets(WorksheetFunction.Vlookup(ComboBox1.Text,
Worksheets("Sheet1").Range("A1:B10"),2,FALSE)).ShowDataForm

But a nicer solution would be to set your combobox so it gets its values
from the range; set the following properties:
BoundColumn = 1
ColumnCount = 2
ColumnWidths = 0,80 pt (keep 1st zero but adjust 2nd number so it matches
the size you need
ListFillRange = A1:B10
ListRows = 10

This will make it use your list as the columns but hide the first column
(the sheet name) so the user sees only the B column (descriptive names you
define) - once they make their selection the .Text will be the sheet name and
you can use the original code I sent.

 

[samir arishy]

I love this tweaking of the combobox properties BUT
As the range A1:B10 is dynamic it will change when new worksheet is
added.

Now, if I give an OFFSET Name to the range (ie making it a dynamic range
( I define the range using the OFFSET )

Will I be able to use the variable myRange (the "named" range instead of
"hardcoding" the range.

Provided ...YOU... can do that...How please

A minor point... The table as it stands now is A1 descriptive B1 Sheet
name which is opposte to what you thought

 

[samir arishy]

Attension: K Dales

Your super one liner (vlookup) does not work with dynamic range name

Here is my code:

Private Sub ComboBox1_Click() ' or _change ?



Dim Suplist As Range ' I have a named range for A1:B14
Dim nCols As Integer
Dim lRows As Long



nCols = 2
lRows = 14

' I need to resize in case of additions

Set Suplist = Sheets("Home").Range("A1").Resize(lRows, nCols)
Debug.Print Suplist.Address ' when I resize it is OK

If ComboBox1.Text = "NewSupplier" Then
createNewSupplier ' this is another macro
Else


'Debug.Print ComboBox1.txt 'did not work

'WorksheetFunction.VLookup(ComboBox1.Text,
Worksheets("home").Range("a1:b14"), 2, False) ' did work

WorksheetFunction.VLookup(ComboBox1.Text,
Worksheets("home").Range("Suplist"), 2, False) ' did not work
WorksheetFunction.VLookup(ComboBox1.Text,
Worksheets("home").Range(Suplist), 2, False) ' did not work also

Why ...I am really Stuck

 

[Guest]

Suplist is already a Range variable - this should do it:
WorksheetFunction.VLookup(ComboBox1.Text,
Suplist,2, False)

 

[samir arishy]

Thank you for your followup.

Vlookup inside VBA cannot accept a range for the table unless it is the
form Range("xx:yy").

I spent the last two hours to prove me wrong Pls a simple test on your
machine can put me right again.

I have a Named range Suplist defined as

=offset(!$A$1,0,0) To allow it to change dynamically
When I put it in your Vlookup line I get an error

If I replace it with Range("A1:B12") it works !!!!

 

[Guest]

Which version of Excel are you using?
I have used Range variables in VBA's WorksheetFunction.Vlookup many times
with no problems, and tested my code earlier by setting up a scenario using a
named range.

This much at least should work:
WorksheetFunction.VLookup(ComboBox1.Text,Range(Range("Suplist").Address),2,
False)

But I still can't understand why it would matter. Once you set the VBA
variable Suplist to be equal to a range, no matter how that range was
originally defined, it should be equal to that range - the address and all
the properties. But do note that the VBA variable Suplist would not in any
way be linked to a range that is given the name "Suplist" through the Excel
Insert... Name... Define menu. The two are separate entities. If you are
expecting your VBA Suplist variable to change as the named range Suplist
changes, it doesn't work that way. If you need to do it that way, reset the
VBA Suplist to be equal to your named range Suplist just before you use it in
the VLookup function:
Set Suplist = Range("Suplist")
WorksheetFunction.VLookup(ComboBox1.Text,
Suplist,2, False)

 

[Samir Arishy]

I did not realize that you actually tested it before forwarding to me.

<<...The two are separate entities...>>.

You hit it on the nail. I assumed they are related. Whatever happens to
the "NAMEed" range in Excel has nothing to do with the what is going on
in VBA.

So, What I should do is Create my own VBA range and use it in the table.
What is your advice in coding this part.

Here what I have A1:Bx whatever
I need to create a range to reflect the current range
let me call it "myVlupRng". If I get it right with your help then I use
that in the VLOOKUP line. And just forget about what I have in my Named
Range.

What code shall I use to define myVlupRng

 

[Guest]

As is often the case, in VBA there are many options and to hopefully
illustrate a few different techniques I will try to show a few. The only
real problem is using the VBA variable (we will now refer to myVlupRange to
avoid confusion) to refer to Suplist if Suplist has changed AFTER the line
Set myVlupRange = Range("Suplist"). There are a few ways you should be able
to get the range right in the WorksheetFunction.VLookup line:

1) Use the named range directly in the function:
Worksheets(WorksheetFunction.Vlookup(ComboBox1.Text,
Range("Suplist"),2,FALSE)).ShowDataForm
This should use the actual named range Suplist if you have it in quotes, and
use its CURRENT set range at the time you run the vlookup - so as long as the
named range is correct at the time this line executes it should work.

2) Make sure your variable myVlupRange is "in synch" with Suplist when you
use it for the lookup. To do this: after you have set and read the combobox,
and immediately before the VLookup, reset the range myVLupRange to be the NEW
(changed) range Suplist (the following two lines should go together in your
code):
Set myVlupRange = Range("Suplist")
Worksheets(WorksheetFunction.Vlookup(ComboBox1.Text,
myVlupRange,2,FALSE)).ShowDataForm
This will insure that myVlupRange is updated to reflect the current range
SupList

3) You could bypass the use of the named range SupList alltogether by having
your code "refind" the relevant range every time it needs to do the lookup.
If your list is always a continuous block of cells surrounded by blank cells,
you could use the CurrentRegion to find the list:
Set myVlupRange = Sheets("Home").Range("A1").CurrentRegion
Worksheets(WorksheetFunction.Vlookup(ComboBox1.Text,
myVlupRange,2,FALSE)).ShowDataForm

I think any of these should work, you can choose the one that best meets
your needs.

 

[samir arishy]

New To VBA

 

[samir arishy]

You made MY DAY Thank you ...

Sorry for the blank message earlier