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

K

Kev Nurse

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
 
M

Max

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
 
K

Kev Nurse

Max said:
One way to play with might be to restrict the scroll area in each sheet to a
certain columnar range.
Click to expand...

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
 
M

Max

Kev Nurse said:
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.
Click to expand...

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 said:
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?
Click to expand...
Note: Unfortunately I can't protect the worksheet and use
Worksheets("Sheet1").EnableSelection = xlUnlockedCells
Click to expand...
--

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 ..
 
K

Kev Nurse

Max said:
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 ---



--

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 ..
Click to expand...

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

Max

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

Glad to hear that !
Thanks for the feedback ..
 
M

Max

is this previous post by Don Guilett ..

Apologies to Don for the typo in the name,

line should read as:
 
M

Max

That's nice of you, Don, thanks !
(but I'll still strive to be more careful, especially with people's names
<g>)
 
Top