Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to Control Data Entry in Excel Spreadsheet

Posted on 2011-04-20
4
Medium Priority
?
334 Views
Last Modified: 2012-05-11
Hi,

I would like to know if it's possible to prevent data entry clerks from cutting and pasting values from one cell into another and from dragging values down into multiple cells? For the attached spreadsheet, I want data entry clerks to enter every value in every cell from columns J-X. In other words, they should not be allowed to cut and paste or drag values in those columns.

Is this possible to control? If so, please teach me how.

carlynne


ERA-Temp-2011-FINAL.xlsm
0
Comment
Question by:Carlynne
  • 2
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35430827
Try this in the worksheet code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("J:X")) Is Nothing Then
      disableCopyDrag
   Else
      enableCopyDrag
   End If
End Sub

Open in new window


and in a normal module:
Sub disableCopyDrag()
   With Application
      .CellDragAndDrop = False
      .CutCopyMode = False
   End With
End Sub
Sub enableCopyDrag()
   With Application
      .CellDragAndDrop = True
      .CutCopyMode = False
   End With
End Sub

Open in new window


0
 

Author Comment

by:Carlynne
ID: 35430851
Hi,

Thanks for your response. Sorry, I am not a programmer. I already have some coding in my worksheet that someone else helped me with, and I have no idea where exactly to put that code you provided.

Can you give me a hint?

thanks,
carlynne
0
 

Author Comment

by:Carlynne
ID: 35430879
Actually, I just played around with it and it seems to be working. So BIG THANK YOU!

Carlynne
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35430901
Here's the file with the code added.
Basically the first part goes below the code you already had in the sheet's code module and the second bit goes in module1.
I've also just added some code to the Workbook_Activate event in case the file is opened with the selection already in that range and to the Deactivate event to reset the ability to fill down in cells:
Private Sub Workbook_Activate()
   With ThisWorkbook.ActiveSheet
      If .Name = "ERA_Template" Then
         If Not Intersect(ActiveCell, .Range("J:X")) Is Nothing Then disableCopyDrag
      End If
   End With
End Sub

Private Sub Workbook_Deactivate()
   enableCopyDrag
End Sub

Open in new window

ERA-Temp-2011-FINAL.xlsm
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question