How do I restrict data movement (cut & paste) to only one column?

Hi,
My workbook is designed for shared use. One crucial function is to allow
the users to move data (text) up and down columns associated with that
specific data. How do I protect the users from mistakenly moving data into
a wrong column. I have tried the validation function, but cut/moved data
carries its own validation properties and is therefore not considered
invalid when pasted into any cell.

Any guidance would be much appreciated.

Thanks

Regards
Kevin Nurse

One way to play with might be to restrict the scroll area in each sheet to a
certain columnar range.

For example, suppose we want to restrict scroll in Sheet1 to B2:B50, and in
Sheet2 to C2:C50. (Try this on a spare copy)

Copy > Paste the macro below in the "ThisWorkbook"* module:

Private Sub Workbook_Open()
Worksheets("Sheet1").ScrollArea = "b2:b50"
Worksheets("Sheet2").ScrollArea = "c2:c50"
End Sub
---
*One way to go to the "ThisWorkbook" module:
Right-click on the Excel icon just to the left
of "File" on the menu > Choose "View Code"
This will bring you direct into the "ThisWorkbook" module

Clear the defaults appearing in the whitespace on the right
Copy > paste the code above there
Save the book and exit

Re-open the book
Go to Sheet1 and Sheet2,
you'll find that the restrictions are in-force
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Kev Nurse" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> My workbook is designed for shared use. One crucial function is to allow
> the users to move data (text) up and down columns associated with that
> specific data. How do I protect the users from mistakenly moving data
into
> a wrong column. I have tried the validation function, but cut/moved data
> carries its own validation properties and is therefore not considered
> invalid when pasted into any cell.
>
> Any guidance would be much appreciated.
>
> Thanks
>
> Regards
> Kevin Nurse
>
>

Max <[email protected]> wrote in message
news:#[email protected]...
> One way to play with might be to restrict the scroll area in each sheet to
a
> certain columnar range.

> > Hi,
> > My workbook is designed for shared use. One crucial function is to
allow
> > the users to move data (text) up and down columns associated with that
> > specific data. How do I protect the users from mistakenly moving data
> > into a wrong column?

Max, thanks for that suggestion. Its certainly a step in the right
direction. However, to add to the problem, I have multiple columns on the
same worksheet and every one of them must have this function. Can the
scroll area code be applied to the column of the selected cell? I would be
prepared to write out the code for up to 15 columns. Thanks for any help.

Rgds
Kev Nurse

"Kev Nurse" <[email protected]> wrote
> Max, thanks for that suggestion. Its certainly a step in the right
> direction. However, to add to the problem, I have multiple columns on the
> same worksheet and every one of them must have this function. Can the
> scroll area code be applied to the column of the selected cell? I would
be
> prepared to write out the code for up to 15 columns. Thanks for any help.

The closest fit I found from googling the Excel newsgroup archives
is this previous post by Don Guilett ..

By Don Guillett Oct 3 2003, 8:44 am
microsoft.public.excel.programming

Try this assigned to a button or shape

--- begin vba ---
Sub Multiscroll()

With ActiveSheet
Select Case InputBox("select area 1,2,or 3 ONLY")
Case 1
..ScrollArea = "a1:a10"
Case 2
..ScrollArea = "b50:c100"
Case 3
..ScrollArea = "d25:d100"
Case Else
..ScrollArea = "a1:a1"
End Select
End With

End Sub
--- end vba ---

"R. Todd Miller" <[email protected]> wrote
> I'm using Excel 97. Is it possible to use Activesheet.Scrollarea on
> several non-contiguous ranges? For example, restrict the user to
> A1:A10, B50:C100, and D25:D100?

> Note: Unfortunately I can't protect the worksheet and use
> Worksheets("Sheet1").EnableSelection = xlUnlockedCells
--

To implement:
Press Alt + F11 to go to VBE
In VBE, Click Insert > Module
Paste Don's Sub Multiscroll() there
(everything within the dotted lines)

Press Alt+Q to go back to Excel
In say, Sheet1, draw a rectangle (say) on the sheet
Right-click on the rectangle > Assign macro
Select "Multiscroll" > OK

Click on the rectangle to fire the sub
It'll bring up an inputbox for the user to input 1, 2 or 3
Inputting "1" > OK will restrict the scroll
to the area defined under Case 1 (.ScrollArea = "a1:a10")
And so on ..

You might be able to adapt Don's sub to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max <[email protected]> wrote in message
news:[email protected]...
> "Kev Nurse" <[email protected]> wrote
> > Max, thanks for that suggestion. Its certainly a step in the right
> > direction. However, to add to the problem, I have multiple columns on
the
> > same worksheet and every one of them must have this function. Can the
> > scroll area code be applied to the column of the selected cell? I would
> be
> > prepared to write out the code for up to 15 columns. Thanks for any
help.
>
> The closest fit I found from googling the Excel newsgroup archives
> is this previous post by Don Guilett ..
>
> By Don Guillett Oct 3 2003, 8:44 am
> microsoft.public.excel.programming
>
> Try this assigned to a button or shape
>
> --- begin vba ---
> Sub Multiscroll()
>
> With ActiveSheet
> Select Case InputBox("select area 1,2,or 3 ONLY")
> Case 1
> .ScrollArea = "a1:a10"
> Case 2
> .ScrollArea = "b50:c100"
> Case 3
> .ScrollArea = "d25:d100"
> Case Else
> .ScrollArea = "a1:a1"
> End Select
> End With
>
> End Sub
> --- end vba ---
>
> "R. Todd Miller" <[email protected]> wrote
> > I'm using Excel 97. Is it possible to use Activesheet.Scrollarea on
> > several non-contiguous ranges? For example, restrict the user to
> > A1:A10, B50:C100, and D25:D100?
>
> > Note: Unfortunately I can't protect the worksheet and use
> > Worksheets("Sheet1").EnableSelection = xlUnlockedCells
> --
>
> To implement:
> Press Alt + F11 to go to VBE
> In VBE, Click Insert > Module
> Paste Don's Sub Multiscroll() there
> (everything within the dotted lines)
>
> Press Alt+Q to go back to Excel
> In say, Sheet1, draw a rectangle (say) on the sheet
> Right-click on the rectangle > Assign macro
> Select "Multiscroll" > OK
>
> Click on the rectangle to fire the sub
> It'll bring up an inputbox for the user to input 1, 2 or 3
> Inputting "1" > OK will restrict the scroll
> to the area defined under Case 1 (.ScrollArea = "a1:a10")
> And so on ..
>
> You might be able to adapt Don's sub to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

Excellent, Max. I've tried it and it works. I also took the hint about
searching Google's newsgroups. Many thanks.
Regards
Kev Nurse

"Kev Nurse" <[email protected]> wrote
....
> Excellent, Max. I've tried it and it works.
> I also took the hint about searching Google's newsgroups.
> Many thanks.

Glad to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

> is this previous post by Don Guilett ..

Apologies to Don for the typo in the name,

line should read as:
> is this previous post by Don Guillett ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Not a biggie...

--
Don Guillett
SalesAid Software
[email protected]
"Max" <[email protected]> wrote in message
news:[email protected]...
> > is this previous post by Don Guilett ..
>
> Apologies to Don for the typo in the name,
>
> line should read as:
> > is this previous post by Don Guillett ..
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

That's nice of you, Don, thanks !
(but I'll still strive to be more careful, especially with people's names
<g>)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Don Guillett" <[email protected]> wrote in message
news:[email protected]...
> Not a biggie...