Listbox as table, with a twist (query information system)

[ykobure]

Good day Excel gurus!

I am working on with yet another Excel VBA project that requires display of information depending on user’s selection, basically the functionality of a query. My boss wants to have this come in as a userform-based interface.

Here is the system:•I am developing a Competitive Analysis information system.
•There is a table consists of multiple columns (in the original data I am working on, it has 55 columns) but for all intents and purposes of this example, I reduced it to 11.
•Bear in mind that the table to the left (see attached Excel file) is a result of lookups (index-small-row formula combination), and therefore formula-driven. The main database is in separate sheet (no need to include in the attached file).
•Based on the user's options in user form the display of data in list box

Does it looks like I’m in a cutthroat situation? I would love to hear other workarounds to make the system simpler and easier to maintain and update.

Competitive Analysis Sample System Flow.xlsm

Hope to hear from you soon guys! Thanks in advance!

EDIT: This issue was also posted to other forum via http://www.mrexcel.com/forum/excel-q...ml#post4212532. I am no longer expecting replies from this link. Instead, all your answers are welcome here in Excel Forum site. Thanks.

[ykobure]

I am doing my own research and I stumbled upon a glimpse of hope via ColumnWidths property. By adjusting value to zero (0), I can just hide those columns that are not relevant to the selection. The thing is, the syntax only accepts string value.

Please Login or Register  to view this content.

I was hoping to add variables in it so that I can easily manipulate it depending on the checkboxes's values. Something like this:

Please Login or Register  to view this content.

Appreciate your inputs guys.

Thanks in advance.

[ykobure]

Bumping this post up. Hope you can help me with this guys.

Thanks!

[Kyle123]

Sorry, what's your actual question? The only one I can see is "am I in a cut throat situation?"?

[nilem]

Hi ykobure,
try this

Please Login or Register  to view this content.

[ykobure]

Sorry, but my question/problem is that how can I display the contents of each column based on the check boxes selections in list box? As you can see in the attachment, there are check boxes that bear the same name of the worksheet's column heads. In the event that multiple options are placed (meaning more than 1 check box is ticked), the list box will adjusts accordingly.

But feel free to suggest other workaround should you think that the way I am heading is a bit strenuous.

Appreciate your help! Thanks.

[ykobure]

@nilem, I'll give your suggestion a go and get back to you with the result. Thanks a lot!

[ykobure]

Hi ykobure,
try this

Please Login or Register  to view this content.

This is just insane @nilem! I don't know how your code works, but it works like a charm!

One last thing, Can you also do some magic on the "Select Companies to Compare" section of the form. This will just display the company names being compared with their corresponding data. Judging by the way you code the first one, you might have a way around for this.

Thank you sir, and a Reputation point from me to yours!

[Kyle123]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[ykobure]

@nilem, just a follow up questions though. When I tried to add more check boxes (as I indicated in my original message above, I will be needing 55 of it), the column I prefer still displays correctly but the other columns no longer hide from view. That is column Q onwards of Sheet1. I studied your code but I cannot locate where it should be adjusted.

Hope you can help me with this. I won't be asking for anything else if this one gets solved.

Thanks in advance!

[ykobure]

@Kyle, apologies for this non-compliance issue. I'll edit my post here right away.

Thanks for the guidance!

[nilem]

Try to rename your checkboxes like this
Chb1, Chb2, ... Chb55
And your code would look like this

Please Login or Register  to view this content.

[ykobure]

You just saved my career, @nilem!! It now works perfectly! Thanks a lot.