Crash Following Combobox Scroll Wheel & VBA Project Update

[jermsell]

Very nice work on this code Cristian! Most everything works tremendously and I'm very impressed. I am running into an issue, however, when scrolling a combobox and then attempting to change a worksheet codename. I have a userform with a combobox. By default this combobox has 11 list items and shows 8. Additional items could be added, however, so the list is not just limited to 11. The user selects an item from the list and, following a button click, a new worksheet is copied from the initial worksheet, the sheet name is changed based on the combobox selection, and the codename is changed for ease of use and identification later. When any item from the first 8 are selected my code runs fine. If I grab the vertical scroll bar and slide down using the cursor and select items 9, 10 or 11, the code runs fine. If items 9, 10, or 11 are manually typed the code runs fine. If I use the mouse scroll wheel, however, and select items 9, 10 or 11, or scroll to the bottom and then use the mouse wheel to scroll to the top of the list and select one of the top 8 items, the code will run and then crash Excel with no error or warning given. If I comment out the line that renames the sheet codename the crash does not happen. Any thoughts on how to remedy? I've reviewed previous issues (specifically #22) and can't resolve.

To my point above, I considered increasing the default number of visible items (eliminating the scroll altogether), but don't want the list to be cumbersome if many additional items are added.

Any help is appreciated - thank you and nice work!

[jermsell]

Another item I forgot to mention:

If I step through the code, or interrupt at any point before changing the worksheet codename, the code will run as expected.

[cristianbuse]

Thanks for all the info. I am away from a computer until Monday and will only be able to test then.

[cristianbuse]

Hi @jermsell

How do you change the worksheet CodeName using code? The property is read-only, so I can only assume you're using a workaround of some kind.

[jermsell]

Good morning @cristianbuse. Thanks for the response. We have a lot of worksheets being added for calculations and to manage them the sheet name, but also the codename is changed. Each user has 'Trust access to the VBA project object model' enabled in the Excel settings. In the code the sheet and codename are changed:

On Error GoTo Err_CalcNm
.Name = strShtNm
On Error GoTo 0

On Error GoTo Err_VB_Proj
ThisWorkbook.VBProject.VBComponents(.CodeName).Name = "Calc" & .Range("Calc_CodeNmSub")
On Error GoTo 0

As noted, this code runs fine if I don't use the mouse scroll wheel, but there is something about the wheel that causes Excel to crash on the line of code starting "ThisWorkbook".

[cristianbuse]

@jermsell

I tried but I cannot replicate the crash.

The codenames can be accessed programatically so a 'rename' like this must affect the global namespace and even potentially cause a recompile. I bet there is some obscure edge case where this is causing a crash like you're experiencing.

I can only think of the following possible workarounds:

1. Add a DoEvents call before and after the ThisWorkbook.VBProject.VBComponents(.CodeName).Name = "Calc" & .Range("Calc_CodeNmSub") line
2. Add a delay using Sleep (use last edit) before and after the line. Maybe there needs to be some delay until some async code finishes the new sheet setup - although I wonder why the crash doesn't occur for the first 8 items in your combo list
3. Use the Worksheet.CustomProperties to store and retrieve relevant sheet information instead of using CodeName and ditch the whole Trusted VB Access

[jermsell]

@cristianbuse - Thank you for the suggestions. DoEvents did not help and I was unable to get the Sleep code to work. I tried playing around with Worksheet.CustomProperties, however, this would require a pretty substantial rewrite of my program and in the end I thought it'd be more trouble than it was worth (good to know for the future though!).

I think I resolved the issue by moving the error handling and ThisWorkbook.VBProject.VBComponents(.CodeName).Name = "Calc" & .Range("Calc_CodeNmSub") after the userform is closed and the mouse scroll is terminated. This seems to work, but still undergoing testing. Will update once I know for sure.

Thanks again - your code is amazing!

[cristianbuse]

Thanks @jermsell for the kind words and the feedback! Looking forward to hear from you.