disable sheet while processing

  • Thread starter Thread starter cate
  • Start date Start date
C

cate

I have a sort sub() that takes some time to complete. Running it
requires that I remove protection for the sheet, unhide rows and blah
blah.

Is there anyway to keep a user's hands off the workbook while this
runs? My impression is that the mouse is on one leg while all this
happens, but I'm not sure just what it can get away with. Que up
clicks and make a mess? This sheet is so busy I'd keep it all locked
up if I could.

Thank you.
 
You can use
application.screenupdating=false
code
application.sceenupdating=true
OR, it may NOT be necessary to unprotect>unhide rows>select,etc
show your code.
 
You can use
application.screenupdating=false
code
application.sceenupdating=true
OR, it may NOT be necessary to unprotect>unhide rows>select,etc
show your code.
Click to expand...

Set mySheet = Application.ThisWorkbook.Worksheets("ChiralV")
mySheet.Unprotect
mySheet.Range("F:J").EntireColumn.Hidden = False

With mySheet
Set rg = .Range(.Range("n_rfpx_datarowfirst"), .Range
("n_rfpx_datarowlast"))
End With

If country_direction = 1 Then
With rg
.Sort Key1:=.Columns(8), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Else
With rg
.Sort Key1:=.Columns(9), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End If

mySheet.Range("F:J").EntireColumn.Hidden = True
mySheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True
 
About the only thing a user could do while the code is running that would
cause a problem would be to press Alt + Ctrl + Delete, or Ctrl + Break to
stop the code. I don't believe that clicking the mouse or pressing keyboard
keys, other than those mentioned would interfere once the code is running.
Although the sheet is unprotected, the macro has control until it completes.
Since you protect the sheet again before exiting the macro, you should not
have a problem. But if you have idiots in the area who are malicious in
nature, no amount of protection will keep them from sabotaging your work.



You can use
application.screenupdating=false
code
application.sceenupdating=true
OR, it may NOT be necessary to unprotect>unhide rows>select,etc
show your code.
Click to expand...

Set mySheet = Application.ThisWorkbook.Worksheets("ChiralV")
mySheet.Unprotect
mySheet.Range("F:J").EntireColumn.Hidden = False

With mySheet
Set rg = .Range(.Range("n_rfpx_datarowfirst"), .Range
("n_rfpx_datarowlast"))
End With

If country_direction = 1 Then
With rg
.Sort Key1:=.Columns(8), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Else
With rg
.Sort Key1:=.Columns(9), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End If

mySheet.Range("F:J").EntireColumn.Hidden = True
mySheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True
 
Back
Top