Excel Query with IF, Vlookup and Data Validation

[ANAND KUMAR]

Dear Experts,

I found myself in a situation of help - I have a file which is attached at following URL

https://drive.google.com/open?id=0Bxtc6jUuxe_pY0hoQXBsaWNhOTg

So If C24 is Yes then E25 needs a value (from drop down – otherwise it needs an error message to detail the spec). Equally if C24 is No or blank then E25 must also be blank (or show error message)

Same is also true for C22 and E22

How can I lock it too? I also need to add a button at the bottom to export to pdf.

Thanks in Advance for any Help.

 

[L. Howard]

Try this in sheet 1 module.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("$C$24")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim myCheck

Select Case Range("$C$24").Value
 Case Is = "Yes"
    If Range("E25") = "" Then
       MsgBox "CDU SPEC (E25) must have an entry."
       [E25].Activate
     Else
    End If
    
 Case Is = "No"
    If Range("E25") <> "" Then
       
      myCheck = MsgBox("CDU SPEC (E25) must be blank." _
                & vbCr & vbCr & "          Clear Cell E25 now?", vbYesNo)
      
      If myCheck = vbNo Then
          Exit Sub
        Else
          [E25].ClearContents
          [C24].Activate
      End If
       
    End If
   
End Select
End Sub

I overlooked E22 part. I will re-look the code.

H

 

[L. Howard]

Try this instead. Delete entire previous code and paste this in its place.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C24,C22")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim myCheck    

Select Case Range("$C$24").Value
 Case Is = "Yes"
    If Range("E25") = "" Then
       MsgBox "CDU SPEC (E25) must have an entry."
       [E25].Activate
     Else
    End If
    
    Case Is = "No"
    If Range("E25") <> "" Then
       
      myCheck = MsgBox("CDU SPEC (E25) must be blank." _
                & vbCr & vbCr & "          Clear Cell E25 now?", vbYesNo)
      
      If myCheck = vbNo Then
          Exit Sub
        Else
          [E25].ClearContents
          [C24].Activate
      End If
       
    End If
   
End Select

Select Case Range("$C$22").Value
  Case Is = "Yes"
    If Range("E23") = "" Then
       MsgBox "LABEL SPEC (E23) must have an entry."
       [E23].Activate
     Else
    End If
    
  Case Is = "No"
    If Range("E23") <> "" Then
       
      myCheck = MsgBox("LABEL SPEC (E23) must be blank." _
                & vbCr & vbCr & "          Clear Cell E23 now?", vbYesNo)
      
      If myCheck = vbNo Then
          Exit Sub
        Else
          [E23].ClearContents
          [C22].Activate
      End If
       
    End If
   
End Select

End Sub

 

[ANAND KUMAR]

Thanks for Reply Howard.

I have Tried the code but It asks for Macro Name to get Run.

 

[L. Howard]

Thanks for Reply Howard.

I have Tried the code but It asks for Macro Name to get Run.

The code will run automatically when you make a selection from either drop down in C22 or C24 on sheet 1.

Howard

 

[ANAND KUMAR]

Ok. And what about Export into PDF. If I create a button and assign this with above code then would it export the Excel in PDF.

Pls. clarify.

 

[L. Howard]

Ok. And what about Export into PDF. If I create a button and assign this with above code then would it export the Excel in PDF.

Pls. clarify.

The code I provided will only take care of the C22, C24, E23 and E25 to the extent that I understand your statement here.

So If C24 is Yes then E25 needs a value (from drop down – otherwise it needs an error message to detail the spec). Equally if C24 is No or blank then E25 must also be blank (or show error message)

The code is a Change_Event macro and is never assigned to a button, it is called into action by a change on the sheet. In this case it responds to changes to either C22 or C24.

Sorry to have overlooked the export to pdf, I don't know how to do that, and I don't understand the lock issue.

Howard

 

[L. Howard]

With the macro recorder I was able to get this code to save as PDF. I am uncertain if this is anything that will work for you, but this macro can be assigned to a button on the sheet. I'm pretty sure the file path or whatever its called would need to be changed.

Copy to a standard module.

Howard

Sub PDF_SAVE_AS()
'
' PDF_SAVE_AS Macro
'
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\ffff\Documents\Copy of Customer Spec Form.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

 

[ANAND KUMAR]

Occurs an error of "Run Time Error - 1004".

 

[L. Howard]

Did you change the file path to yours?

Maybe try using the macro recorder and Save as PDF(*pdf) in the Save as Type window.

That is how I got the code I posted. When you record, it will include your file path,

Howard