problem code works in worksheet selection event but not in change event

[abdelfattah]

hello

i have this code works in selection event but not in change event and if is possible amending the code or getting alternative the code select cells or change depend on range ("d11:m22") and show in j23 so i want only this range applies when cell is actived not out side this range

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$j$23" Then
Target.Value = ""
Else
[j23].MergeArea = ActiveCell
End If
End Sub

my data

active cell.xlsm
	D	E	F	G	H	I	J	K	L	M
11	1	2	3	4	5	6	7	8	9	10
12	11	12	13	14	15	16	17	18	19	20
13	21	22	23	24	25	26	27	28	29	30
14	31	32	33	34	35	36	37	38	39	40
15	41	42	43	44	45	46	47	48	49	50
16	140	148	181	182	183	184	185	186	187	188
17	189	190	191	192	193	194	195	196	197	198
18	199	200	201	202	203	204	205	206	207	208
19	209	210	211	212	213	214	215	216	217	218
20	219	220	221	222	223	224	225	226	222	228
21	229	230
22				numbers
23
sheet1

 

[offthelip]

try this slight modification:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$j$23" Then
Target.Value = ""
Else
Application.EnableEvents = False
[j23].MergeArea = ActiveCell
Application.EnableEvents = True
End If
End Sub

what is happening your code triggers when a change is made, this makes a change in j23 which triggers the worksheet change event again which immediately clears out the value in j23!!

 

[abdelfattah]

thanks offthelip it works i have this code in selection change i try more to make target.value="" but it clear range "d11:m22" could you tell me how make j23 ="" if is not within range "d2:m22" just curiosity you've solved my problem

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cells As Range
    Set cells = ActiveSheet.Range("d11:m22")
    If Not (Intersect(Target, cells) Is Nothing) Then
    
       ActiveSheet.Range("j23").MergeArea = Target.Value
      
    End If
End Sub

 

[offthelip]

If I have understood what you want all you need is:

    If Not (Intersect(Target, cells) Is Nothing) Then
      ActiveSheet.Range("j23").MergeArea = Target.Value
     else
      ActiveSheet.Range("j23").MergeArea = ""
    End If

 

[abdelfattah]

it gives me error

Method 'Range' of object '_Worksheet' failed” error message

in this line

 ActiveSheet.Range("j23").MergeArea = Target.Value

 

[offthelip]

It works fine for me, what cell or cells did you have selected and what was in them? Note this was your code!!
it might be worth wrapping the application enable events falst / true around it to check it isn't trigger the worksheet change event e.g.

If Not (Intersect(Target, cells) Is Nothing) Then
     Application.EnableEvents = False
     ActiveSheet.Range("j23").MergeArea = Target.Value
     else
      Application.EnableEvents = False
     ActiveSheet.Range("j23").MergeArea = ""
    End If
    Application.EnableEvents = True

Note I have deliberately put the application enable event true outside the if statement!!

 

[abdelfattah]

it works only selection event not change any way the idea when show only values in this range d11:m22 but if i select out the specific range it show like this i don't want

1.xlsm
	G	H	I	J
22	numbers			numbers
23
sheet1

 

[offthelip]

I am sorry , I don't understand what you want

 

[abdelfattah]

let me make clear more the values are existed from d11: m22 when i choose from theses cell is ok the code works but if i select outside this range then also show for instance if i have some cells contain text or value n2,or p22 any cell locates outside this range d11:m22 it doesn't supposes show in j23 show only what locates within this range d11:m22

 

[offthelip]

I think I have understood what you want, try these two subroutines:;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.EnableEvents = False
    If Not (Intersect(Target, Range("d11:m22")) Is Nothing) Then
      ActiveSheet.Range("j23").MergeArea = Target.Value
      Else
      ActiveSheet.Range("j23").MergeArea = ""
    End If
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Intersect(Target, Range("d11:m22")) Is Nothing) Then
[j23].MergeArea = ActiveCell
Else
ActiveSheet.Range("j23").MergeArea = ""
End If
Application.EnableEvents = True

End Sub