experchange > excel.* > excel.main

ataur rahman rony (11-07-18, 09:33 PM)
Is there any option that users should not be able to copy and paste values which are not in the validation!!!
GS (11-07-18, 11:20 PM)
> Is there any option that users should not be able to copy and paste values
> which are not in the validation!!!


Normally, DV prevents entering values not defined in the settings unless you
deliberately disable that feature during setup. Recheck your DV config to be
sure that it applies the appropriate rules!
David (11-08-18, 05:34 AM)
On Wednesday, November 7, 2018 at 1:20:25 PM UTC-8, GS wrote:
> Normally, DV prevents entering values not defined in the settings unless you
> deliberately disable that feature during setup. Recheck your DV config tobe
> sure that it applies the appropriate rules!
> --
> Garry
> Free usenet access at
> Classic VB Users Regroup!
> comp.lang.basic.visual.misc
> microsoft.public.vb.general.discussion


Hello,
I do not see a way to stop it. Even after you copy and then select the cellto paste into, Excel keeps the copied from address as the Activecell.Address. I do not even see a way to ID the cell selected to paste into, which you need to be able to stop the operation, yes?

Thanks, David
GS (11-08-18, 06:47 PM)
> On Wednesday, November 7, 2018 at 1:20:25 PM UTC-8, GS wrote:
> Hello,
> I do not see a way to stop it. Even after you copy and then select the cell
> to paste into, Excel keeps the copied from address as the Activecell.Address.
> I do not even see a way to ID the cell selected to paste into, which you need
> to be able to stop the operation, yes?
> Thanks, David


Ok, I should expand my previous post..

When you copy any cell to a DV cell, it changes the DV cell to become
same as the copied cell exactly.

When you copy the *contents* of any cell to a DV cell, the value pastes
*without* regard to the DV setting, and the cell's DV setting persists.

When you copy the *contents* of any cell to a DV cell and use *EditMode*
(hit *F2*, not just start typing or pasting), the value pastes *with*
normal regard to the DV setting.

If you force EditMode on DV cells then DV settings will persist. This needs to
be done via code in the _SelectionChange event as follows...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("dvCells")) Is Nothing Then
'Disable Paste by clearing the current Clipboard contents
Application.CutCopyMode = False
End Sub

...where the range named "dvCells" is defined as 'fully-absolute' with local
(sheet level) scope as shown using the Me. object specifier denotes.

Now, your user is forced to enter a value directly because Excel will give the
user the ding.wav because there's nothing to paste!
Similar Threads