I think combobox is what I need but CAN'T figure them out!

[zookeepertx]

I have a macro that is working fine for the spreadsheet that I initially created it for. But I got to thinking that it's something I have to do at various times, so want to make it more dynamic.

To start with, I have a spreadsheet with data in columns A-G, which is sorted by F-A-C. So, the rows are grouped by data in F. My macro adds "regular" borders to the whole sheet and thick borders around each group designated by the data in F. So far, all is good.

But sometimes, I'll have other reports where I want to do the same thing, except that there may be more or fewer columns and the data for the first sort is in a different column than F.

I'm thinking that if I could get the macro to call up a combobox (or something better, if you have any ideas) that includes all of the headers in row 1 for the user to select from, then have the macro continue, using THAT column instead of F to determine where the borders need to be thin or thick.

I'm attaching a workbook in which Sheet1 is the original report that was the basis of the macro and Sheet2 is the result. Sample1 is a different report where I've sorted by B and that's the column to be used in determining which rows get grouped with a thicker border. Sample2 is the result needed from that.

To do the 2 Sample sheets, I manually changed the code to use B instead of F. What I'm hoping is that the macro could pop up a combobox with all the headers on that particular report so that you can choose which to use and, once you select, the macro continues.

I hope I've explained this clearly enough; if not, let me know and I'll try again.

Here's the code I have so far:

Please Login or Register  to view this content.

Thanks!

Jenny

[mehmetcik]

Maybe you need more than one Combobox.

It would be quite easy to have say 10 comboboxes

Combobox1 First Sort Column = F Combobox6 = Ascending

Combobox2 First Sort Column = A Combobox7 = Ascending

Combobox3 First Sort Column = C Combobox8 = Descending

Combobox4 Not used in your example Combobox9 Not used in your example

Combobox5 Not used in your Example Combobox10 Not used in your example


Don't you Think?

[zookeepertx]

Oooh, I"m sorry; I knew I wasn't being clear! The reports will already be sorted before running the macro. There just won't be any borders on them yet. I just need the macro to call up a box asking which column to use in deciding which rows get grouped/surrounded by a thick border instead of the thin one.

In the case of Sheet1, that column is F - Vendor name
In the case of Sample1, that column is B - Broker File Number

Since different reports will have different numbers of columns, it needs to include all available columns for that particular workbook in the combobox list.

[zookeepertx]

(I've been googling and testing and thinking on this for about a day and a half now and am about ready to tear my hair out!! I should be able to figure this out; at this point, I just feel stupid!)

[mehmetcik]

Sorry too Late.

I have created a userform that will do the sort for you.

I will put that aside and create a simple one just to insert your borders.

Create a Userform with one Combobox and One Command Button.

Please Login or Register  to view this content.

[jolivanes]

You can do it with a UserForm and ListBox with all the Headers also but this is simpler I think.

Please Login or Register  to view this content.

and then refer to the ranges like so
Instead of this

Please Login or Register  to view this content.

use this

Please Login or Register  to view this content.

'here the rest of your code

[jolivanes]

See if the attached comes close to what you have in mind

[zookeepertx]

Sorry too Late.

I have created a userform that will do the sort for you.

I will put that aside and create a simple one just to insert your borders.

Create a Userform with one Combobox and One Command Button.

Please Login or Register  to view this content.

Thanks for your reply! I just tried that coding and it gets stuck right away on the line "ComboBox1.List = Application.Transpose(Range("A1", Cells(1, LC)).Value)".
In the past, this computer has had a problem with referring to "Application" in a macro, but I never have figured out why. In those cases, I've just kept playing around and found other wording that made things work. Wonder what could be confusing my computer about that word?

[zookeepertx]

See if the attached comes close to what you have in mind

YES! That works great! I never thought of that approach. Funny - when I just added the lines from your first post into my "old" macro, it wouldn't let me select any cells when the input box was showing, but when I opened the file you attached, it does work there. I tried it on my original sheet here and it works there, too. Wonder why that is; the pertinent line of code (calling up the InputBoc) look just the same to me. Hmmmm...

Anyway, thank you very much for your help! This will be a very helpful macro for a lot of different reports.

Jenny

[zookeepertx]

BTW, any chance you could expain to me how these 2 lines work? What's up with all the commas, LOL?

Please Login or Register  to view this content.

Thanks

Jenny

[jolivanes]

Jenny
Here it is explained better then I ever could.
Have a read through that.

http://www.ozgrid.com/VBA/find-method.htm

[jolivanes]

This is what you originally had in mind I think

[zookeepertx]

Jenny
Here it is explained better then I ever could.
Have a read through that.

http://www.ozgrid.com/VBA/find-method.htm

Hmmm, still not getting it. That lists 7 parameters that you can specifiy for the "Find". But in the first of the 2 lines of code, you only have 3 comma-separated spots, 2 of which are blank with the final one being the number "1". In the 2nd line of your code, there are 7 comma-separated spots, 4 of which are blank with the final 2 being "2" and "1". Why the variance in the number of separate spots? I get that not all parameters have to be used, but the comma-separated blanks appear to indicate that the blank itself means something. And, if so, which parameters are being used in the first line of your code and which are being left out?

Did I mention how technologically illiterate I am?

[zookeepertx]

This is what you originally had in mind I think

That actually is what my original thought was! But it won't totally work over here; it won't let me click on any items in the listbox and then Excel kind of freezes up. I can get out of that by hitting Esc but then get an error message in the macro. I must be doing something wrong.

[jolivanes]

https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
Whichever is not used uses the default. What the defaults are, I do not know but I am sure a person can find that out if needed.

The attached from post #12 worked at this end.
Strange that the inputbox code works but the userform code does not.
BTW, tested it in Excel 2013 and 2007. Worked on both versions.

[zookeepertx]

https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
Whichever is not used uses the default. What the defaults are, I do not know but I am sure a person can find that out if needed.

The attached from post #12 worked at this end.
Strange that the inputbox code works but the userform code does not.
BTW, tested it in Excel 2013 and 2007. Worked on both versions.

Oh, well, that might be the problem; I'm in Excel 2010. Well, the other version works fine, so that's no problem. I'm still a happy camper, LOL!

But I'm still at a loss as to what the blanks and the numbers mean in those 2 particular lines. My brain is completely blank on that.

Anyway, thanks very much for your assistance!

Jenny