Multiple Column Lookup Needs to combine formatting for Percentage and Text String.

I am using a variant of a code excerpt for using multiple columns validation list boxes.

The code snippet is as follows

Please Login or Register  to view this content.

Column 8 = Column H contains a percentage. Column 9 = Column I contains a description of what the user must have completed to earn the percent complete. Column 10 = Column J stores the data from column 8 but is formatted to read as the value in column 8 followed by the description from column 9. Column 10 or J is the range to which the named range is pointed for the validation list data. So even through it is supposed to read percentage then text, it only return the percentrage to the cell using the validation list.

Please Login or Register  to view this content.

The code above works perfect if the values in column 8 are text based or general based, but when using percentages in column 8, the description text of column 9 disappears and the percentage goes back to being formatted as a decimal i.e. 15% becomes 0.15.

A second issue to be worked out later is that my data ranges that will be using these validation lists as the source for the drop downs are quite narrow column widths (They are only intended to store the percentage value!). The descriptions are quite lengthy and as such I only see a portion of the descriptions in the validation list drop down box. I have been searching for hours on the net for any way to customize the width of the validation list drop down box, but it seems the concensus is to write some VBA code to make the column housing the drop down box wider (temporarily) as the width of the drop down is based on the width of the column using it, and then reset back to the original column width after the user navigates away to some other part of the sheet. This is quite annoying to me and seems to me something that the user should be able to change via a properties dialog box. Maybe in some future version of Excel!. I have 20 or so columns that point to 20 different validation lists with an unknown number of rows of data being pulled in from multiple access databases, so trying to overlay comboboxes on the cell doing the lookup seems daunting.

Thanks.

I have come up with what should have been an obvious work around the first time out of the gate, however it does not address the fundamental question of how to combine the formatting of a percentage value followed by a text string.

I left column 8 as is. I altered column 9 to append the percent complete value at the front of the description. Then I revised the subroutine which points column 10 to take the value of column 8 but format the visible value to the value in column 9 only, rather than try to combine the values from column 8 and column 9.

Here is the revised versions.

Please Login or Register  to view this content.

Now the columns look like this:

Please Login or Register  to view this content.