Manipulating control toolbox combo box with VBA 1

[shawnmandel]

I have the following table:

[tt]1 ABC Company[/tt]

 

[shawnmandel]

I apologize, I clicked on Submit Post by mistake...

Here is the table that I have in a sheet called Table:


[tt]A B C [/tt]
[tt] [/tt]
[tt]1 ABC_Company 12906[/tt]
[tt]2 Aldo 32600[/tt]
[tt]3 Bay 15637[/tt]
[tt]4 Bureau Engros 25900[/tt]
[tt]5 Garment_Plus 14568[/tt]
[tt]6 FutureShop 18906[/tt]
[tt]7 Staples 16788[/tt]
[tt]8 Zellers 09653[/tt]

I have the following named ranges defined:

cat => $B$1:$C$8
company => $B$1:$B$8
catlookup => $A$1:$C$8

On another sheet called UserInput, I have a control toolbox combo box with the ListFillRange property value defined as the 'company' named range. Thus, users are able to scroll through the company names in the combo box populated by the above table.

I also have one line of code associated to the combo box with the following:

Private Sub ComboBox1_Change()
Range("I6").Value = ComboBox1.Value
End Sub

In other words, I'm inserting the company name into cell I6.

Let's assume a user used the dropdown combo box to select FutureShop. I need assistance in also accomplishing the following:

1) Since FutureShop represents the 6th row, I also need to insert the row count into cell I4; i.e., I would like to see the value 6 in cell I4

2) Since FutureShop represents company code 18906, I also need to insert this value into cell I5.

The final results should appear as follows:

[tt] A B C D E F G H I[/tt]

[tt]4 6[/tt]
[tt]5 18906[/tt]
[tt]6 FutureShop[/tt]

How do I accomplish the above actions using the aforementioned combinations of named ranges?


I hope I am being clear.

Regards,
Shawn

 

[TonyJollans]

Hi Shawn,

Set the ListFillRange of your Combo to [blue]catlookup[/blue]; set the ColumnCount to [blue]3[/blue], the BoundColumn to [blue]2[/blue], and the ColumnWidths to [blue]0;8;0[/blue] (or whatever width you want).

Then , in your Change Event, code:

Range("I4").Value = ComboBox1.Column(0)
Range("I5").Value = ComboBox1.Column(1)
Range("I6").Value = ComboBox1.Column(2)

or something along those lines

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[shawnmandel]

Hi Tony,

That's perfect--exactly what I was looking for.

One more related question:

What can I do in terms of validation? In other words, now that the user has more flexibility to type in the company name, how can I build in some validation in that only the company name in the list is a valid value?

Thanks so much for the help!

Regards,
Shawn

 

[TonyJollans]

Hi Shawn,

It would be nice if MatchRequired = True was imposed by Excel, but it isn't so we need something else.

One way is to check, on leaving the combo, what the ListIndex is - it will be a value from 0 to (number of items - 1) if the input matches an entry in the list, and -1 if it doesn't so something like this should do it for you:

[blue]Private Sub ComboBox1_LostFocus()

If Me.ComboBox1.ListIndex = -1 Then
    MsgBox "Naughty, naughty!"
    Me.ComboBox1.Activate
End If

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[shawnmandel]

Tony,

That's great. All of my inqueries have been answered very efficiently.

Cheers!
Shawn