Excel VBA behaving strange - really strange

Question

I have created a simple Excel file that contains cascading drop downs (ActiveX) in a form, and it works great.  I select the first combo box and the next combo box contains a list based on the first combo box.  Simple stuff.

I needed to have additional functionality that when a button is pushed, that depending on what was selected on the the second combo box, it hides or un-hides an image.  This image is just a linked image to a range of data on another sheet.  This works great too.  I do this to give the user the option of viewing a large set of text, if needed, based on what was selected.

In order to make the text appear in the image consistently, I call the autofit function when the button is pushed so that the wrapped text in the row fits the cell.  The intent here was to ensure that the text fits the cell so that it displays all the text in the image.  This is the really strange part: whenever I use the autofit function, it doesn't matter where it's used (from the button, a different button, in the Worksheet load, etc), when I step into any function/procedure that uses autofit (doesn't matter how, if it's columns, rows, ranges, cells, or whether it's referencing the activesheet or not), it ALWAYS jumps to the subroutine for the second combo box as though Excel has somehow confused the Combo Box subroutine as the autofit function itself.  It even doesn't matter if it's set to Combobox2_Change, or ComboBox2_Click, etc.  It even recursively calls itself if the autofit function is used from within the combo box subroutine, then it produces an error and the autofit function never runs.  On top of that, breakpoints don't work in certain parts of the code, so I have to manually step each function.

I thought that perhaps that the file was corrupt, so I re-did it the whole thing, and the same thing happens.  I even deleted the second combo box and created another one, but it always somehow called the subroutine for the second combo box.  So frustrating.

At this rate, I wanted to code as little as possible with this sheet and it looks like I'll have to set up some sort of form control and have the data appear in a customized box with a scrollbar... But whatever.  

This kind of erratic behaviour in very basic Excel VBA is why I hate working with VBA.  Before I start posting code, I'm just wondering if anyone has any experience or knowledge about this kind of problem.  I'm going to start from scratch anyway, so we'll see if redoing it is faster than trying to troubleshoot this odd VBA behaviour, but I thought I'd put that out there.

Answer 1

Hello,

We suggest checking this link, and post your concern regarding Excel.

After which, select Community >Forums > View All > Type in “Excel” then select the appropriate category.

Thank you.

Answer 2

Without seeing the code there is no way to know what the problem is. It sounds like you aren't disabling events when your code runs and it's firing events when you change things.

Post the code.

Answer 3

I already have the events suppression wrapped around the code for every combobox change, and when I trace the code where changes to the Combobox2 values are made, the change event still triggers.  When the code is traced to the second combobox as it "runs" even though it shouldn't, it runs itself again when Autofit is reached (and when Autofit is run in any other part of the code).  It just occured to me, does autofit cause change events?  IF so, why only combobox2?   There really is not much to the code.  I did a test and completely suppressed the event handling for the entire time and the same thing happens.

They are ActiveX controls on the sheet.

Answer 4

While ActiveX controls can be placed directly into worksheets near to the data to which they apply, their bug laden state that has persisted for years discourages their use.  You particular problem is only one of many that remain unaddressed by MS.  If you are linking combo boxes to excel ranges on the same sheet, each change to the worksheet is going to trigger a control change event in all controls so linked.  Disable the controls referencing the sheet while making any changes to it or remove the linkages and replace the afterwards.  Regards, WHG