Drop down selection activates MACRO

[ALAMZEB]

Hi
I am bit new do this.
I have below code to remove data from cells but can it be done from drop down
For example : if someone select “remove” in A1 dropdown, than macro will activate without going to press macro button.
What code should I put in that activates macro if “remove” is selected from A1 drop down
Thanks in advance

 

[Nebu]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
    If Range("A1") = "Remove ALL" Then
    Range("A7:A23").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End If
End If
End Sub

Use the above code

 

[Smallman]

You really only should be triggering the macro if Cell A1 changes. This should get you over the line.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [a1]) Is Nothing Then
        If [A1] = "Remove ALL" Then Range("A7", Range("A" & Rows.Count).End(xlUp)).Clearcontents
    End If
End Sub

Take care

Smallman

 

[Nebu]

@Smallman My Macro will trigger only when Cell A1 changes hence the if condition target.row and target.column )

 

[Smallman]

Oh yeah. Here is a valid lesson then, in VBA there is no need to select anything in order to perform an action. After you have written some code take a look at it and if there is any selection in the code take it out.

Take care

Smallman

 

[Nebu]

I have not wriiten the entire code I just modified the code given by ALAMZEB by putting if condition to it thats all, please cross varify with the attached file from ALAMZEB.....

 

[Smallman]

Always try and improve on the coding which is available as there will be people coming in after (maybe years after) to look at what is there. Drop the best you have and if someone improves on it try and learn from that.

 

[Nebu]

Doing this in between my office hrs trying to my best, its a learning process for me as well though have a fair amount of work experience with excel

 

[Jake Collins]

Oh yeah. Here is a valid lesson then, in VBA there is no need to select anything in order to perform an action. After you have written some code take a look at it and if there is any selection in the code take it out.

Take care

Smallman

Dear Smallman

Can you explain what you mean by this, as your code also has a select in it? (and no action?) Thanks.

 

[Smallman]

Ha - that says clearcontents.

I mean to say and I will go the long way around here, when you hit the Record button on a macro the recorder records everything. Users tend to leave the selection in coding and anything else the recorder may leave in which adds no value to the file.

Take care

Smallman

 

[ALAMZEB]

Hi Small Man & Nebu

both of your codes are not working, I know its me

 

[Nebu]

Well, You were not using none of our macros I fixed whatever code you had written it is working at my end here is the file....

 

[Smallman]

Hi Alamzeb

You will need to turn the alerts off when you run the code like this. Otherwise it run more times than it needs to.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
  If Not Intersect(Target, [B3]) Is Nothing Then
    If [B3] = "Remove all and Replace" Then Range("A17:C97,O17:O97,Q17:Q97,S17:S97,T6:T10, U11").ClearContents
  End If
Application.EnableEvents = True
End Sub

Then turn the events on again at the end of the coding to ensure it works the next time B3 changes.

Take care

Smallman