Trigger macro automatically on specific value entered in any cell of a specific range using VBA

Dear Experts:

I would like to get the following macro expanded:

If somebody enters '03-014-00-01' in any of the cells of the range spanning from B2:B80 on the Worksheet 'Offer', ...
... the following macro is to be triggered


Sub CopyNamedRangeData()

    Dim wS3 As Worksheet
    Dim rCnt As Long, cCnt As Long
    Application.ScreenUpdating = False
    Set wS3 = Worksheets("Offer")
    Application.Range("Set_OMF_Basic").Copy Destination:=Cells(ActiveCell.Row + 1, 1)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Open in new window


Professional Help is very much appreciated. I have attached the corresponding file for your convenience.

Thank you very much in advance.

Regards, Andreas

Trigger-macro-on-certain-value-EE.xlsm
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Use this code which will do what you are looking for..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B2:B80")) Is Nothing Then Exit Sub
Application.EnableEvents = False

 Dim wS3 As Worksheet
    Dim rCnt As Long, cCnt As Long
    Application.ScreenUpdating = False
    Set wS3 = Worksheets("Offer")
    Application.Range("Set_OMF_Basic").Copy Destination:=Cells(Target.Row + 1, 1)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    Application.EnableEvents = True
End Sub

Open in new window


Saurabh...
Trigger-macro-on-certain-value-EE.xlsm
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh,

thank you very much for your quick and professional help.

We are almost there, I guess ...
... with your macro the macro is triggered no matter which value I enter in the B2:B80 range.

But I would like the macro to get only triggered after entering the value  "03-014-00-01". Any other entry, no matter which will do nothing.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Saurabh Singh TeotiaCommented:
Use this Andreas..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B2:B80")) Is Nothing Then Exit Sub
Application.EnableEvents = False

if target.value="03-014-00-01"

 Dim wS3 As Worksheet
    Dim rCnt As Long, cCnt As Long
    Application.ScreenUpdating = False
    Set wS3 = Worksheets("Offer")
    Application.Range("Set_OMF_Basic").Copy Destination:=Cells(Target.Row + 1, 1)
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
end if
    
    Application.EnableEvents = True
End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh, will do some testing this evening, thank you very much for it.

Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
Hi Saurabh, ok, great, works like a charm. Thank you very much for it.

Regards, Andreas
Saurabh Singh TeotiaCommented:
Andreas... Yw.. Always Happy to Help.. :-)

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.