VBA works on undesignated range.

[Anne Troy]

If I have the range as column C, why does this also work when I accidentally click on column A or B?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C300")) Is Nothing Then
        Application.enableEvents = False
        If ActiveCell.Value = "X" Then
            ActiveCell.ClearContents
        Else
            ActiveCell.Value = "X"
            ActiveCell.Offset(0, 2).Copy
        End If
        Cancel = True
    End If
    Application.enableEvents = True
End Sub

 

[Joe4]

Howdy Anne!

A few things I see that look a little odd with your code.
- Typically in these Event Procedure code, you do not use "ActiveCell", but instead use "Target".
- I don't think the "Cancel = True" is needed here.
- In your "Else" clause, I see a copy command, but not a paste command. Is this intentional?

See if this code works any better for you:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C300")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "X" Then
            Target.ClearContents
        Else
            Target.Value = "X"
            Target.Offset(0, 2).Copy
        End If
    End If
    Application.EnableEvents = True
End Sub

If not, can you explain what exactly you would like the code to do?

 

[Fluff]

Along with Joe's comments, why are both the If & Else looking to see if the value is X?

 

[Joe4]

Along with Joe's comments, why are both the If & Else looking to see if the value is X?

Good catch!
Missed that one...

 

[Anne Troy]

Checking!

 

[Anne Troy]

I yoink all my code and edit very little. Not sure WHY it's looking for X either way. LOL
It seems to be working perfectly with TARGET code. THANKS!!!

Here's what I'm doing. Twice per month I receive about 175 invoices. I mark it received with the X. Then, the code copies the path & filename two columns to the right (I've derived from a formula using the date and some other stuff) to the clipboard. I right-click the file in the email and Save Target (or file or whatever) and just paste the contents of the clipboard as the file name. It leaves the extension whatever it happens to be (JPG, XLSX, PDF). (Luckily, Mr Excel's board programmer Suat Ozgur gave me code that converts all the non-PDF files to PDFs.) The

Mine would be:

C:\Users\anne\name of our Dropbox\00-Payroll\This Pay Invoices\PXT 2021-09-25 Anne Troy

Thanks so much!!

 

[Joe4]

Along with Joe's comments, why are both the If & Else looking to see if the value is X?

Actually, on closer inspection, that is not what it is doing.
The "IF" is looking to see if is equal to "X", and the "ELSE" is setting it equal to "X" if it is not.
So I don't think there is any redundancy or conflict there after all.

It seems to be working perfectly with TARGET code. THANKS!!!

Sounds like you are all set then!
If not, and you need anything else, let us know!

 

[Anne Troy]

Thanks for always being here, Joe!
(I love how my thing says I'm an MVP. Not a chance!)

 

[Fluff]

Actually, on closer inspection, that is not what it is doing.

Oops, you're right. Need to get my ? tested.

 

[Joe4]

Oops, you're right. Need to get my ? tested.

I have had (more than) a few of those days too myself!