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 <atyahoo<dotcom
----
"Kev Nurse" wrote in message
...
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 wrote in message
...
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" 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" 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 <atyahoo<dotcom
----

Max wrote in message
...
"Kev Nurse" 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" 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 <atyahoo<dotcom
----



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" 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 <atyahoo<dotcom
----

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 <atyahoo<dotcom
----

Not a biggie...

--
Don Guillett
SalesAid Software

"Max" wrote in message
...
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 <atyahoo<dotcom
----

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 <atyahoo<dotcom
----
"Don Guillett" wrote in message
...
Not a biggie...