Is it just me, or a Forms a complete PITA?

I've used them fairly extensively over the last 7 or so years,
building all sorts of things, and each project has been easy a
nightmare of de-bugging.

I'll give you an example: ControlSource. I thought this would be a
nifty way of populating my controls, and the 'problem' is that for
simple forms it actually works okay, so you set up your forms to use
ControlSources.
But when you have controls that are interdependant it all goes wrong.
Eg. In my current project I've got 8 checkboxes. The Checked items
appear as a list in a ComboBox, and the selected item of this combobox
dictates the contents of a list box.
This simple didn't work for me a lot of the time. Macros fire
repeatedly, or not at all, and Excel crashes.

It can be fine when the forms up and running, but then the 'up and
running' processes will conflict with the 'initialisation' processes
that you run to first populate the form when you first run the form.

My conclusions a
) You can use RowSource, but avoid ControlSource or you WILL regret
it.
) Controls have to be populated by macro instead (eg.
MyForm.ComboBox1.value = "Fred").
) If the RowSource is a calculated set of cells than this can cause
Excel to crash. If this happens then: eg. use the column to the right
of the calc cells and copy and pastespecial into this right hand
column, so the control isn't fed directly as a result of an
application calculation.
) If a Listbox isn't displaying the items in the rowsource that should
be feeding it then try 'application.calculate'
) Macros that fire when a control is changed have to be de-activated
when you're initialising the form (eg. you have some boolean called
"DoNOFireComboboxMacro", etc.). Then once you've set the control you
fire the macro (quite bizarre really!):

....initalisation code....
DoNOFireComboboxMacro = True
MyForm.ComboBox1.value = "Fred"
DoNOFireComboboxMacro = false ' so that it will fire if the user
subsequently changes ComboBox1
ComboBox1ChangeMacro ' now run it.
....initalisation code....

) Allow lots of time for debugging a project, which will be completely
unappreciated by the client.

All of the above have been concluded after many frustrating hours. I
haven't ever conferred with anyone else - perhaps I'm missing
something, and there's better ways. I haven't come across any of the
problems that I've had in text books.

So I'd be interested to hear of other form builder's experience.

Bon Chance,
Dz

I abandoned ControlSource and RowSource soon after I discovered these
properties. It's too complicated to set up, it's hard to account for
changing ranges, it ties forms too closely to worksheets, and it drastically
reduces flexibility.

I dump whatever worksheet data I need into arrays, pass these into the
forms, use code behind the forms to put the arrays into whatever controls
need the data, then pass the data back through other arrays. It sounds like
more work, but it makes it much easier and more robust.

Rather than setting up a separate Boolean, change Application.EnableEvents
to False. A UserForm's events do not respond to Application.EnableEvents,
but you can use it as your Boolean flag:

Private Sub MyButton_Click()
If Application.EnableEvents Then
' do the stuff
End If
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"dransfield" wrote in message
...
Is it just me, or a Forms a complete PITA?

I've used them fairly extensively over the last 7 or so years,
building all sorts of things, and each project has been easy a
nightmare of de-bugging.

I'll give you an example: ControlSource. I thought this would be a
nifty way of populating my controls, and the 'problem' is that for
simple forms it actually works okay, so you set up your forms to use
ControlSources.
But when you have controls that are interdependant it all goes wrong.
Eg. In my current project I've got 8 checkboxes. The Checked items
appear as a list in a ComboBox, and the selected item of this combobox
dictates the contents of a list box.
This simple didn't work for me a lot of the time. Macros fire
repeatedly, or not at all, and Excel crashes.

It can be fine when the forms up and running, but then the 'up and
running' processes will conflict with the 'initialisation' processes
that you run to first populate the form when you first run the form.

My conclusions a
) You can use RowSource, but avoid ControlSource or you WILL regret
it.
) Controls have to be populated by macro instead (eg.
MyForm.ComboBox1.value = "Fred").
) If the RowSource is a calculated set of cells than this can cause
Excel to crash. If this happens then: eg. use the column to the right
of the calc cells and copy and pastespecial into this right hand
column, so the control isn't fed directly as a result of an
application calculation.
) If a Listbox isn't displaying the items in the rowsource that should
be feeding it then try 'application.calculate'
) Macros that fire when a control is changed have to be de-activated
when you're initialising the form (eg. you have some boolean called
"DoNOFireComboboxMacro", etc.). Then once you've set the control you
fire the macro (quite bizarre really!):

...initalisation code....
DoNOFireComboboxMacro = True
MyForm.ComboBox1.value = "Fred"
DoNOFireComboboxMacro = false ' so that it will fire if the user
subsequently changes ComboBox1
ComboBox1ChangeMacro ' now run it.
...initalisation code....

) Allow lots of time for debugging a project, which will be completely
unappreciated by the client.

All of the above have been concluded after many frustrating hours. I
haven't ever conferred with anyone else - perhaps I'm missing
something, and there's better ways. I haven't come across any of the
problems that I've had in text books.

So I'd be interested to hear of other form builder's experience.

Bon Chance,
Dz

"Jon Peltier" wrote in message
...
I abandoned ControlSource and RowSource soon after I discovered these
properties. It's too complicated to set up, it's hard to account for
changing ranges, it ties forms too closely to worksheets, and it
drastically reduces flexibility.

Amen to that, binding is bad news. The only thing it buys is listbox
headings, and I can get around that to avoid binding it.

On 2 Apr, 14:50, "Jon Peltier" wrote:
I abandoned ControlSource and RowSource soon after I discovered these
properties. It's too complicated to set up, it's hard to account for
changing ranges, it ties forms too closely to worksheets, and it drastically
reduces flexibility.

I dump whatever worksheet data I need into arrays, pass these into the
forms, use code behind the forms to put the arrays into whatever controls
need the data, then pass the data back through other arrays. It sounds like
more work, but it makes it much easier and more robust.


Come to think of it, although I use RowSource, it is a struggle. For
my next project I'm going to do everything with arrays as you
describe.
Dz