Stop Cell Dragging 2

[simoncpage]

hi all,

Does anyone know of a way of stopping someone from dragging a cell down on a sheet? I want to preserve a range naming order but not limit the page so that it is protected?

Anny help appreciated!

Thanks

Simon

 

[SkipVought]

Hi,

When you say Draging, you do mean MOVE?

Only ways I know are sheet protection OR using an IS/WAS technique which employs a phantom very hidden sheet that has the WAS sheet state. So if a Move occurs in a specified range, then you retore the WAS condition.

BTW, this uses the Worksheet_Change event. But you must prevent recursive calls to the restore procedure.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

sorry, hit the wrong button...

You can disable event processing to prevent recursive calls by using...

Application.EnableEvents = False
RestoreWAS
Application.EnableEvents = True

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

or.....
Tools>Options>Edit
untick allow cell drag and drop

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Geoff,

Goes to show how [b[broad[/b] the capabilities of Excel are and how narrow my knowledge is in ALOT of Excel functionality!

==>* ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

heh heh - one of those things I accidently unticked for a while and had to hunt for it to turn it back on....that's the only reason I know about it.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[simoncpage]

WOW it does go to show theres me looking into some snazzy whorksheet change code and all I need to do is that lol

Cheers for the help Skip! I always go by the thought process that Excel isn't as functional as I can make and I think in most cases that helps!

Simon

 

[simoncpage]

Although this has thrown up another problem as I only want the stop cell drag to be present when using a specific model rather than all excel sheets open,

So just having:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CellDragAndDrop = False
End Sub

in the workbook activate and opposite for the Deactivate event should work yes? But it doesn't seem to? any ideas?

 

[SkipVought]

simon,

Your approch works on worksheet_Activate/Dactivate...

Private Sub Worksheet_Activate()
    Application.CellDragAndDrop = False
End Sub

Private Sub Worksheet_Deactivate()
    Application.CellDragAndDrop = True
End Sub

Try get away from WINDOW referencing and into Workbook/Worksheet/Range refreencing.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

simon,

Your approch works on worksheet_Activate/Dactivate...

Private Sub Worksheet_Activate()
    Application.CellDragAndDrop = False
End Sub

Private Sub Worksheet_Deactivate()
    Application.CellDragAndDrop = True
End Sub

Try get away from WINDOW referencing and into Workbook/Worksheet/Range refreencing.

BTW -- use the WORKSHEET OBJECT and NOT a module when coding this!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[simoncpage]

Yeah I of course have it as a worksheet object sub - I dont see why taking the "ByVal Wn As Window" out would make any differnce anyhow? WHich it doesn't still not working. Its probably my cell stop drag coding I had in thats not making it work as that code on another blank book is fine.

thx