VBA - Clear Content with drop-down list

[Beatrice]

I wish to create a simple drop-down box, option <YES> or <NO>
if the box is not filled, or selected as <NO>, then some of the specific cells content will be clear automatically.

My trial as below:
--
If Range("L66") <> "Yes" Then
Range("R61,R62,Q64,R64,Q65,R65,Q66,R66").ClearContents
End If
End Sub
--
but it failed, what is the reason behind and how to fix it?

Thanks in advance for your help.

 

[DanteAmor]

Try this

Range("R61,R62,Q64,R64,Q65,R65,Q66,R66").Value = ""

Or this:

  Dim c As Range
  For Each c In Range("R61,R62,Q64,R64,Q65,R65,Q66,R66")
    c.ClearContents
  Next

Any particular characteristics that the cells have?
The code is on a button or the code is executed when an event of sheet is triggered?

 

[Beatrice]

Try this

Range("R61,R62,Q64,R64,Q65,R65,Q66,R66").Value = ""

Or this:

  Dim c As Range
  For Each c In Range("R61,R62,Q64,R64,Q65,R65,Q66,R66")
    c.ClearContents
  Next

Any particular characteristics that the cells have?
The code is on a button or the code is executed when an event of sheet is triggered?

Thanks, I tried the first code is running fine.
But I don't know why the code is not run automatically.
To reply your question, the cells have no characteristics and the code is not a button.

Let me explain: I wish to run these code automatically while the worksheet/workbook is open.
User already set their excel to enable macro and no need click tab [developer]> [view code]>[F5]
This is to create a tool for staff and aim to limit access as much as possible to avoid mistake.

In the same worksheet I have different code is running fine, without go to the [developer]
but when I insert this new set of code, it is not running automatically.
Is there any way to fix it?

Thanks for your help.

 

[LazyBug]

What event (in workbook or worksheet?) should trigger the launch?

 

[DanteAmor]

You can put here the 2 complete codes.
Use code tag to insert code.

 

[Beatrice]

Private Sub clear_content()

If Range("L66") = "No" Then
Range("R61,R62,Q65,R65,Q66,R66,Q67,R67").Value = ""
MsgBox "You have removed the part entry", vbOKOnly, "Clear Content"
End If

End Sub

 

[DanteAmor]

In the same worksheet I have different code is running fine

What is that code?

 

[Beatrice]

What is that code?

Private Sub CommandButton2_Click()
CreateObject("Wscript.Shell").popup "Please allow a few second for Exporting... ", 1, "Kind Reminder", vbSystemModal + 64
    
  
Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

Application.ScreenUpdating = False

' Open Source and Destination files

    ChDir "C:\desktop\Tooling Project\EXPORT TEMPLATE"
    Workbooks.Open Filename:="C:\desktop\Tooling Project\EXPORT TEMPLATE\Export Template.xlsx"
     
' Copy Dedicated Part
    
Workbooks("Tool template.xlsm").Sheets("Summary").Range("A1:AL105").Copy
Workbooks("Export Template.xlsx").Sheets("COPY OF SUMMARY").Range("A1").PasteSpecial xlPasteValues

    Application.CutCopyMode = False
    Application.Goto Worksheets("COPY OF SUMMARY").Range("A1"), True

' Save Destination file named as Today's date

    DD = Day(Date)
    MMM = Month(Date)
    YY = Year(Date)
    WBK = "C:\desktop\Tooling Project\" & Range("F4") & " " & Range("L4") & " - summary - " & Format(Now, "DD MMM YY") & ".xlsx"
    
    Windows("Export Template.xlsx").Activate
    ActiveWorkbook.SaveAs Filename:=WBK, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    ActiveWindow.Close
    
    Call sourceSheet.Activate

MsgBox "Export Completed", vbOKOnly + vbInformation, "Done"
    

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'CHANGE 1, MARGIN INPUT
    
        Dim Rng As Range:   Set Rng = Range("AK11:AK14")
        If Target.Address(0, 0) = "AE10" Then
            Application.EnableEvents = False
            Select Case Target
                Case "Scenario 1": Rng = "No"
                Case "Scenario 2": Rng = "Yes"
                Case "Scenario 3"
                    Rng.ClearContents
                    Rng(1).Activate
                    MsgBox "Please advice which side(s) will have bleeding margin", vbInformation, "Input Required"
            End Select
            Application.EnableEvents = True
        End If
        
 
'CHANGE 2, PART SEARCH
        
        If Not Intersect(Target, Range("D23,D24,G23,G24,K23,K24")) Is Nothing Then
        Range("K39").ClearContents
    End If
        If Not Intersect(Target, Range("N23,N24,Q23,Q24,U23,U24")) Is Nothing Then
        Range("U39").ClearContents
    End If
        If Not Intersect(Target, Range("Z23,Z24,AC23,AC24,AG23,AG24")) Is Nothing Then
        Range("AG39").ClearContents
    End If

End Sub

above code is all running automatically, but when I continue to compose more code, the new code is not running
any idea?

 

[DanteAmor]

To make it automatic, add the following lines to the Change event of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

'CHANGE 1, MARGIN INPUT
    
  Dim Rng As Range:   Set Rng = Range("AK11:AK14")
  If Target.Address(0, 0) = "AE10" Then
    Application.EnableEvents = False
    Select Case Target
        Case "Scenario 1": Rng = "No"
        Case "Scenario 2": Rng = "Yes"
        Case "Scenario 3"
            Rng.ClearContents
            Rng(1).Activate
            MsgBox "Please advice which side(s) will have bleeding margin", vbInformation, "Input Required"
    End Select
    Application.EnableEvents = True
  End If
        
 
'CHANGE 2, PART SEARCH
        
    If Not Intersect(Target, Range("D23,D24,G23,G24,K23,K24")) Is Nothing Then
        Range("K39").ClearContents
    End If
    
    If Not Intersect(Target, Range("N23,N24,Q23,Q24,U23,U24")) Is Nothing Then
        Range("U39").ClearContents
    End If
    
    If Not Intersect(Target, Range("Z23,Z24,AC23,AC24,AG23,AG24")) Is Nothing Then
        Range("AG39").ClearContents
    End If

'Other
  If Not Intersect(Target, Range("L66")) Is Nothing Then
    If LCase(Target.Value) = LCase("No") Then
      Range("R61,R62,Q65,R65,Q66,R66,Q67,R67").Value = ""
      MsgBox "You have removed the part entry", vbOKOnly, "Clear Content"
    End If
  End If

End Sub

 

[Beatrice]

Thank you DanteAmor