Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Protect cells that has data Validation

0

HI ,

how to protect cells that has data validation in it?

the user should not be able to copy paste to that cells and only use the available drop down.

also how to run vba on a password protected worksheet .

thanks in advance ! Happy new year 

Answer
Discuss

Discussion

Happy New Year Nigel!

I assume you're using List style data validation but please edit your question to attach a representative Excel file.
John_Ru (rep: 2867) Jan 1, '22 at 6:03 am
To keep it simple, have attached the same file that you worked on previously.
Password "123"
Nigel (rep: 6) Jan 1, '22 at 6:28 am
Add to Discussion

Answers

0
Selected Answer

Nigel.

List style data validation in Dashboard cells E4 and E5 will not allow users to paste values (other than those defined by the List) but the cells could be overwirtten if the entire cell C5 say is pasted. To prevent this, you can add an event macro behind Sheet1 (Dashboard") as follows, with comments and disabling line in bold:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Ignore if user is not picking Field cells...
If Intersect(Target, Range("E4:E5")) Is Nothing Then Exit Sub
'Otherwise, prevent paste
Application.CutCopyMode = False

End Sub
Now, if the user copies C5, choosing E4 or E5 will stop any pasting. 

In the revised file attached, sheet Jan has been reduced from 100,000 rows to 200  (to reduce size only). In the Dashboard sheet, cells C4:C5 and E4:E5 re unlocked as follows:

  1. Unlock using ribbon Review /Unprotect Sheet and provide password
  2. Use Ctrl_left click to select cells
  3. Right click/Format cells.../ Protection tab then uncheck "Locked" then Okay. Re-protect sheet using ribbon Review /Protect Sheet  (and uncheck "Locked cells")

Then the user can select only the mentioned cells in the sheet (shaded pale green) or tab between them. They can press the Search and Clear buttons.

In VBA, we can then provide the password to unlock the sheet so we can change cells (then re-lock it after the macro has run). In the main macro extract below, the lines in bold are added (including a line to state report results in B8):

Sub Data_Search()
' TeachExcel.com

Dim ws As Worksheet
Dim dashboard As Worksheet

.
.
.<< code unchanged>>
.
.

'Clear Dashboard
Call Clear_Data
'Unlock sheet
dashboard.Unprotect Password:="123"
.
.
.<< code unchanged>>
.
.
Next ws
'state report basis
dashboard.Range("B8").Value = "Search results " & _
Now & " for (" & _
FieldValue & "=" & searchValue & _
" OR " & FieldValue2 & "=" & searchValue2 & ")"

' reapply protection
dashboard.Protect Password:="123"

'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!

End Sub
Note that the .Unprotect method is applied to sheet AFTER the Clear_Data macro since that too needs the same lines (and also now clears the search values) as follows (additions in bold):
Sub Clear_Data()

'Dashboard sheet
Set dashboard = Sheets("Dashboard")
'clear search values and reoprt basis
dashboard.Range("C4:C5").ClearContents
dashboard.Range("B8").ClearContents

dashboard.Unprotect Password:="123"

'Data table information
dashboardDataColumnStart = 3 ' Column for the data on the dashboard
dashboardDataRowStart = 10

dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).ClearContents
dashboard.Protect Password:="123"


End Sub
That's it! 

Users can see your password in the code if they know VBA (but you could choose to protect the VBA Project and add extra code to lock/unlock).

Hope this is right for you.

Discuss

Discussion

That did work ! Brilliant. Thank you so much. 

What I meant about copy paste was, so in C4 if the search value updated is "111", this field can be copied and pasted in E4 as well (lets say by user mistake). How can I restrict this cell (E4:E5) only to click the listed data validation? 

Also I have one more question - let me know if I should raise a new one ?

When we clear the data by clicking the "clear button" it clears the data from row 10 and column 2 onwards. can this clear button clear values entered in "C4:"C5" as well?
Nigel (rep: 6) Jan 1, '22 at 9:39 am
Ah, now I see what you mean. See my revised Answer/ file.
John_Ru (rep: 2867) Jan 1, '22 at 10:40 am
Thanks John. 
It works as excpected thank you so much again. 
Nigel (rep: 6) Jan 1, '22 at 10:54 am
Great, Nigel. I juist got back and tweaked my answer to correct tne comments in the event macro and to correct the clear statements in Clear_Data (the "Or" in cell B4 was being erased too). See revised file (with same name)
John_Ru (rep: 2867) Jan 1, '22 at 11:55 am
thanks John. 
I just got one question/clarification (not an expert so dont know the feasibility), when the user clicks search, can the search field be there ("C4:C5") rather than populating it as a string in B8?
When clear data is clicked then all the data gets cleared like it does for all the search results (data row 10 and column 2)
Nigel (rep: 6) Jan 1, '22 at 12:10 pm
Nigel. That's possible but the Search macro calls Clear_Data so I'd rather not do it- better in my opinion to clear it once but keep fixed (in B8 stating what the columns mean). Otherwise someone could change C4:C5 values and you might think the report reflects the new values (even though the Search button has not been pressed). Now B8 says what the search is, even if you do change the values (so there's no doubt).

You could ask that in another question but I don't think it's worth pursuing.
John_Ru (rep: 2867) Jan 1, '22 at 12:19 pm
Yes make sense. 
No I just asked if at all it was possible thats about it 
Nigel (rep: 6) Jan 1, '22 at 12:25 pm
Nigel. I see you deleted your recent question about pdf printing and Excel export. Please note that my discussion comment on that was NOT a refusal to answer it (in time); it was simply a request for you not to address it to any individual. Make sense?
John_Ru (rep: 2867) Jan 1, '22 at 1:04 pm
Yes got it ! No issues 
Nigel (rep: 6) Jan 1, '22 at 1:15 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login