Code for "If ComboBox"

[lildudette]

Hi All.

Just need some help with userform. I'm trying to create an entry form for logistics. I've attached a sample of what I'm trying to do.

Basically one brand have several items that are listed horizontally across the excel sheet.
So far this is how I've been approaching the VBA.

If you can see from the file I want to make it easier to enter the data without scrolling across the page. But as you can see the code becomes too long. And I might need to add more ComboBox and items (some brand has 70 items). I've encountered in the original file that there is too many codings or something. So I couldn't even add more ComboBox.

Is there a way I could make this easier? or reduce the length of coding?

Much help would be appreciated. Thank you for your time.

(I don't know what should be the title for this)

[nimrod]

As long as you keep your pattern consistent , then the following code will work for as many comboBoxs, Textboxes and Item as you want to add.

What the code is doing is parsing out the number from "item #" and "Combox#" and "TextBox#" and then working out where you want values to go.

This code loops through all you ComboBoxes so there is no need to list each one seperately. IN-OTHER-WORDS ... if you add another 70 comboboxes you don't have to add another line of code

Please Login or Register  to view this content.

[6StringJazzer]

Please Login or Register  to view this content.

The combobox attribute ListIndex gives the index of the selected item, starting at 0 for the first item. Because you are using an offset based on position of the selected item, this works well for you. You don't even have to know the text of the selected item.

[nimrod]

Hi 6StringJazzer
You might want to tweak you code just a little by adding a test that a value in the Combox has been selected ... with your code I get the Location information overwriten if the user doesn't utilize all item drop downs. Other than that it's nice code

[lildudette]

Hi nimrod,
thank you for the codes. However the pattern on the original file is not so straight forward. Its not "item 1" to "item 20". In the excel file they go by name of the product. So not as straight forward.

Might try 6StringJazzer code to see if that's a better solution.

[nimrod]

Here's a second soln ... kind of working of the index number approach of Jazzer
I do like jazzer's approach ... if you use that sol'n the only thing I'd change/tweak is a test to make sure an item has been selected .. or you will overwrite you location information.

Please Login or Register  to view this content.

[lildudette]

Thank you so much. I've decided to go with jazzers approach. The file is too complicated haha. But thank you so much for the solutions.

[6StringJazzer]

Very good point by nimrod.

Please Login or Register  to view this content.

nimrod's solution looks a little more complex but it's actually more robust and requires no changes at all if you add more comboboxes, whereas mine still requires three lines of code for each additional combobox. I would not reject it out of hand.

[nimrod]

Hi 6StringJazzer:
Thanks for you comments on my work. But, unless the OP is going to add lots more combobox's I think he' chosen wisely by going with yours. Readability is always an important part of the code , and your's scores high points there. I also liked your use of the index ... wish I'd had thought of that. Anyway it was nice to see your work

[lildudette]

Definitely did not neglect nimrods solutions jazzer. Keeping it for later use.

[lildudette]

Hi 6StringJazzer:
Thanks for you comments on my work. But, unless the OP is going to add lots more combobox's I think he' chosen wisely by going with yours. Readability is always an important part of the code , and your's scores high points there. I also liked your use of the index ... wish I'd had thought of that. Anyway it was nice to see your work

I'm a she

[nimrod]

Hello lildudette:
Sorry about the gender specificity in my last posting ... it was a slip that I will try not to make again. ... some of my best friends, boarding buddies and work associates are of your gender