Mandatory Check and Locked Row Help

Status
Not open for further replies.

sdennant

New Member
Joined
Mar 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I wondered if someone could help me on the below thread:

I’m struggling to find a solution to this. Please help if you have any suggestions.

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

ISBNCustomerDivisionTitleAuthorPub dateRRPFormatImportant Do Not TouchPromotionAdditional infoImportant Do Not TouchChart DateMod BreakStorebaseDiscount Net Cost End dateExisting deal in SAP end date Existing deal in SAP terms Ratecard PO
9781408364093SainsburysHachette Children's GroupWhere's the Poo? Sticker Activity BookOrchard Books18/03/2021£4.99Kids_PBSS5SS5 Activity18/03/2021138£ 2.00 £ 350.004500125666
9781510202252TescoHachette Children's GroupThe IckabogJ.K. Rowling10/11/2020£20.00Kids_HBEPUEPU21/01/2021311£ 8.4031/12/99998.4£ 300.004500125671
9781408364833TescoHachette Children's GroupLove from Giraffes Can't DanceGiles Andreae07/01/2021£6.99Kids_EPUEPU21/01/2021311£ 2.8031/12/99992.8£ 300.004500125673
9781510108752TescoHachette Children's GroupHorrid Henry: Holiday HorrorsFrancesca Simon04/02/2021£6.99Kids_PBAISLEPower Aisle08/02/2021234£ 2.8031/12/99992.8£ 390.004500125671
9781444956368TescoHachette Children's GroupRainy Day StoriesEnid Blyton18/02/2021£6.99Kids_PBSIDE STACKStack08/02/2021363£ 2.8031/12/99992.8£ 800.00TRUE4500125671
9781444954265TescoHachette Children's GroupMagical Fairy TalesEnid Blyton03/09/2020£6.99Kids_PBSIDE STACKStack08/02/2021363£ 2.8001/05/20212.8£ 800.00TRUE4500125671
9781444956368Hachette Children's GroupRainy Day StoriesEnid Blyton18/02/2021£6.99Kids_PBAA20/01/2021
9781444954265Hachette Children's GroupMagical Fairy TalesEnid Blyton03/09/2020£6.99Kids_PBRequiredRequired


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.

VBA Code:
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?

VBA Code:
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

VBA Code:
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,596
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: Locked row based on value and Mandatory Fields

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.
 
Status
Not open for further replies.

Forum statistics

Threads
1,147,517
Messages
5,741,629
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top