Avatar of x5225642
x5225642 asked on

Auto run macro if user tried to type in cell

Hi all,

Really at a loss on how to script this...

I need a macro to run if the user tries to type in any of the cells in the range (E7:E650). I know how to set it up if the user selects the cell it runs but this is not practical for this because I need the user to still be able to delete and copy the cell content ... To make matters a bit more complicated I need to make sure it doesn't cause conflicts with a user form which unloads data into those cells!

Any suggestions are greatly appreciated as I am well and truly stuck
Thanks in advance!
Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Doug

Would using cell protection do the trick? You can disable it when your form is running and enable it when your form is not running. You should be able to set up specific protections when you protect your sheet (e.g., select cells).
Professor J

you can put this code below in worksheet object
you macro to be called in would be in a standard module

replace YouMacro with your macro real name

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("E7:E650")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    For Each r In rng
        If r.Value = "" Then
            Exit Sub
        End If
    Next r
    Call YourMACRO
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Professor J

@x5225642

Sktneer code works for you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
x5225642

Hi ProfessorJimJam,
tried your code but it doesn't seem to do anything unfortunately.

Hi sktneer,
Tried your code too but again it does not appear to do anything and presents my other macro starting which is triggered from double clicking a cell in the same range

Sorry!
Subodh Tiwari (Neeraj)

What macro you are calling within the code I proposed?
The code will work if user tries to change  the cell content by typing in it and once hits Enter then or deleting the cell content and as a result your desired macro should be called automatically in any case.
ASKER
x5225642

I don't know if this helps but is the code I have in worksheet object:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
   
    If Not Intersect(Target, Range("E7:E650")) Is Nothing Then
    Call Ref
    Call PickFunctions
   
    End If
    Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
If Intersect(Target, Range("E7:E650")) Is Nothing Then
    Exit Sub
Else
    Range("A1").Value = ActiveCell.Address
    Call PickFunctions
           
    End If
End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
x5225642

Ok I restarted my spreadsheet and now the code above is working (not sure why that made any difference).
Thanks again for your help!
Subodh Tiwari (Neeraj)

You're welcome. Glad it worked as per your requirement.