Macro that autofills the cell based on drop down menu in excel

0
votes

I need help for generating the macro that basically gives the value "200000" based on a drop down menu in a cell. This drop down menu has two defined values in it(120 and 480). If other value in the drop down menu is selected then, I should have the freedom of writing any value that I want. The code which I came up with is below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
 If Not Intersect(Target, Range("$G$11")) Is Nothing Then
 Range("$B$20:$R$25,$Z$20:$AM$25").ClearContents
 End If

If Target.Cells.Count > 1 Then Exit Sub
 If Not Intersect(Target, Range("$G$11")) Is Nothing Then
 Range("$F$16:$Q$16,$R$15:$U$16,$V$16:$AA$16,$AB$15:$AM$16").ClearContents
 End If

If Range("I16") = 120 Or Range("I16") = 480 Then
        Range("F16") = 200000
    Else
        Range("F16") = ""
    End If
exitHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub

However, I have another macro which clears all the contents in the cells due to which the above code is causing an error. Any help is much appreciated.

2
excelvbaautofill
Maybe you should show us the other macro then? Not much we can do with the posted code. – Tim Williams
Hi Tim, thanks for viewing. Please find below – Siddharth Gadekar
´´´´ If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("$G$11")) Is Nothing Then Range("$F$16:$Q$16,$R$15:$U$16,$V$16:$AA$16,$AB$15:$AM$16").ClearContents End If – Siddharth Gadekar
Please edit your question if you need to add code - it's unreadable in comments. Also, don't just post snippets - it's useful to know for example if you're using an event handler to fire your code (and I'd have to guess that, based on the presence of Target there...) The less guessing we do the better. – Tim Williams
Hi Tim, I am really sorry. I have edited the code as per your request in the original question. – Siddharth Gadekar

2 Answers

1
votes

Make sure you're not re-triggering your event handler from within. Also worth adding an error handler to make sure events aren't left turned off.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v

    On Error GoTo exitHandler

    If Target.Cells.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Me.Range("G11")) Is Nothing Then
        Application.EnableEvents = False
        Me.Range("B20:R25,Z20:AM25,F16:Q16,R15:U16,V16:AA16,AB15:AM16").ClearContents
    End If

    If Not Intersect(Target, Me.Range("I16")) Is Nothing Then
        v = Target.Value
        Application.EnableEvents = False
        Me.Range("F16").Value = IIf(v = 120 Or v = 480, 200000, "")
    End If

exitHandler:
    Application.EnableEvents = True

End Sub
0
votes

Basically you just need to disable events before clearing cells so that the Change code is not triggered.

I'm not sure how the second bit of code relates so may need some adjustment.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("$G$11")) Is Nothing Then
        Application.EnableEvents = False
        Range("$B$20:$R$25,$Z$20:$AM$25").ClearContents
        Range("$F$16:$Q$16,$R$15:$U$16,$V$16:$AA$16,$AB$15:$AM$16").ClearContents
        If Range("I16") = 120 Or Range("I16") = 480 Then 'presumably belongs elswhere as just cleared I16 above?
            Range("F16") = 200000
        Else
            Range("F16").Clear
        End If
    End If
    Application.EnableEvents = True
exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub