SelectionChange problem
 

The following code works perfectly for a cell that is NOT merged:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

The following code also works perfectly for a cell range that IS MERGED:
If Target.Address = Range("CHECK.EX.PAYERS").Address Then: Application.Run
"PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

What DOESN'T work for a MERGED cell range is:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

nor will any other statement like:
If Not Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then
Exit Sub
End If

I get an error 13 Type mismatch.
PLEASE NOTE: I have been careful to ensure that the named range of the
merged cells describes the whole range (not just the first cell).

Can someone please help. It's doing my head in (and it's already done in
from a cold!). Regards, Brett.

SelectionChange problem
 

HI

I had the same problem long ago try this

If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Mergearea.Cells(1,1).Valu e = "END EXISTING PAYERS
CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

Let me know if it does or doesnt work But i am pretty sure it does

Cheers


"Brettjg" wrote:

The following code works perfectly for a cell that is NOT merged:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

The following code also works perfectly for a cell range that IS MERGED:
If Target.Address = Range("CHECK.EX.PAYERS").Address Then: Application.Run
"PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

What DOESN'T work for a MERGED cell range is:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

nor will any other statement like:
If Not Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then
Exit Sub
End If

I get an error 13 Type mismatch.
PLEASE NOTE: I have been careful to ensure that the named range of the
merged cells describes the whole range (not just the first cell).

Can someone please help. It's doing my head in (and it's already done in
from a cold!). Regards, Brett.

SelectionChange problem
 

Hi CmK, well, not so far. Now if I click on ANY cell I get a 1004 App or
object defined error. This is what I coded:
If Target.Address = Range("CHECK.VISA").Address And
Range("CHECK.VISA").MergeArea.Cells(1, 1).Value = "END VISA CHECK" Then:
Application.Run "PERSONAL.xls!END_VISA_CHECK".

Then I thought I might need to bring the name range back to just one cell.
This stopped the error from happening, but the macro won't fire when I click
on the "CHECK.VISA".

"CmK" wrote:

HI

I had the same problem long ago try this

If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Mergearea.Cells(1,1).Valu e = "END EXISTING PAYERS
CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

Let me know if it does or doesnt work But i am pretty sure it does

Cheers


"Brettjg" wrote:

The following code works perfectly for a cell that is NOT merged:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

The following code also works perfectly for a cell range that IS MERGED:
If Target.Address = Range("CHECK.EX.PAYERS").Address Then: Application.Run
"PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

What DOESN'T work for a MERGED cell range is:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

nor will any other statement like:
If Not Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then
Exit Sub
End If

I get an error 13 Type mismatch.
PLEASE NOTE: I have been careful to ensure that the named range of the
merged cells describes the whole range (not just the first cell).

Can someone please help. It's doing my head in (and it's already done in
from a cold!). Regards, Brett.

SelectionChange problem
 

What does the (1, 1) refer to? My merged area is 7 cells wide by 1 cell high
so I tried (1, 7) and (7, 1) but that didn't work either.

"CmK" wrote:

HI

I had the same problem long ago try this

If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Mergearea.Cells(1,1).Valu e = "END EXISTING PAYERS
CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

Let me know if it does or doesnt work But i am pretty sure it does

Cheers


"Brettjg" wrote:

The following code works perfectly for a cell that is NOT merged:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

The following code also works perfectly for a cell range that IS MERGED:
If Target.Address = Range("CHECK.EX.PAYERS").Address Then: Application.Run
"PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

What DOESN'T work for a MERGED cell range is:
If Target.Address = Range("CHECK.EX.PAYERS").Address And
Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then:
Application.Run "PERSONAL.xls!END_EXISTING_PAYERS_CHECK"

nor will any other statement like:
If Not Range("CHECK.EX.PAYERS").Value = "END EXISTING PAYERS CHECK" Then
Exit Sub
End If

I get an error 13 Type mismatch.
PLEASE NOTE: I have been careful to ensure that the named range of the
merged cells describes the whole range (not just the first cell).

Can someone please help. It's doing my head in (and it's already done in
from a cold!). Regards, Brett.

SelectionChange problem
 

Hi again

You dont really have to re reference the range name to include all the
merged cells
Justr use Range("MyRangeName").Mergearea.address

Anyways I have done some testing
Heres the codes i used
and it worked fine
I hope this helped
My range name was Cell and A1 to C2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyTestString As Variant
Dim oMergearea As Object
Set oMergearea = Range("Cell").MergeArea

MyTestString = oMergearea.Cells(1, 1).Value

If Target.Address = oMergearea.Address And MyTestString = "AAAA" Then
MsgBox "True"
Else
MsgBox "false"
End If


End Sub

SelectionChange problem
 

That's it! It works now. Tricky little suckers eh? Thank you very much for
all your efforts. Regards, Brett.

"CmK" wrote:

Hi again

You dont really have to re reference the range name to include all the
merged cells
Justr use Range("MyRangeName").Mergearea.address

Anyways I have done some testing
Heres the codes i used
and it worked fine
I hope this helped
My range name was Cell and A1 to C2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyTestString As Variant
Dim oMergearea As Object
Set oMergearea = Range("Cell").MergeArea

MyTestString = oMergearea.Cells(1, 1).Value

If Target.Address = oMergearea.Address And MyTestString = "AAAA" Then
MsgBox "True"
Else
MsgBox "false"
End If


End Sub