|
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 <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 ---- |
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... |
| All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com