Combo box help

[superfb]

Hi all,

I have a macro workbook that's been created by a predecessor. It has a combo box (drop down) that gives a list of the templates the end user would like to use.

The macro when selected would pick out this template and produce tables.

My issue is, the template names are listed in column E and F. The linked cell to the combo box is E2. Column E is Output file name and Column F is Template. Both the names in each column is the same.

Col E. Col F
Total Total
Pay Pay
Costs Costs

I would like the combo box be more intuitive for the end user by having Table 1 - Total, Table 2 - Pay and so on.

Is there a way how I can change the name showing in the combo box however the underlying data remains the same format so the macro can pick this up.

So Drop down shows
Table 1 - Total........but Total is shown in E2
Table 2 - Pay...........but Pay is shown in E2

Thanks

 

[Micron]

If this is an ActiveX control then based on how I interpret your post I'd say you need 2 columns in the combo and to base the source on 2 sheet columns that contain the values you want to see. If necessary, these could be 2 new columns for the list values and you can hide these columns if need be. You'd have to play with the column widths and list width properties (in points) to get it to look nice.
my test source range is O3:P6

Combo has 2 cols, bound column is 1; col widths are 0; 100, list width 100

My value is in D12

EDIT - source column #1 would contain the values you need to pass to the code. This needs to be the bound column but does not need to be visible to the user.

 

[superfb]

If this is an ActiveX control then based on how I interpret your post I'd say you need 2 columns in the combo and to base the source on 2 sheet columns that contain the values you want to see. If necessary, these could be 2 new columns for the list values and you can hide these columns if need be. You'd have to play with the column widths and list width properties (in points) to get it to look nice.
my test source range is O3:P6
View attachment 116998
Combo has 2 cols, bound column is 1; col widths are 0; 100, list width 100
View attachment 117000
My value is in D12
View attachment 117001

EDIT - source column #1 would contain the values you need to pass to the code. This needs to be the bound column but does not need to be visible to the user.

Hi

Thank you for the reply. This is exactly what I am trying to mirror. Could you kindly share the properties to your combo as I am trying to replicate it but I feel I am missing something. Thank you.

 

[Micron]

I think all that information is in the details of my post? Anway,

Pretty sure you can ignore the Value property as I suspect it is "Cost" only because that is what I last chose.

 

[superfb]

I think all that information is in the details of my post? Anway,
View attachment 117006

View attachment 117007
Pretty sure you can ignore the Value property as I suspect it is "Cost" only because that is what I last chose.

Thank you that is very helpful. I needed to put 2 in column count.

I have attached my result.

When u select the drop down I can see Table 1 - pay etc however when I select it I guess the corresponding value pay will be in the combo box and in the linked cell?

 

[Micron]

when I select it I guess the corresponding value pay will be in the combo box and in the linked cell?

AFAIK, that depends on which column you decide to show when the drop down is closed and which column you decide is the bound column. If they are the same, then whatever is shown after a selection is put in the linked cell. Either way, it is the bound column that goes into the cell. If that is not visible (has zero length) then what is shown in the combo after a selection is the first non-zero length column from the left and what goes into the cell is what is in the invisible (has zero length) bound column. I think that is the goal you're trying to achieve. Are you scrutinizing my post for such details? Pretty sure it's all there, although I may not have put a lot of spin on it.

I'm mainly an Access guy so that's why I say AFAIK.

 

[superfb]

AFAIK, that depends on which column you decide to show when the drop down is closed and which column you decide is the bound column. If they are the same, then whatever is shown after a selection is put in the linked cell. Either way, it is the bound column that goes into the cell. If that is not visible (has zero length) then what is shown in the combo after a selection is the first non-zero length column from the left and what goes into the cell is what is in the invisible (has zero length) bound column. I think that is the goal you're trying to achieve. Are you scrutinizing my post for such details? Pretty sure it's all there, although I may not have put a lot of spin on it.

I'm mainly an Access guy so that's why I say AFAIK.

Hey! No not at all, not scrutinising your post at all You very kindly took the time out to help me and wrote that post so I'm very grateful! So thank you

This is the first time I've come across "bound column". I've managed to draft something up, I'm just going to play around with it!

 

[Micron]

I'm saying you should be scrutinizing my posts - that is, carefully reading the content because it seems to me that you're overlooking details when you ask questions that I think I covered. Then again, sometimes I don't explain things well enough, probably because what I write makes sense to me, and that can happen when I don't know what the OP knows about a topic.

To expound: your 1st post indicates to me that you want to see Table 1 - Total when you drop down the list. So that needs to be in col. B (or whatever - I will use sheet columns A and B to explain. Don't confuse this with combo columns). Beside that in col A you need Total. Combo list comes from A2:B6 (or wherever your list is). In combo properties you hide the first combo column (zero 0 for width) and make 1st column the bound column. When user drops down he sees Table 1 - Total If user picks that list row, that's what ends up being displayed in the combo. What goes into the linked cell is the bound column value - "Total". Your code should pick that up from there. TBH, you don't really need the linked cell as the code can get the value from the combo but you would have to modify the code and probably don't want to go there.

Excel and Access combos don't behave exactly the same way, so I hope I'm not overlooking anything, but as per my pics, I seem to have created what you're after.