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 · Nov 1, 2013

OK, you can download my version here:
https://www.dropbox.com/s/ijrjoe55a8rx2lc/DOORS%20Template.xls

I have totally disabled your macro, and everything works through a change macro on the sheet module.

I don't know how the import is done, but if the data for the import is copied into columns A:E then everything should work OK.

JJust check it manually first to see if it does do what you want.

Eline · Nov 8, 2013

Thank you Sijpie, your macro looks so much better than the one I had and seems a great idea to have it all set up as a change macro. I’ve had a go with it, but it’s not doing all the things I need it to do, so I’m not sure where it is going wrong?
Here’s what needs to happen:

Template spreadsheet need to ask for Save As option on opening – works perfect
Extracted data is manually imported by me into the template (in the Save As copy); this is a copy and paste into columns A-E only.
Row 1 (headings) and columns A-E then need to be locked for editing – doesn’t happen at the moment?
If the cell in column E says “Heading”, this entire row needs to be locked for editing – doesn’t happen at the moment?
The user then gets sent this spreadsheet and will select one of the 3 options in drop down list in column F: “Compliant”, “Not Compliant” or ”N/A” (the rules for these are as explained previously - works perfectly!).
Additionally, if “ Not Compliant” is selected, column K needs to display the drop down validation list of “High”, “Medium”, and “Low” – doesn’t happen at the moment?

Would you be able to advise on how to make these last couple of things work also?
Thank you so so much for all your help

Eline · Nov 8, 2013

Apologies, drop down in Column K also works.

yay!
Therefore the only two things that don't seem to work are the locking of columns A-E after copy & paste export and locking the row if cell in column E says "Heading"...

sijpie · Nov 11, 2013

I'll see when I have some time to look at it.

sijpie · Nov 15, 2013

Sorry it took a while.

Anyway, I forgot one little line in the onChange code :banghead to lock the sheet again...

Here it is right at the bottom:

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, import or so UnprotectSheet Me Application.ScreenUpdating = False For Each rC In Intersect(Target, Columns("F")).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 .Locked = False .HorizontalAlignment = xlGeneral End With With rC.Offset(0, 3).Resize(1, 5) ' Ix:Mx .Value = "N/A" .Locked = True .HorizontalAlignment = xlCenter End With Case "Not Compliant" With rC.Offset(0, 1).Resize(1, 2) ' Gx:Hx .Value = "N/A" .Locked = True .HorizontalAlignment = xlCenter End With With rC.Offset(0, 7) ' Mx .Value = "N/A" .Locked = True .HorizontalAlignment = xlCenter End With With rC.Offset(0, 3).Resize(1, 4) ' Ix:Lx .ClearContents .Locked = False .HorizontalAlignment = xlGeneral End With Case "N/A" With rC.Offset(0, 7) ' Mx .ClearContents .Locked = False .HorizontalAlignment = xlGeneral End With With rC.Offset(0, 1).Resize(1, 6) ' Gx:Lx .Value = "N/A" .Locked = True .HorizontalAlignment = xlCenter End With Case vbNullString ' empty With rC.Offset(0, 1).Resize(1, 7) ' Gx:Lx .ClearContents .Locked = False .HorizontalAlignment = xlGeneral End With End Select ' End If Next rC Application.ScreenUpdating = True ' <<<< End changes to column F ElseIf Not Intersect(Target, Columns("E")) Is Nothing Then ' >>>> change has been made in column E ' take account of multiple cell change, for instance copy down, import or so UnprotectSheet Me Application.ScreenUpdating = False For Each rC In Intersect(Target, Columns("E")).Cells 'Lock row if cell in column E = "Heading" If rC = "Heading" Then rC.EntireRow.Locked = True End If Next rC ProtectSheet Me End If End Sub

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

sijpie

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

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

sijpie

Well-known Member

sijpie

Well-known 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