PDA

View Full Version : If statement if cell is active?



JKB
10-29-2015, 01:50 AM
Hi everybody.

This is a simple problem but i could not find an answer through google!
Im running a code, using byval:

Private Sub worksheet_change(ByVal target As Range)
If Not Application.Intersect(Range("H9:I9"), Range(target.Address)) Is Nothing Then
Call Special
End If
End Sub

Within the sub "Special" i want only one of the cells (I9) to switch "back" if you press enter... I had hoped that the following was possible:


If ActiveCell = ActiveSheet.Range("I10") Then
ActiveCell.Offset(-1, 0).Select
End If

I.e. if you push enter in the field I9 and it goes to I10, then it should go back to I9. In H9 i have used a list (from data validation) so it's not a problem! Does anybody know how i can create an if statement, which does an action, if the cell "I10" is activated?

Cheers!

Kenneth Hobs
10-29-2015, 05:51 AM
If I don't want them in a cell, I would just lock it and protect the sheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [I10]) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
Target.Offset(, -1).Select
.EnableEvents = True
End With
End Sub

JKB
11-02-2015, 12:30 AM
If I don't want them in a cell, I would just lock it and protect the sheet.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, [I10]) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
Target.Offset(, -1).Select
.EnableEvents = True
End With
End Sub


Ah nice! Thank you very much Kenneth! :)