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

You don't even need the select case he 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

--
- K Dales


"Arishy" wrote:

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

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!

*** Sent via Developersdex http://www.developersdex.com ***

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

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.

--
- K Dales


"samir arishy" wrote:



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!

*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

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







*** Sent via Developersdex http://www.developersdex.com ***

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


"samir arishy" wrote:



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







*** Sent via Developersdex http://www.developersdex.com ***