Activate Range, Allow Edit, Continue

Question

As a secondary method to my question here: http://stackoverflow.com/questions/511488/show-cell-range-on-userform-then-update

I would like to have a macro that selects a range, and activates it, but allows the user to edit cells in that range. Then, it needs to have a button that moves onto the next range. Is there a way to keep a form visible, but allow the user access to a range?

And if that's possible, can I temporarily lock all other cells than those in the range while the updating is taking place, to avoid errors?

Answer 1

You can show a form in 'modeless' state, which means that the user is not locked to that form:

Dim interactionForm As New DemoForm

interactionForm.Show vbModeless

You can also programatically lock and unlock certain cell ranges depending on what you want to do:

Dim bigRange As Range
Set bigRange = Sheet1.Range("SomeRange")

Sheet1.Cells.Locked = True
bigRange.Locked = False
Sheet1.Protect "password1" ' add other options here

Using this you can lock all cells on a sheet, but unlock the range you want them to edit. You could even colour that range Green or some other colour to enforce what you want them to do.

Just don't forget to unlock everything when you're done!

Sheet1.Unprotect "password1"
Sheet1.Cells.Locked = False