Locked row based on value and Mandatory Fields

[sdennant]

Hi there,

I Wondered if this was even possible and i'm newish to VBA:

Notes: Column J is a data validation list and the below is from A:W

ISBN	Customer	Division	Title	Author	Pub date	RRP	Format	Important Do Not Touch	Promotion	Additional info	Important Do Not Touch	Chart Date	Mod Break	Storebase	Discount	Net Cost	End date	Existing deal in SAP end date	Existing deal in SAP terms	Ratecard		PO
9781408364093	Sainsburys	Hachette Children's Group	Where's the Poo? Sticker Activity Book	Orchard Books	18/03/2021	£4.99	Kids_PB		SS5	SS5 Activity		18/03/2021		138		£ 2.00				£ 350.00		4500125666
9781510202252	Tesco	Hachette Children's Group	The Ickabog	J.K. Rowling	10/11/2020	£20.00	Kids_HB		EPU	EPU		21/01/2021		311		£ 8.40		31/12/9999	8.4	£ 300.00		4500125671
9781408364833	Tesco	Hachette Children's Group	Love from Giraffes Can't Dance	Giles Andreae	07/01/2021	£6.99	Kids_		EPU	EPU		21/01/2021		311		£ 2.80		31/12/9999	2.8	£ 300.00		4500125673
9781510108752	Tesco	Hachette Children's Group	Horrid Henry: Holiday Horrors	Francesca Simon	04/02/2021	£6.99	Kids_PB		AISLE	Power Aisle		08/02/2021		234		£ 2.80		31/12/9999	2.8	£ 390.00		4500125671
9781444956368	Tesco	Hachette Children's Group	Rainy Day Stories	Enid Blyton	18/02/2021	£6.99	Kids_PB		SIDE STACK	Stack		08/02/2021		363		£ 2.80		31/12/9999	2.8	£ 800.00	TRUE	4500125671
9781444954265	Tesco	Hachette Children's Group	Magical Fairy Tales	Enid Blyton	03/09/2020	£6.99	Kids_PB		SIDE STACK	Stack		08/02/2021		363		£ 2.80		01/05/2021	2.8	£ 800.00	TRUE	4500125671
9781444956368		Hachette Children's Group	Rainy Day Stories	Enid Blyton	18/02/2021	£6.99	Kids_PB		AA			20/01/2021
9781444954265		Hachette Children's Group	Magical Fairy Tales	Enid Blyton	03/09/2020	£6.99	Kids_PB	Required			Required

What I'm trying to achieve is:

Essential – need to be included before we circulate

• Make column J “Promotion” mandatory ( In column I I've added this formula to use as the trigger for the msgbox =IF(J9>"", "", IF(A9>"","Required","")) but maybe there's an easier way)
• Make column M “Chart Date” mandatory ( In column L I've added this formula to use as the trigger for the msgbox =IF(M9>0, "", IF(A9>"","Required","")) but maybe there's an easier way )
• When the PO has been filled in the corresponding ratecard cell should be locked ( this i cant figure out because it chucks up all kinds of errors when protecting sheets it will either not run the other macros because some columns are hidden or it will lock the cel but not work all the time.)

These are also the things i want to implement

• Highlight if the discount/net cost does not match the existing deal in SAP
• If the customer is Tesco they should fill in Mod Break and Store Base
• A prompt if the customer is a supermarket for them to fill in store base
• A prompt to check for duplicates if a deal for the same ISBN/customer/promotion has already been loaded.

below are the macros i've tried. maybe i'm close but i have no idea at this point.

This below before save code works but stops working when the locked cell macro is activated. I also wanted it to show the location of the highlighted cel but couldnt figure that out so had to use an offset colour instead. but what i wanted was to essentially if isbn was entered then column J and M to pop up with a message box stating what to do and where to do it and refuse to save otherwise.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("Deals Agreed 2021")
  
  Application.ScreenUpdating = False
    Columns("I:I").EntireColumn.Hidden = False
    Columns("L:L").EntireColumn.Hidden = False
  
  Dim r1, r2, MultipleRange As Range
  Set r1 = Range("I:I")
  Set r2 = Range("L:L")
  Set MultipleRange = Union(r1, r2).Find("Required", , xlValues)

If MultipleRange Is Nothing Then
Else
MultipleRange.Offset(, 1).Interior.Color = RGB(255, 0, 0)
MsgBox "Please enter Promotion and Chart Date"
Cancel = True
End If
Columns("I:I").EntireColumn.Hidden = True
Columns("L:L").EntireColumn.Hidden = True
Application.ScreenUpdating = True
  End Sub

this is the locked row code i used but not sure if this is what i should be doing?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("Deals Agreed 2021")
  Dim r1 As Range
  ws.Unprotect
  Set r1 = Range("V:V").Find("TRUE", , xlValues)

If r1 Is Nothing Then
Else
ws.Range("U" & r1.Row).Locked = True
End If
    
    ws.Protect
    
End Sub

i also used this code to change back the red colour to none but doesnt work when the locked cel macro is active

 Application.EnableEvents = False

  Dim r1, r2, MultipleRange As Range
  Set r1 = Range("J2:J100000")
  Set r2 = Range("M2:M100000")
  Set MultipleRange = Union(r1, r2)
  
  If Not IsEmpty(Target.Value) Then
  MultipleRange.Interior.Color = xlNone
        End If

basically, i'm lost guys and i have no idea how to get this to seamlessly work where i can fulfil the list of sheet requirements - any help would be amazing.

 

[sdennant]

Hi all, just wanted to know if the above was possible. I think I’m close but can’t tie it all together

 

[sdennant]

Hi there, can anyone assist me please?

 

[sdennant]

?

 

[sdennant]

Hi all,
Is there anyone out there that can offer some advice on this?