Paste disabled when using SelectionChange

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
I am using the worksheet_selectionchange macro. I noticed that if you refer to any other cell than the one that is the "target" cell, the Paste option is greyed out. This means that for the person using the worksheet, paste is never available. Is there a way of getting around this? I've tried both direct code inside of the macro and calls to other subs--neither works. I'm guessing that it's related to the fact the the macro is essentially using the excel clipboard to keep track of the content of the target, but that's not very helpful to the end-user. Any suggestions? Thanks
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Put some code in your event that restricts it from doing anything outside your target range...

Like if you only want the event code to run if the target cell is within column C

Then make this the first line of your event code.

If Intersect(Target,Range("C:C")) Is Nothing Then Exit Sub

Now, the event code will only run if the user selects a cell in column C
that should help..
 

ChgsAllAround

New Member
Joined
Apr 11, 2008
Messages
36
Thank you. My problem is that I NEED to refer to cells other than the target in this macro. For example, Col A and Col B have dates. Col C is colored depending on how far apart the dates fall. So...the user changes a date, hits enter, and the macro activates to check for time span.

"WhichColor" is a custom function that uses a case statement to return the proper ColorIndex

For i = 1 to 5
Range("C" & i).Interior.ColorIndex = WhichColor(Range("A" & i).Value, Range("B" & i))
Next i

Any more ideas on how to reference other cells in this macro and still have paste available?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It will still be able to "Refer" to other cells.

That code will just restrict the code to be TRIGGERED by column C being selected. so it doesn't run if OTHER cells are changed..
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
AFAIK the event code being called will clear the clipboard. One solution would be to paste the clipboard elsewhere (perhaps to a hidden sheet) then to grab it back again within the event procedure.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,201
Messages
5,443,002
Members
405,212
Latest member
Arnie58

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top