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!
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!
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).
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@x5225642
Sktneer code works for you.
Sktneer code works for you.
ASKER
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!
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!
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.
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
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
ASKER
Ok I restarted my spreadsheet and now the code above is working (not sure why that made any difference).
Thanks again for your help!
Thanks again for your help!
You're welcome. Glad it worked as per your requirement.