Parse one cell with 1 - 4 case numbers into userform

[AndyTampa]

I had an idea for the future, but it seems impossible. I have a cell in my spreadsheet where I have to place the case number of the job I'm working. Sometimes, there are 2 and I've had up to 5 so far. I have buttons that read that cell to place custom messages in a different cell. When I type these case numbers into the cell, they are usually separated by a comma and a space. Sometimes, I miss the comma or the space.

I've never used a UserForm so I'm completely unfamiliar with it. I was thinking of maybe a function. What I want is, when I press one of the buttons, I want the button macro to see that there are more than the 6 characters of the case number (when there's only 1) and call a function that will place each case number on it's own line on a user form with a button to select it for the button I pressed.

The result will be that if I press the button to reinstate a case, the button for the "Case ###### Reinstated" button will ask me which case to reinstate and likewise for other buttons. Case numbers are alpha-numeric.

Is this possible to create a macro that will look at the cell and recognize the different cases even if the separator may be wrong? At this point, I don't want to put out anyone to create it. I just want to know if it's possible and what commands might be needed for the parsing.

 

[RoryA]

As long as one or other of the delimiters is there, it should be fairly simple. Even if not, if the numbers are always 6 characters, you could simply remove all the delimiters and then loop through the cell contents extracting each 6 characters until you've done all of them.

 

[AndyTampa]

As long as one or other of the delimiters is there, it should be fairly simple. Even if not, if the numbers are always 6 characters, you could simply remove all the delimiters and then loop through the cell contents extracting each 6 characters until you've done all of them.

From what little I've learned, that would be as simple as removing all delimiters, and at each 6 characters add the same delimiter, then I can Split once I learn how that works and use the segments in a Userform. From there selecting one of them should be programmable easily enough.

Thank you. My creative gears are spinning now.

 

[AndyTampa]

I haven't even begun to learn userforms yet, but I'm thinking the code would look at the cell, remove all characters that aren't alpha-numeric and count the number of characters are left, then divide by 6 so that none remain. It would throw an error if they aren't all 6 characters. Then somehow I'd have to add a delimiter every six characters and use Split to get an array, which I also struggle with.

This is going to be a tough one.

Can a UserForm be dynamic?

In other words, can it only display one button if there's only one case number and 3 buttons if there are three case numbers?

I imagine those case numbers can be placed on the buttons instead of beside them too?

 

[RoryA]

Yes a userform can be dynamic. From what you've described though I would just use a listbox that you populate with the case numbers, then one button to do whatever is needed with the selected item(s). I don't really see any need to add delimiters just to split on them. You may as well just test if the length is evenly divisible by 6 (using Mod for example), then take every 6 characters using something like (air code):

for i = 1 to len(cell.value) step 6
listbox1.additem mid$(cell.value, i, 6)
next i

 

[AndyTampa]

Yes a userform can be dynamic. From what you've described though I would just use a listbox that you populate with the case numbers, then one button to do whatever is needed with the selected item(s). I don't really see any need to add delimiters just to split on them. You may as well just test if the length is evenly divisible by 6 (using Mod for example), then take every 6 characters using something like (air code):

for i = 1 to len(cell.value) step 6
listbox1.additem mid$(cell.value, i, 6)
next i

As fun as Excel is to make my little tools with what limited knowledge I have, every so often a post just tells me I know a lot less than I think I do.

I was thinking of a dynamic userform for another project of mine. It's a prorating calendar for premiums and calculations are based on mid-month beginning, endings, or changes. The calculator I already wrote has no macros except a reset button. Everything else relied on hidden cells and formulas and I'm pretty proud of it. However, when things like the formulas they use change, it's very difficult to retrace my steps to correct the calculator. I'd like to use VBA. In regard to a dynamic userform, my calculator has 6 input fields. Three of them become invisible under mid-month beginnings and endings. Can a userform be made to do this? Or would I be better served by just using the worksheet I have and adding code to it? My thinking is that it is so much easier to change formulas in the code instead of fighting through nested formulas with all the if statements and dependencies in hidden cells.

 

[RoryA]

Can a userform be made to do this?

Yes, it's pretty easy to show/hide (or disable/enable) controls on a form. It's also possible to add or remove controls at run time, though it gets a little trickier if you need code associated with those controls.

 

[AndyTampa]

Wow. Thank you.