Filtering a HeaderRowRange list

[TheScott]

This code picks up all of the table headers and presents them to a combobox.
Many of the column names on that list are irrelvant to the task of the combobox and present clutter and confusion.
Is their anyway to filter this list before it is presented to the combobox?

Please Login or Register  to view this content.

Thank You for having a look

Scott

[LJMetzger]

Hi Scott,

The code you used only seems to work with a continuous range. If there are gaps between columns try something like the following (tested and working):

Please Login or Register  to view this content.

Lewis

[Norie]

How do you want to filter the headers?

[TheScott]

Over the weekend I have reworked some approaches to how I want this module to work with others that I am working on. In doing so I changed the code originally posted in this question. The problem, however still remains. I want to say that the code originally posted to correct the problem did work and I thank you for that effort. I will restate the original and will add an addendum to it as it relates

1. The comboboxes are filled with table header names (all of them). Some of these names do not apply and are not appropriate for selection in these comboboxes. I would like to be able to remove them. While in this example they are contiguious, it may not always be that way and I might desire to exclude single columns elsewhere in the table.

2. The comboboxes provide a list of expense categories. On any given transaction up to 6 categories can be chosen to split up the original transaction total. The problem is that no category item should ever be chosen twice in the same transaction, yet the same drop down list exists in all 6 comboboxes providing the same choices already selected in a previous combobox. What I would like is for the list to adjust to remove a previously selected category.

Examples on in the file show the problem graphically…. I hope.

Your help is always greatly appreciated.

Scott

[LJMetzger]

Hi Scott,

Very nice Spreadsheet design and ditto for the UserForm Design.

I think your problems can be solved as follows:
a. Add a helper row (that can be hidden) above row 2 in your table. In each cell above a category to be excluded you would put an 'X' or some other character or group of characters to indicate that that category would not be included in the UserForm. Any category with an 'X' above it would not be included in the ComboBox List.

b. To remove items from the list, is doable, but a little more complicated, because when you change categories in a ComboBox, the old value must be recycled into the list. One way to solve this problem is to:
(1) initially put all the valid categories in a two dimensional array before populating the ComboBoxes. The first index would contain the category names. The second index would indicate whether the category is currently in use by a ComboBox.
(2) When a ComboBox selection is made use the .Tag property of the ComboBox to store the current ComboBox Value. The previous .Tag value (if not blank) would be the category to be recycled.
(3) In the two dimensional array mark the recycled value as available.
(4) Clear all ComboBox Lists.
(5) Create a new list for all ComboBoxes.

----------------------
The .Tag property is a little known item available in UserForm Controls. It is an item that the programmer can use to store anything. It is quite useful in situations like yours. There is very little documentation about the property.

I am very busy the next few days. If I don't see a response that you like, I will try to post possible updates to your file on Friday or Saturday (US Eastern time).

Lewis

[LJMetzger]

Hi Scott,

I had time to implement the changes I mentioned in the previous post. My goal is to make you comfortable with your file. Anything that I did is negotiable, if you don't like the say I did something.See the attached copy of your file which implements the following:
a. Inserted new row 2 in Sheet 'Data'. It is currently VISIBLE, so you can see what I did.
b. Implemented code to seed the UserForm ComboBoxes with some Categories omitted. The omitted categories are NOT BLANK in row 2.
c. Implemented code to remove a Category from all ComboBox lists when the category is used by any ComboBox. If there was a VALUE in the ComboBox, that VALUE is placed in restored in all ComboBox lists.

The changes above were made to the UserForm module (changes in red), and the addition of module 'ModUserFormTransactions'.

UserForm 'Transactions' modified routines:

Please Login or Register  to view this content.

New module 'ModUserFormTransactions':

Please Login or Register  to view this content.

Lewis

[TheScott]

Thank you so much for your time and thought; it appears we crossed in the mail. I wasn’t expecting you back until Friday so thought posting today would give you a heads up on what I had done before you expended so much effort.

I have been working on this since your suggestions on Sunday. With about 100 web sites, a couple of pieces of formula, an answered question and a kick in the butt, I created the posted solution. I will implement yours and study it… no doubt it is better but I would be ever so grateful if you could review the approach I took and let me know, in your opinion, its pros and cons... an exercise in learning.

Thanks

Scott


Expenses V3 Example Modified.xlsm

[TheScott]

Lewis,

I have totally re-written this message as it was, in error, partially a duplicate of the one above.

Originally yesterday I had posted that initial observations of your solution showed a problem wherein only the first textbox value associated with the comboboxes posts to the spreadsheet. That problem persists. I have not been able to discern the location of the error but will keep trying... no doubt it is obvious to anyone with any real knowledge. Anyway, the following is added today.


I have had some time to go through your solution and it appears, with the exception of it not posting all of the amounts to the spreadsheet, it does everything that mine does plus addresses the concerns about expandability that I had with mine… very cool. You even, either deliberately or inadvertently corrected another small problem that I was going to address in the future.

Other than my inability to understand exactly how you accomplish the tasks, it does exactly what I was looking for. I still am very fuzzy on arrays. I mean, I understand the concept and can even visualize them (at least up to 3 dimensions at which point my creativity falters) What I don’t get is the mechanisms by which you populate an array and even fuzzier is how you locate, much less extract, the individual elements. It will come… maybe.

I do feel bad though. You have been busy and it would appear to me anyway, that this took a reasonable amount of time to create. I know you do this because you want to but it almost feels like I have ripped something from piratebay… Thank you again. Thank you for your initial hints because it gave me the basis for finding my own, if less capable solution from which I learned, and of course for your solution. I can now carry on … and create even more questions.

With Regards
Scott

[LJMetzger]

Hi Scott,

Thanks for the rep points. It's my pleasure to help, especially for someone who is trying to learn to fish.

I was able to find out the cause of some of your problems, but not the reason why the problem occurs.

...showed a problem wherein only the first textbox value associated with the comboboxes posts to the spreadsheet. That problem persists.

1. For some unknown reason the ComboBox .ListIndex value always had the value -1, even when ComboBoxes had lists in them. When I changed the test from .ListIndex to looking for the value in the ComboBox I had success. Your test for the first ComboBox always tested for the VALUE.

2. The use of 'Option Explicit' at the top of each code module is highly recommended. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer).
http://www.cpearson.com/excel/DeclaringVariables.aspx

Important excerpts of the code I used from Sub xxx follow:

Please Login or Register  to view this content.

I will address the questions you raise inside your sample file in the following post.

Lewis

[LJMetzger]

1. The 'masterlist in column A is a linked list to the header row of the table on the Data sheet. This allows that any changes in header names are posted to this list. The problem is that this list is not dynamic in that if I add a new column, it is not added to this list. I don't know how to address this problem

It's almost as if you have two Master Lists, which increases the likelihood that at least one of them is going to be wrong.

If you're going to have a Master List on Sheet 'formulas', one way to implement it would be:
a. There would be NO FORMULAS linking the lists on Sheet 'Data' and Sheet 'formulas'.
b. Column 'A' in Sheet 'formulas' would be the Master List. The items on the SpreadSheet in Sheet 'Data' and in the ComboBoxes would be in the order that is in Column 'A' on Sheet 'formulas'.
c. Each time Sheet 'Data' is activated, there would be a check to make sure all the columns are in the correct order, and there were the correct number of columns. If not, then the data in Sheet 'Data' would be manipulated to add/delete columns. If a column were deleted that had data, some adjustment would be necessary to keep the 'Balance' correct.
d. The data in columns 'D' thru 'I' on sheet 'formulas' would have to be verified and/or changed each time a Change is made in Column 'A' or 'B' of sheet 'formulas'.

It could be done, but it seems like a lot of duplication of work to me. It seems that it isn't that difficult to add Columns to the Table on Sheet 'Data', or to move columns around.

Lewis

[TheScott]

Lewis,

This use of .Value vs .ListIndex and the conversations and code changes that occurred around them are what prompted me to PM you for advice. The story is long, involved the comments of three different people leaving me with code that did not function as I wanted so in the end I fiddled with the code until it worked. Sorry for the problem if I injected it. It did not show these symptoms before so in my ignorance… I had gotten it right. When changed the first combobox back to .Value and it worked, I then changed the others to which I got this error:

Combo.value.png



So I left them as listindex

I am not quite sure about how your comments reference ‘option explicit’ come into play with this problem… confused? I understand your point for its need but did not using this inject an error somewhere?

As for my solution, comments taken. I think the only reason I created the ‘Masterlist’ was visual clarity. It certainly is a duplication of the original ‘masterlist’. I too, in the process of discovering this solution, found a lot of flaws which required too much manual intervention. For me it was more a lesson in the interaction between formulas on the sheet and controls on the userform. The formulas and that solution are probably more appropriate to controls used on the worksheet anyway. I always suspected that there would be a much better way to do it and your solution did that quite well.

Scott

[LJMetzger]

Hi Scott,

Your test conditions are incorrect. Each test will always be true unless the Category Name is "-1". My test works as follows:
a. Look at the category name 'CbAllocate2.Value'
b. Remove leading and trailing spaces 'Trim'
c. If the number of characters remaining is greater than zero, it must be a category name
NOTE: If you type in a Category name that isn't in the list, the test will pass and you will get the same error that you had highlighted in yellow, because the 'CATEGORY DOES NOT EXIST' and there is no valid 'ListObjects' column.

Please Login or Register  to view this content.

-------------------
Your error isn't due to 'Option Explicit'. 'Option Explicit' saves a lot of headaches when you spell something wrong. As a test, comment out 'Option Explicit', and replace 'response ' with 'resp0nse ' (ZERO that looks a little like the letter 'o'). The logic is correct but the answer will always NOT be 'vbYes'. I run across errors like that all the time in other people's code, and in my code when 'Option Explicit' is accidentally erased. I once spent more than two weeks confused by a variable spelling error, when I knew the logic was correct.

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

I hope this helps.

Lewis

[TheScott]

Auh Ha! My confusion is cleared. Suffice to say most of it was a communications error on the part of the receiver. Almost had another round of question and answer... then... and 'oh yah' moment. We are clear now as to what you did and why. I am still trying to study my way through your module but understanding more every time I go through it.

Thank you for the explanation of the Len and Trim functions and where I created a silly, and in hindsight, obvious error in my condition testing. Very helpful!
I should put this thread to bed and mark it solved…. Thanks for your help… now that you have some spare time you can go shovel some snow.

Regards
Scott