VBA to make cell mandatory based on another cell

[iktpq]

HI, so i have a sheet set that gets filled in and updated and i need to make D13 mandatory only when G7 shows "internal" if G7 is anything else D13 is not needed to be mandatory

i've played around with some things but couldn't get anything working.

Thanks in advance for you help

 

[Alan_P]

Hi iktpq,

Could you explain what/when you want to happen if the conditions are not met? For example do you want to make it so that the workbook can't be saved if G7 = "internal" and D13 is empty? Or would you like a message to pop up if internal is entered in G7 prompting them to complete D13? Or make it so another sheet can't be selected? or make it so that only D13 can be selected? The possibilities are endless Depends on how annoying you want it to be

example; if you put the below code in the "ThisWorksheet" module, the file cannot be saved if G7 = "internal" and D13 is empty

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If LCase(Sheets("Sheet1").Range("G7").Value) = "internal" And Sheets("Sheet1").Range("D13") = "" Then
    SaveAsUI = False
    MsgBox "A value must be entered in cell D13", vbExclamation
    Sheets("Sheet1").Range("D13").Select
End If
End Sub

Cheers,
Alan.

 

[iktpq]

Hi iktpq,

Could you explain what/when you want to happen if the conditions are not met? For example do you want to make it so that the workbook can't be saved if G7 = "internal" and D13 is empty? Or would you like a message to pop up if internal is entered in G7 prompting them to complete D13? Or make it so another sheet can't be selected? or make it so that only D13 can be selected? The possibilities are endless Depends on how annoying you want it to be

example; if you put the below code in the "ThisWorksheet" module, the file cannot be saved if G7 = "internal" and D13 is empty

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If LCase(Sheets("Sheet1").Range("G7").Value) = "internal" And Sheets("Sheet1").Range("D13") = "" Then
    SaveAsUI = False
    MsgBox "A value must be entered in cell D13", vbExclamation
    Sheets("Sheet1").Range("D13").Select
End If
End Sub

Cheers,
Alan.

Sorry i should have said, so the sheet is used for quality and is uploads to a report, when the upload button is pressed ive got it set up so some cells must be filled in or it won't get sent. This now needs to be one of them but the d13 only needs to be filled in if g17 = internal. So I will have msg box pop up to prompt them to do so. I'm not at work at the moment so can't try out your code. I'll give it a shot tomorrow. Thanks for the help

 

[Alan_P]

Ah ok, best thing is to send the code you've already got and then we can work the conditions into it...

 

[iktpq]

Ah ok, best thing is to send the code you've already got and then we can work the conditions into it...

Sub upload()
If Range("D10").Value > Range("D9") Then
MsgBox "Please check the date of the call before continuing"
Else
'to check if mandatory fields are empty
 Dim mandatorycell As Range
 
 For Each mandatorycell In Range("D10:D12")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure a Date and Call ID are inserted before uploading"
        GoTo Start
       End If
Next
  For Each mandatorycell In Range("G7")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please insert a 'Site' before uploading"
        GoTo Start
       End If
       
 Next
  For Each mandatorycell In Range("G10:G13")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure Advisor Name, Manager Name and Call Type are inserted before completing"
        GoTo Start
       End If
       
 Next
  For Each mandatorycell In Range("E18:E21")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure all questions within 'CEAR' are completed before uploading"
        GoTo Start
       End If
       
 Next
  For Each mandatorycell In Range("E25:E31")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure all questions within 'DPA/BACS' are completed before uploading"
        GoTo Start
       End If
       
 Next
  For Each mandatorycell In Range("E35:E62")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure all questions within 'SLC25' are completed before uploading"
        GoTo Start
       End If
       
 Next
  For Each mandatorycell In Range("E66:E80")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure all questions within 'key message' are completed before uploading"
        GoTo Start
       End If
       
           Next
  For Each mandatorycell In Range("E99")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please complete DCM"
        GoTo Start
       End If
 Next
 
    For Each mandatorycell In Range("E83")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please ensure you have pointed the call before uploading"
        GoTo Start
       End If
 Next
 
    For Each mandatorycell In Range("B86")
  If mandatorycell.Value = vbNullString Then
        MsgBox "Please add pointing comments before uploading"
        GoTo Start
       End If
       Next
Application.ScreenUpdating = False
    Workbooks.Open Filename:= _
        "urlGoesHere"
    Windows("Graeme Scorecard.xlsm").Activate
    Sheets("Sales Scorecard").Select
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    Rows("2:2").Select
    Selection.Copy
    Windows("Graeme Data.xlsm").Activate
ActiveSheet.Range("A1").End(xlDown).Offset(1).EntireRow.Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
    Windows("Graeme Scorecard.xlsm").Activate
    Sheets("Sales Scorecard").Select
        Sheets("Sales Scorecard").Select
    Sheets("Sheet1").Visible = False
    
 ' ClearAll Macro
  Range("G7:G10").Select
    Selection.ClearContents
    Range("G11:G12").Select
    Selection.ClearContents
    Range("D10:D11").Select
    Selection.ClearContents
    Range("D13").Select
    Selection.ClearContents
    Range("E18:F21").Select
    Selection.ClearContents
    Range("E25:F31").Select
    Selection.ClearContents
    Range("E35:F62").Select
    Selection.ClearContents
    Range("E66:F80").Select
    Selection.ClearContents
    Range("B86").Select
    Selection.ClearContents
    Range("B93").Select
    Selection.ClearContents
     Range("E99").Select
      Selection.ClearContents
     Range("E83").Select
     ActiveCell.FormulaR1C1 = "0"
 Range("A1").Select
 
 Application.ScreenUpdating = True
 
 MsgBox "Your scorecard has been saved and submitted"
Start:
End If
End Sub

i know its messy code, i'm not that skilled :P

so when the sheet has been filled in we would press an upload button that then runs this code which takes what we've just done and puts in to the next line in our own data sheets. everything is working fine. its i just need to have D13 mandatory when G7 = internal, if its not filled in to show a msg box, if it is not required then go to the next mandatory cell to check

thanks for the help

 

[iktpq]

Hi iktpq,

Could you explain what/when you want to happen if the conditions are not met? For example do you want to make it so that the workbook can't be saved if G7 = "internal" and D13 is empty? Or would you like a message to pop up if internal is entered in G7 prompting them to complete D13? Or make it so another sheet can't be selected? or make it so that only D13 can be selected? The possibilities are endless Depends on how annoying you want it to be

example; if you put the below code in the "ThisWorksheet" module, the file cannot be saved if G7 = "internal" and D13 is empty

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If LCase(Sheets("Sheet1").Range("G7").Value) = "internal" And Sheets("Sheet1").Range("D13") = "" Then
    SaveAsUI = False
    MsgBox "A value must be entered in cell D13", vbExclamation
    Sheets("Sheet1").Range("D13").Select
End If
End Sub

Cheers,
Alan.

I've used part of this and it seems to work fine

 If LCase(Sheets("Sales Scorecard").Range("G7").Value) = "internal" And Sheets("Sales Scorecard").Range("D13") = "" Then
    MsgBox "Please enter the BP number"
    GoTo Start
 Cancel = True

Thanks for your help Alan

 

[Alan_P]

Hi iktpq, Glad I could (sort of) help