We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to Control Data Entry in Excel Spreadsheet

Carlynne
Carlynne asked
on
Medium Priority
385 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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

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

Carlynne
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.