VBA lock row depending on content cell and amendments to created macro

Eline · Sep 30, 2013

I'm very new to writing Macros and have tried to find the answer online, but I’m stuck! So really hope someone can help me?
On a regular basis, data is extracted into an excel spreadsheet. It will contain a set number of columns (A-L), but the amount of rows will differ each time it is run. I’m creating a macro which needs to be run for each newly extracted spreadsheet to make it user friendly (therefore all steps required need to be contained within this macro!). All these steps need to end in the last row that is populated (cells in column A-E, the others (F-L) will need to be manually populated by the users after the macro is run – these cells need to be blank after the macro is run before cells are populated)
I’ve written a macro (see below), but this is not quite working the way it should, and am also missing a couple of steps as I’m really unsure of how to write them? Here are the 4 queries I’m struggling with:
1) The spreadsheet needs to have a drop down list in column F (options that can be selected are "Compliant" or "Not Compliant"). I'd like the following to happen when each option is selected:

If “Compliant” is selected in column F (starting in F2, ending in the last populated cell in column (A-E));
* Columns G and H need to be populated (so a note saying "Please populate" will need to be added to the cells (ideally in red so it stands out)). So if cell in F2 is "Compliant", G2 and H2 need to read "Please populate". These cells need to be open for editing. (Formula in the cell needs to be hidden from user)
* Columns I, J, K, L do not need to be populated (so a note saying N/A need to be added to the cells, and the cells need to be locked). So if cell in F2 is "Not Compliant", I2, J2, K2, L2 need to read "N/A", and be locked for editing (cell cannot be populated, or pasted into). (Formula in the cell needs to be hidden from user)

If “Not Compliant” is selected;
* The above needs to be reversed
2) Column E of the extract will contain one of 3 words; “Heading”, “Requirement” or “Information”. If the cell in column E (starting in E2, ending in the last populated cell in column E) contains “Heading”, I need it to lock the entire row for this cell – the row cannot be edited, populated or pasted into. I also do not want the drop-down lists to appear in this row.
3) If someone tries to populate the locked cells (from question 1 and 2), I need a pop up message box to appear, saying “This section does not need to be populated” and an OK button to close the message box.
4) How would I best save this Macro so that I can run it on the newly extracted data each time it is run (it being a new spreadsheet each time)? (This Workbook, New Workbook or Personal Macro Workbook? And perhaps create a macro button?)
Here’s the macro I’ve written so far. Would very much appreciate your help in amending the steps that aren’t working quite right and help on how to write the missing steps. Thank you very much in advance! Regards, Eline

Sub DOORS_Template()
'
' DOORS_Template Macro
' This macro protects new Doors extracted data

'Freezes panes below header
Range("A2").Select
ActiveWindow.FreezePanes = True

'Unlocks sheet, then locks Header row, and columns A, B, C, D, E
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("1:1,A:A,B:B,C:C,D:D,E:E").Select
Range("E1").Activate
Selection.Locked = True
Selection.FormulaHidden = False

'Adds in Validation Lists for column F "Compliance Status" and K "Impact Assessment"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "Compliant"
Range("AA3").Select
ActiveCell.FormulaR1C1 = "Not Compliant"
Range("AA5").Select
ActiveCell.FormulaR1C1 = "High"
Range("AA6").Select
ActiveCell.FormulaR1C1 = "Medium"
Range("AA7").Select
ActiveCell.FormulaR1C1 = "Low"
Range("AA8").Select

'"Compliance Status" validation list column F
Range(Range("F2"), Range("F2").End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$AA$2:$AA$3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

'"Impact Assessment" validation list column K
Range(Range("K2"), Range("K2").End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$AA$5:$AA$7"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

'Adds in "Compliant" rule; If compliant in column F, columns G,H need to be populated, and I,J,K,L are N/A.
Dim LR As Long
LR = Range("F" & Rows.Count).End(xlDown).Row

Range("G2:H" & LR).FormulaR1C1 = "=IF(RC6=""Compliant"", ""Please Populate"", """")"
With Range("G2:H" & LR).Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$F2=""Compliant"""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please leave blank"
.ShowInput = False
.ShowError = True
End With

'Adds in "Not Compliant" rule; If not compliant in column F, columns I,J,K,L need to be populated, and G,H are N/A

Range("I2:L" & LR).FormulaR1C1 = "=IF(RC6=""Not Compliant"", ""Please Populate"", """")"
With Range("I2:L" & LR).Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$F2=""Not Compliant"""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please leave blank"
.ShowInput = False
.ShowError = True
End With
'Pop up message box when locked cell is being population (???)
'Lock row if cell in column E = "Heading" (???)
'Steps to end in last populated cell column A-E (???)

'Protecting sheet
Cells.Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingHyperlinks:=True, AllowFiltering:=True
End Sub

sijpie · Oct 25, 2013

OK,
there are a few things which ai am not fully sure of, in your sheet.

One is the validation that you set if Fx contains Compliant or Not Compliant:

Code:

                                .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _                                        Operator:=xlBetween, Formula1:="=$F2=""Compliant"""

I don't understand what this formula =F2="Compliant" is supposed to do. So you may need to fix that in the code below.

To get this code to work, you need to open the module for the sheet. This is a special module where you can trap (some of) the events that happen on the sheet. In our case we want to trap the Change event, which is fired any time a change is made to the sheet.
So let's open this module: Go to the Excel worksheet where this is all happening, right click on the tab of the sheet and select 'View Code'
The macro editor opens the sheet's module. (You should not use these modules for general macros.)
to the top left above the panel that opend is a drop down box showing '(General)'. Click on it and you see you can also select 'Worksheet'. Do this and you will notice that a skeleton
Private Sub Worksheet_SelectionChange() is written to the module. Ignore (or delete) this. In the right hand selection box you see a large amount of events that you can trap. One of them is the 'Change' event.
Select it, and the editor will enter the skeleton for this event to the module

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
end sub

So this macro will run for any change made to the sheet. Target, which is passed to the macro by Excel, is the cell(s) that has been changed.

We will need to check if it is in column F, if not immediately quite the macro.
If it is in Column F (we use the intersect function for this: does the Target intersect the column F) then we need to take account of the possibility that the user changed more cells at the same time, by using copy paste, or by dragging values down. So we will loop through each cell in target and act accordingly.
If the changed cell (rC) is in column F then we start applying the rules according to the value of the cell rC.
We use Select Case to do this efficiently.
The first case is "Compliant"
so if rC value is "Compliant" then
we clear Gx and Hx in the same row (as the changed cell) and set the validation for these two cells
next we enter "N/A" in the cells Ix:Lx.

If the case is "Not Complian" then we do the reverse
if the case is "" (which is vbNullString, meaning an empty string) then we clear the contents of Gx:Lx

OK, so paste the following into the module, overwriting the skeleton macros:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rC As Range If Not Intersect(Target, Columns("F")) Is Nothing Then ' change has been made in column F ' take account of multiple cell change, for instance copy down or so For Each rC In Target.Cells If rC.Column = 6 Then ' only for cells in F Select Case rC.Value Case "Compliant" With rC.Offset(0, 1).Resize(1, 2) ' Gx:Hx .ClearContents With .Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$F2=""Compliant""" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Please leave blank" .ShowInput = False .ShowError = True End With End With With rC.Offset(0, 3).Resize(1, 4) ' Ix:Lx .Value = "N/A" End With Case "Not Compliant" With rC.Offset(0, 1).Resize(1, 2) ' Gx:Hx .Value = "N/A" End With With rC.Offset(0, 3).Resize(1, 4) ' Ix:Lx .ClearContents With .Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=$F2=""Compliant""" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "Please leave blank" .ShowInput = False .ShowError = True End With End With Case vbNullString ' empty With rC.Offset(0, 1).Resize(1, 6) ' Gx:Lx .ClearContents End With End Select End If Next rC End If End Sub 

Now test it by changing something on the sheet, and then in column F. In the firt case nothing special should happen, in the second case them macro is run and you should see some results in G:L.

If you want to see what is happening, go back to the editor, and in the grey border to the left of 'If Not Intersect(' click your mouse. The line will turn deep red. You have set a bookmark.
Go back to the sheet, make a change and bang the editor comes up at this line, now with yellow highlighting. Press the F8 key to step through the code one line at a time.
To remove the bookmark, click in the border again. This is a very useful technique to debug code if you don't understand what is happening. See also my quick guide (link below) for more tips.

Eline · Oct 25, 2013

WOW, thank you so so much for explaining everything so clearly and in such detail (which is exactly what I need!)!
I absolutely cannot thank you enough for taking the time out to help me with this, I really appreciate it, thank you!

I will have a go at this over the weekend and let you know how I got on.

Thanks again!

Eline · Oct 29, 2013

I see what you mean about the validation that is set if Fx contains Compliant or Not Compliant, it’s falling over at that point…

What I did is the following:

I’m putting the below formula in G2:H2:

Code:

"=IF(RC6=""Compliant"","""",IF(RC6="""","""",""N/A""))

The Data Validation that goes on those same two cells to only allow someone to type in those cells would be:

Code:

Allow: Custom
Source: =$F2="Compliant"
Ignore Blanks: False

The formula I’m putting into I2:L2 is:

Code:

"=IF(RC6=""Not Compliant"","""",IF(RC6="""","""",""N/A""))

The Data Validation that goes on those same cells to only allow someone to type in those cells would be:

Code:

Allow: Custom
Source: =$F2="Not Compliant"
Ignore Blanks: False

I turned on the macro recorder and let it record me entering all that in G2 and I2, then edited the recorded code to use the variable I created above to get the code I’m using.

How do I fix this step to stop it from falling over at that point?

Eline · Oct 30, 2013

Are you able to help me with this please Sijpie?

Thank you

sijpie · Oct 30, 2013

can you post the spreadsheet somewhere, because I really do not understand what you are trying to do with your funny formulas.

Eline · Oct 30, 2013

O dear, am I that rubbish...

Unfortunately the forum rules won't let me attach anything to the thread, do you have any suggestions?

Thank you so much for your time, really appreciate it Sijpie

sijpie · Oct 30, 2013

Just post it on dropbox, google Drive or similar, and put The link here

Eline · Oct 31, 2013

Hope this works:
https://www.dropbox.com/s/dfpjbfk6o9b72pm/DOORS%20Template.xls

sijpie · Oct 31, 2013

This data validation you are trying to create. Say that someone selected "Compliant"in F5. Now what should his choices be in G5? See, I do not for the life of me understand what you are trying to achieve by setting the validation of G5 to '=F5="Compliant" '. And neither does Excel understand it. What is it supposed to do?

Please explain what choice someone should have. Because I am assuming that with validation you mean the person gets a pick list, just us in column F, or an error message when something wrong has been entered. But what is right and what is wrong?

Eline · Nov 1, 2013

Sorry for the confusion…
What I’m trying to achieve in these steps is the following:
If “Compliant” is selected in column F, the cells in columns G & H are left blank for populating by the user (free typing). (If nothing is selected it is left blank too) If something else is selected (so “Not Compliant” or “N/A”), the cell is populated with “N/A”. The Validation I’ve added in isn’t a drop down list, but a Custom Validation which locks the “N/A” cells, as I don’t want the user to be able to edit these cells (it therefore gives an error message of “Please leave blank” when the user tries to populate this cell). I agree this validation looks odd, but it seems to work when I run the macro? Is there perhaps a better way of writing the macro for locking these cells? 

I hope this explains what I am trying to achieve and hope you will be able to help me with the change macro?

Really do appreciate your time and effort on this, thank you

VBA lock row depending on content cell and amendments to created macro

Eline

New Member

sijpie

Well-known Member

Excel Facts

Eline

New Member

Eline

New Member

Eline

New Member

sijpie

Well-known Member

Eline

New Member

sijpie

Well-known Member

Eline

New Member

sijpie

Well-known Member

Eline

New Member

Similar threads

Forum statistics

Share this page

VBA lock row depending on content cell and amendments to created macro

New Member

Well-known Member

Excel Facts

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock