Do NOT allow validations to be overwritten with non-validation values

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
810
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a VBA to not allow cut and paste within a specific range, however what I am really searching for is to be able to copy paste into a cell which has a validation list but of the value being pasted does not match a value within the validation list then it will not allow it.

Is this possible?

Thanks in advance!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
The issue is that Copy/Paste also copies the formatting of the source cell, so it would overwrite any existing Conditional Formatting rules.
You can instead use Worksheet_Change event procedure VBA code to restrict what goes in that cell. So you would have to set the rules in the VBA code.
We can probably help you with that if you provide the details.

Of course the caveat there is that VBA/Macros need to be enabled for any VBA code to work.
 

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
810
Office Version
  1. 365
Platform
  1. Windows
The issue is that Copy/Paste also copies the formatting of the source cell, so it would overwrite any existing Conditional Formatting rules.
You can instead use Worksheet_Change event procedure VBA code to restrict what goes in that cell. So you would have to set the rules in the VBA code.
We can probably help you with that if you provide the details.

Of course the caveat there is that VBA/Macros need to be enabled for any VBA code to work.

ok great. thanks for the info!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,787
Messages
5,638,309
Members
417,020
Latest member
MSVII

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