PDA

View Full Version : Stop user interaction with VBA



BlueDNA
04-27-2006, 06:31 PM
Hi guys,

Im working on this macro that runs through a loop and checks the integrity of the cells in those records.

The problem that im having is, when the loop runs, i would like to stop the user from being able to click on the cells in the sheet that are being validated (since this is causing some problems and the macro to crash).

I do not want to protect the worksheet, since i would need to unprotect and protect the cells again everytime a cell value is changed/modified.

Locking the sheet would be the same as well?

At the moment, i have set the visibility of the workbook to false while this loops runs and back to true again when validation has completed.

Any advice would be greatly appreciated, as usual!

Cheers guys.

:hi:

XLGibbs
04-27-2006, 07:07 PM
There is worksheet property that you can set to allow no selection of cells


Sheets("Sheet1").EnableSelection = xlNoSelection

then turn it back on when the procedure is done


Sheets("Sheet1").EnableSelection =xlNoRestrictions

BlueDNA
04-27-2006, 07:51 PM
There is worksheet property that you can set to allow no selection of cells


Sheets("Sheet1").EnableSelection = xlNoSelection

then turn it back on when the procedure is done


Sheets("Sheet1").EnableSelection =xlNoRestrictions

Doesnt work without: UserIntefaceOnly

If this helps anyone else, heres what i used.

On
Sheet1.Protect UserInterfaceOnly:=True
Sheet1.EnableSelection = xlNone


Sheet1.Protect UserInterfaceOnly:=False
Sheet1.EnableSelection = xlNoRestrictions


Thanks for the help XLGibbs. :friends:
Appreciate it dearly.

Marcster
04-28-2006, 01:45 AM
If you want to block all keyboard, mouse and other apps to Excel (you can
still imput to dialog boxes) you could use this:
Application.Interactive = False

To revert back to normal:
Application.Interactive = True

Marcster.

BlueDNA
04-29-2006, 11:05 PM
If you want to block all keyboard, mouse and other apps to Excel (you can
still imput to dialog boxes) you could use this:
Application.Interactive = False

To revert back to normal:
Application.Interactive = True

Marcster.

Thanks Marcster,

Ill keep that in mind.

Appreciate it!