I have a shared worksheet full of textual data (names). I want the users to
be able to freely move the data around the sheet by simple pointer movement
or through cut and paste. However, deleting any data (even if immediate
re-entering elsewhere is intended) must not be allowed in any circumstance.

Is this possible? If so, how?

Thanks

Kevin Nurse

If I had to do this, I think I'd provide a macro that did exactly what I wanted
done and no more. I'd protect the worksheet and then ask the user to select the
from range and to range and do the copy in code (also unprotecting and then
reprotecting the worksheet).

But worksheet protection isn't very strong. It can be broken by anyone who can
find these newsgroups.

You could add more validity checks to this.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim RngF As Range
Dim RngT As Range
Dim myPwd As String

myPwd = "hi"

Set wks = Worksheets("sheet1")

Set RngF = Nothing
On Error Resume Next
Set RngF = Application.InputBox(prompt:="Pick your From Range", _
Type:=8).Areas(1)
On Error GoTo 0

If RngF Is Nothing Then
Exit Sub
End If

If RngF.Parent.Name < wks.Name Then
MsgBox "Has to be on to be on worksheet: " & wks.Name
Exit Sub
End If

Set RngT = Nothing
On Error Resume Next
Set RngT = Application.InputBox _
(prompt:="Pick the top left cell of your To Range", _
Type:=8).Cells(1)
On Error GoTo 0

If RngT Is Nothing Then
Exit Sub
End If

If RngT.Parent.Name < wks.Name Then
MsgBox "Has to be on to be on worksheet: " & wks.Name
Exit Sub
End If

wks.Unprotect Password:=myPwd
RngF.Cut _
Destination:=RngT
wks.Protect Password:=myPwd

End Sub


Kev Nurse wrote:

I have a shared worksheet full of textual data (names). I want the users to
be able to freely move the data around the sheet by simple pointer movement
or through cut and paste. However, deleting any data (even if immediate
re-entering elsewhere is intended) must not be allowed in any circumstance.

Is this possible? If so, how?

Thanks

Kevin Nurse

--

Dave Peterson