Bound Controls on UserForms lock Worksheet cells

[spilly39]

I hope this post is the last time I will trouble you with my problems.
But it's why I joined the grp really...
It may be an RTFM question. I hope not!
I HAVE searched but not in the right place.

I'm writing my first ever app with Userforms
using the 'Let's see if America exists' method that Columbus used

I discover (AFTER the code design stage!) that Userforms lock cells they are
bound to, so you can't do normal Excel things until the lock is removed.

Where is this locking documented?
- What event(s) cause the lock to be applied?
- what event(s) remove the lock?
- Are there rules of thumb for manipulating the RowSource & ControlSource
properties by code?
e.g.
Sometimes you might want to Get the property or value;
other times you might want to Set the property or value
So I'm looking for
"Do things in THIS sequence and change the properties in THESE steps"


That wd be most helpful to an aging Userforms newbie

spilly

 

[spilly39]

As soon as I send I think of something I've forgotten!
I do realise you can have unbound controls (=Setting the property)
- but I'd still like to understand when the locks are applied and released

For instance if a Control's Value is tied to .ControlSource how can that
control be easily re-used on the next row of data?
To change the data when it is bound to a fixed area, must I unload and
reload the form for each row before I can copy/paste the next row?


spilly

 

[spilly39]

I see nobody has responded to this...
Suggests I'm barking up the wrong tree

In fact I've just got a new theory on this "Locked" state

I think maybe that Forms hi-jacks the Excel keyboard (but not the VBE one),
which gives the appearance of the worksheets being "locked"

Can anyone confirm this? and..
Is there a way of "getting the Excel keyboard back" for a while in break
mode, and then "giving it back" to Forms???
(I doubt it. Reckon we have to modify data on spreadsheet by code execution
if Forms has the keyboard)

Comments?

 

[GS]

spilly39 formulated the question :

I see nobody has responded to this...
Suggests I'm barking up the wrong tree

In fact I've just got a new theory on this "Locked" state

I think maybe that Forms hi-jacks the Excel keyboard (but not the VBE one),
which gives the appearance of the worksheets being "locked"

Can anyone confirm this? and..
Is there a way of "getting the Excel keyboard back" for a while in break
mode, and then "giving it back" to Forms???
(I doubt it. Reckon we have to modify data on spreadsheet by code execution
if Forms has the keyboard)

Comments?

Have you tried displaying your form modeless?

Form1.Show vbModeless