VBA works on undesignated range.

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
If I have the range as column C, why does this also work when I accidentally click on column A or B?

VBA Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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?
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
Along with Joe's comments, why are both the If & Else looking to see if the value is X?
 

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607

ADVERTISEMENT

Checking! :love:
 

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
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!!

1632340559965.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
Thanks for always being here, Joe!
(I love how my thing says I'm an MVP. Not a chance!)
 

Forum statistics

Threads
1,148,259
Messages
5,745,719
Members
423,969
Latest member
seanguerrero

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top