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

[Eline]

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?</SPAN>
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)</SPAN>
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:</SPAN>
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)</SPAN>

If “Not Compliant” is selected;
* The above needs to be reversed</SPAN>
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.</SPAN>
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.</SPAN>
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?)</SPAN>
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</SPAN>

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]

You are confusing two things and trying to write them in the same macro. That won't work:

1. You are asking for a macro to set up the new sheet.
2. Then the import of the data occurs in columns A-E (?)
3. Then the selections in column F occur manually, which should result in modifying contents and locked status of cells in G:L
4. Lastly the user fills out the requested info.

So you need

1. one macro to set up the sheet, the selection ranges and all that (or have this ready in a template book)
2. possibly a macro to help with the data import, but
3. at least a macro to be run after the data import to lock or unlock rows etc (depending on values in E) and , fill out column F &K to the correct amount of rows.
4. then an onChange macro that will react to any changes made in column F to lock/unlock cells in G:L

Please confirm that the data imported is only in columns A:E.

Personally I would do this as follows:

• Have a template workbook with the headings and the validation ranges already filled, and with the macros in place.
• On opening a macro runs asking you to save as, and next helps with the data import and the after import activities.
• Once that is done the worksheet module checks for any changes in column F and acts accordingly.

 

[Eline]

Thank you, I've tried to follow your advise, but am struggling with a couple of bits, so hoping someone can help me?

I’ve attached the VBA which I’m working from.
The template workbook freezes panes, has a validation list in columns F and K and contains conditional formatting in columns G-L.

On opening the template, a macro runs to save a copy. Data is then input into this copy (copy and paste) and a separate macro then needs to be run to format the following:
1 – Unlocks sheet, then locks header row and columns A-E
2 – If cell in column E = “Heading”, validation list in that row needs to be deleted and row needs to be locked for editing
3 – Adds in custom validation in columns G-L dependant on what is selected from validation list in column F (Compliant, Not Compliant)
4 – Protects sheet to ensure selected rows and columns are locked

I’ve got the following issues:
• Step 2 does not work in this macro, but does when I run it on its own?
• Validation list in column K disappears, but I need this back IF “Not Compliant” is selected in column F?
• Would like to hide the formulas in columns G-L, but when I do this by selecting in Format Cells/Protection, I then cannot amend or add to these cells. It wipes what is input in the cells if you click in it, how do I get round this?

Your help is so very much appreciated, thank you in advance!

Sub Formats_after_data_import()
'Freeze panes - already within template
'Validation list column F and K - already within Template
'Conditional Formats columns G-L; "N/A" grey background, "Please populate" red letters - already within template
'Save As macro seperate - run automatically when template workbook is opened
'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:E").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    
    
'Lock row if cell in column E = "Heading"
Dim count
For count = 1 To Range("E" & Rows.count).End(xlUp).Row
   If Range("E" & count) = "Header" Then
      Rows(count).Cells.Validation.Delete
      Rows(count).Cells.Locked = True
   End If
Next
       
              
'Adds in "Compliant" rule; If compliant in column F, columns G,H need to be populated, and I,J,K,L are N/A.
'Column G and H
Dim LR As Long
    LR = Range("F" & Rows.count).End(xlDown).Row
    
    Range("G2:H" & LR).FormulaR1C1 = "=IF(RC6=""Compliant"","""",IF(RC6="""","""",""N/A""))"
    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
'Column I, J, K and L
    Range("I2:L" & LR).FormulaR1C1 = "=IF(RC6=""Not Compliant"","""",IF(RC6="""","""",""N/A""))"
    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
         
'Protects sheet to ensure selected rows and columns are locked
    Cells.Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowFiltering:=True
  
End Sub

 

[sijpie]

You did not read my post properly i think: You can NOT do this in one macro. Unless all this needs to happen before the user makes any changes.

You say you do not want to disable validation in column K if E=Heading and F=Not Compliant, so just code for that with something like this::

Select Case True
   Case Range("E" & Count) = "Header" And Range("F" & Count) = "Not Compliant"
        ' remove validation from all but not from K
   Case Range("E" & Count) = "Header"
        ' remove validation from all 
   Case Else
        ' leave validation in place
end Select

But this only works if the selections are already like this. If you want to have the program react after the user selects Non Compliant in column F, so after the sheet has been set up you need to have a separate macro.

i am not sure what you mean with:

3 – Adds in custom validation in columns G-L dependant on what is selected from validation list in column F (Compliant, Not Compliant)

does the selected here mean that the user select or that it happened to be in the sheet during the setup? That is what is confusing me.


To solve the problems you have, I suggest you step through your code, that will be very useful in that you can see why your step 2 doesn't work and why you have to code it differently: at the moment you are coding column by column, whereas what you are trying to achieve is n a row by row basis, so when you are processing your second column you are overwriting the changes you made to therows during the processing of your first column.

If you want to affect things on a row, than process the table by row, if you want to affect things by column, then process by column.

Anyway, I won't spoil the fun: do the debugging and step through the code - detailed instructions can be found in my short guide to better VBA on this site, see link below.

 

[Eline]

Thank you for taking the time to help me with this, really appreciate it.</SPAN>

I’ve tried to follow your advice, by creating a template workbook with the validation lists already filled in (also freezes panes, and has conditional formatting).</SPAN>
I’ve also created the Save As macro, which works great (thank you for that suggestion!).</SPAN>

Now I need a macro, and you’re probably right in saying I’m asking it to do too much, that makes 4 things happen (as listed previously) after data has been imported into columns A-E. (Columns F-L will be populated manually by the user after the macro is run).</SPAN>

3 of these steps now work (yay!), but the one I’m struggling with is step 3;</SPAN>
Column F contains a validation list (which is in the Template Workbook, so not part of Macro) with a drop down list of ‘Compliant’ and ‘Not Compliant’. The macro puts a formula in the cells in columns G-L (If ‘Compliant’ is selected, G-H are left blank for manual input, and columns I-L are locked for editing and say N/A; If ‘Not Compliant’ is selected the same happens but the other way round). </SPAN>
However, this part of the macro is overwriting the existing validation list from the Template Workbook in column K…</SPAN>

Q: How do I get it to not overwrite the validation list in column K if ‘Not Compliant’ is selected? (If ‘Compliant’ is selected it will say ‘N/A’ so no need for validation list). </SPAN>
Or perhaps you have any other suggestions?</SPAN>

Just to clarify, ‘Compliant’ or ‘Not Compliant’ will be selected after the macro is run by the user. I don’t want the users to have to run any additional macros.</SPAN>

I hope I’ve explained it a little better what it is I’m after.</SPAN>

Hope you can help me?</SPAN>

 

[sijpie]

You need Bto have an on change macro in the sheet module that will check for any changes in column F and then set the values in the other columns. I will see if I have time on Monday, maybe Sunday to explain. The user won't need to run a macro, it will happen automatically.

 

[Eline]

Thank you, I'd really appreciate that, as I've not worked with on change macros before.

Thank you

 

[Eline]

Hi, have you had a chance to look at the above for me?

Thank you

 

[sijpie]

Sorry, i have forgotten, but away from PC till Friday.

 

[Eline]

No problem, hope to hear from you shortly