Switch cells on input
 

I'm using Worksheet_Change for a range d6:l17 . What I am trying to
accomplish is if 1 is entered in F and a 1 is entered in H both contence is
cleared and a 1 is placed on d.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
Range(Cells(.Row, 4)).Value = 1
End If
End With
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

??? Thanks for the help

Switch cells on input
 

Dan:

try

With Target
If Cells(.Row, 6) = 1 And Cells(.Row, 8).Value = 1 Then
Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
Cells(.Row, 4).Value = 1
End If
End With

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Dan" wrote:

I'm using Worksheet_Change for a range d6:l17 . What I am trying to
accomplish is if 1 is entered in F and a 1 is entered in H both contence is
cleared and a 1 is placed on d.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
Range(Cells(.Row, 4)).Value = 1
End If
End With
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

??? Thanks for the help

Switch cells on input
 

Dan

I don't think you can get what you want to do by doing what you are
doing?

A couple of reasons :-

1. When you are entering some data in a cell, your selection or target
range will always be 1 cell. So you event procedure will never go
beyond line 1 since you cell count will never be greater than 1.

2. You can never enter any data in more than 1 cell at any one time
from the front end.

What you can do is write a Worksheet_Change event procedure and check
for the contents of cells in columns 'F' and 'H' for the same row say
contents of cells(5,6) and cells(5,8) and if both these cells contain a
value of 1, then clear both these cells and insert a 1 in cell(5,4).

However, you will need to disable your event procedures before you make
any changes to any cell to avoid the cascading effect. I take it that
you are aware of the two ways of doing so viz Disable applicationEvents
or create a static variable and insert the corresponding code as the
first line of your event procedure.


Best regards


Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=486129

Switch cells on input
 

And if you don't want to also clear the contents of the cell in column G
then try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If Cells(.Row, 6).Value = 1 _
And Cells(.Row, 8).Value = 1 Then
Cells(.Row, 6).ClearContents
Cells(.Row, 8).ClearContents
Cells(.Row, 4).Value = 1
End If
End With
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Hope this helps
Rowan

Dan wrote:
I'm using Worksheet_Change for a range d6:l17 . What I am trying to
accomplish is if 1 is entered in F and a 1 is entered in H both contence is
cleared and a 1 is placed on d.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("D6:L17")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target
If Range(Cells(.Row, 6)) And Range(Cells(.Row, 8)).Value = 1 Then
Range(Cells(.Row, 6), Cells(.Row, 8)).ClearContents
Range(Cells(.Row, 4)).Value = 1
End If
End With
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

??? Thanks for the help

Switch cells on input
 

Hi Deepak

1. When you are entering some data in a cell, your selection or target
range will always be 1 cell. So you event procedure will never go
beyond line 1 since you cell count will never be greater than 1.

The line of code is "If Target.Cells.Count 1 Then Exit Sub" so it will
only exit if more than one cell is changed at a time. Usually used to
trap users pasting a range, deleting a row etc.

2. You can never enter any data in more than 1 cell at any one time
from the front end.

Select a range of cells on a sheet. Type some data and hit Ctrl+Enter.
You've just entered the same data in the entire range all at the same time.

However, you will need to disable your event procedures before you make
any changes to any cell to avoid the cascading effect.

The Application.EnableEvents commands are included in the original event.

Kind Regards
Rowan

agarwaldvk wrote:
Dan

I don't think you can get what you want to do by doing what you are
doing?

A couple of reasons :-

1. When you are entering some data in a cell, your selection or target
range will always be 1 cell. So you event procedure will never go
beyond line 1 since you cell count will never be greater than 1.

2. You can never enter any data in more than 1 cell at any one time
from the front end.

What you can do is write a Worksheet_Change event procedure and check
for the contents of cells in columns 'F' and 'H' for the same row say
contents of cells(5,6) and cells(5,8) and if both these cells contain a
value of 1, then clear both these cells and insert a 1 in cell(5,4).

However, you will need to disable your event procedures before you make
any changes to any cell to avoid the cascading effect. I take it that
you are aware of the two ways of doing so viz Disable applicationEvents
or create a static variable and insert the corresponding code as the
first line of your event procedure.


Best regards


Deepak Agarwal

Switch cells on input
 

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim x As Integer

If Target.Cells.Count 1 Then Exit Sub
Set rng = Union(Range("F6:F17"), Range("H6:H17"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
x = IIf(Target.Column = 6, 3, -1)
If Target.Value = 1 And Target(1, x) = 1 Then
Union(Target, Target(1, x)).ClearContents
Cells(Target.Row, 4) = 1
End If
CleanUp:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Regards,
Greg

Switch cells on input
 

Hi Guys

Apologies here - forgot about the Ctrl+Shift+Enter

I was thinking about selecting a cell to enter values.


Best regards


Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=486129