I think Sue is using VBA, so the "Option1_Click() handles Option1.click"
syntax does not apply.
On the other hand, if Sue were not using VBA, you would not create 44 option
buttons. You'd create one control array, and make most of the problem just
However, you are right, you could use VBA UserForm Click events and a
module-level variable. FWIW, I this is how I chose between these two ways to
1. A module-level variable is a more complex thing than a local variable.
it is more likely to be hit with unwanted side effects, and is harder to
maintain. All other things being equal (and they rarely are!), then I'd
choose a solution with a local variable rather than a module-level variable.
I am not saying module-level variables are evil. Indeed, we could not live
without them. But simple is good.
2. I'd never use an Integer as a flag like this. The chance of getting those
flags wrong is extremely high. What happens when Sue decides, in 6 months'
time, to insert a new option button between current number 27 and 28? You
somehow have to remember what each number meant. Depending on the nature of
the change, you may need to re-number all the items from 28 to 44. If you
need a flag, use an Enum.
3. One way or another, Sue is going to end up with 44 somethings. 44 items
in an If / Else If structure, or 44 event handlers, or 44 something elses.
Any maintenance is going to require changing one or more of those 44 things.
I'd rather have *one* place in which they need to be changed. With an event
handler, a flag and then a separate routine to respond to the button click,
you have three things that need to be updated, or at least checked, each
time you make a change. That massively increases the chance of error, and
makes maintenance a much bigger job than it needs to be.