Combobox_change and VBA MAC/Windows

My first visit to this forum, so please be nice! Just hope someone can help. I've got a pretty complex VBA project that I've put together containing around 150 subroutines, 15 different user forms and a whole heap of untidiness thanks to the organic way in which I've developed it over the past 12 months or so.

I've just stumbled across two issues in my final checks on the project:

1. I've got a combobox on tab 2 of an 8 tab multipage. Each tab has a button at the bottom which advances the user to the next tab, the previous page then being locked down and inaccessible. I set up a subroutine counter, so that every routine logged when it was accessed. For some reason the combobox_change() routine for this object, once activated, continues to be accessed long after the value is selected and the relevant page disabled. By the time the form completes its running, this routine has potentially been accessed 500-1000 times!

I've managed to nip this in the bud by adding combobox.value="" once the combobox input has been used and recorded on the data storage worksheet in the underlying XLS, but it's rather a clumsy solution to a problem that - as far as I can see - shouldn't exist.

The combobox is definitely not changed by any other events or code lines, as I've searched the entire project, and it doesn't have a cell-link, so there's no question of an underlying cell being changed either. The Change event is supposed to trigger when there's a change of value, but Msgbox reports confirm that the Event is being triggered when there has in fact been no underlying change. Most puzzling of all, combobox.enabled=false and combobox.locked=true don't stop the macro activating either!

Is this a known bug, or is there some underlying thing going on here!?

2. I've just sent my project out for wider examination, and it's immediately been flagged to me that it doesn't work on Mac Excel. Are there simple steps required to convert the sheet, or is the possibility of a multi-platform project pretty much dead in the water?

For info, I'm very much an amateur excel enthusiast with no formal training who honed his programming skills on the ZX Spectrum (10 Print "Bum", 20 Goto 10), which probably goes a long way to explaining why my sheet is somewhere over 20,000 lines of code!

Originally Posted by romperstomper

1. Sounds like something underlying going on.

So, any idea what can cause a locked, disabled control object that isn't being directly accessed either by the Code or the Worksheet to keep firing!?

2. Which version of Mac Excel? 2008 doesn't support VBA at all, so you're dead in the water if it's that version! Earlier versions do, but it's basically VBA5 (what was in Excel 97)

Well, that'll be dead in the water then. Phew. One less job

Originally Posted by romperstomper

How is the combobox populated? Using the RowSource or with List/AddItem?

Its populated using RowSource, which in turn looks at a variable in a named range. Something broadly like this:

rowsource=range("combosource")
range("combosource")=Generator!C2
Generator!C2="="A1:A" & counta(A:A)

The range that is being counted isn't changed by anything in the VBA Project. Although VBA Help does say that the Change event is triggered by a change in .Value, not in RowSource but I guess the fact that there is a calculation involved may mean that it keeps firing.

Guess the way to test it is to set Application.Calculation =xlmanual and see if that stops it. The only thing that bothers me is that I have other comboboxes in the project which are allocated using this same method, and they don't have the same Change event issue.

I am at least reassured (or perhaps disappointed?!) that it's not a known issue, so it must be *something* tucked away in the depths of my sprawling mess....

Originally Posted by romperstomper

Unless you need more than 10 columns in your combo (or listbox), I don't recommend ever binding controls to a worksheet in any way.

Any particular reason for this?

I try to avoid hardcoding references to allow for more customising when I inevitably want to amend the sheet later. I try to have as many of the variables and reference written into Excel, so that the Code just contains the data processing.

I tried changing the RowSource reference to lock it in, but the routine is still accessed repeatedly. Thanks and nice try, but that's not it

Originally Posted by romperstomper

Precisely because of the sort of thing you are experiencing (and changing the Rowsource was not my suggestion - not using it is what I'm suggesting)

OK, I see what you mean, but I don't believe this what's causing the issue. I guess the true test is to remove all references to the worksheet, and see if I have the same problem with a set array of options. Although I'm getting a Permission Denied runtime error when I try to use either List or AddItem. Odd...

I think there's some funny business going on somewhere. Back to the drawing board I suspect!

Originally Posted by romperstomper

You can't use AddItem or List while you have a RowSource set because they conflict with each other. You have to clear the Rowsource first.

Yup, that did it ok, thanks for that (thought I'd commented out the RowSource line, but I was missing an underscore all this time).

And whaddya know, that has indeed solved the issue. I've also tested it with setting the range on a different worksheet, so I think the root of the problem is the fact that the Referenced range is contained on a sheet which has it's own Worksheet_Change code running. Some major rationalisation needed all round.