Macro behaving differently depending on how it is called

I have written a macro that creates an Excel sheet that is then given to users. This macro works perfectly when called from the Run Macro box. It also appears to work if an ActiveX button is used. However it does not work correctly with a Form Control button.

I am using Excel 2013 at work and this is the version that is having problems. I have tested this on my home computer on Excel 2010 and it works perfectly with the Form Control button.

Does anyone know why Excel 2013 would behave differently when called from a Form Control button than from other methods?

I realise that this is not a major problem since the ActiveX button works but it is a mystery I would like to solve.

Many thanks for any help.

Hi,

It works OK for me using 2013. Are you perhaps using a Mac at work and Windows at home.

Just an observation but disabling screen updating would be easier on the eye and speed things up a bit.

It's perhaps a bit late now since you've put a significant amount of work into this, but for any future development consider the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Re screen updating. I'd still disable it and use a progress bar or even just the status bar to let the user know things are still progressing.

I wasn't suggesting for one minute that the end form should be changed. I was simply suggesting that it would be more efficient to use a normalised 2 dimensional table (or tables) of data as the source for populating the final form.

When you say the Form Control button doesn't behave 'correctly' what does happen?

Have you tried toggling a BreakPoint on the first line of the CreateLists procedure and then stepping through to see where things go awry?

Hi,

It seems to work OK for me. i.e. it copies the filtered column I values on the Raw Data Sheet to E6 on the Attendance Summary Groups sheet. You say this last Copy is from the wrong sheet. If not the Raw Data sheet which sheet should be copied?

Are you sure that in stepping through the macro and presumably jumping back to Excel to see the effect you haven't inadvertently changed the Active 'Raw Data' sheet to some other sheet? This is quite easy to do when you look to see what is happening to two sheets.

It's always advisable to fully qualify which ranges you are dealing with by including the sheet reference in a VBA instruction. i.e. instead of just

Formula:

Please Login or Register  to view this content.

always use

Formula:

Please Login or Register  to view this content.

Note 'Raw_Data' is the VBA sheet code name not the tab name. You should always use code named rather than the string text of the tab names which are too easily changed and in which case would cause your macro to fall over. Similarly always pass the sheet code name to a procedure rather than the sheet name string.

What do you mean by the Summary Sheet? It's copying from the Attendance Raw Data sheet column I.

And when I step through it works as I'd expect. I don't see the stuff you show in column D in your picture. Have you checked the file you uploaded to the forum by downloading it, rather than using one which may be after that?

This is very confusing

I can step through and also F5 to run the macro to its conclusion in the VBE and get the results expected.

I also put a break on the first line of the macro that is actioned by both the buttons and clicked each one in turn and stopped the macro when they both came to the break.

Finally I removed the break and ran the macro from both buttons neither of which left me with what you show in the picture and produced what I would expect to see.

I'm therefore struggling to understand the cause.