• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop down selection activates MACRO

ALAMZEB

Member
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
 

Attachments

  • Macro Test.xlsm
    15.4 KB · Views: 12
Code:
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
 
You really only should be triggering the macro if Cell A1 changes. This should get you over the line.

Code:
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
 
Last edited:
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
 
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.
 
Last edited:
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 :)
 
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.
 
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
 
Hi Small Man & Nebu

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

Attachments

  • Macro Test2.xlsm
    13.4 KB · Views: 3
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....
 

Attachments

  • Macro Test2.xlsm
    16.4 KB · Views: 5
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.

Code:
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
 
Back
Top