VBA to allow numbers only of copy/pasted data while keeping destination cell formatting

JustMeAZ

New Member
Joined
Oct 30, 2022
Messages
1
Office Version
  1. 365
I’m limited in my Excel abilities and somehow got suckered into developing this spreadsheet for many different users to copy and paste number values only from their own source data.

There’s too many of them and we can’t make sure they will “paste values” only. So that’s not an option. I found VBA code that works to prevent the reformatting the destination cell with their source:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next

Target.PasteSpecial xlPasteValues

Application.CutCopyMode = True

End Sub

How, what, where can I add into this to make it so that the destination cells will not accept letters or characters or anything that isn’t a number (decimals are fine) when it is pasted instead of entered? In case it’s relevant, the range of these cells is broken up all across this spreadsheet. For example, it would apply to B9, B10, B11, B12 and then E9, E10, E11, E12. Then B15, B16 and E15, E16. And so on. So it’s consistently in the same columns, but not every single cell In the column.

I had done data validation on these cells but found they’re wiped out when copy and paste is used.

Thanks so much for any guidance!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Add this code to The sheet. Right Click on the sheet name at the bottom of excel, and Click View Code. Paste the below code

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Range("B9:B12,E9:E12,E15:E16") ' specify all your range Here
    If Not Intersect(Target, rg) Is Nothing Then
        If Not IsNumeric(Intersect(Target, rg)) Then
            Application.EnableEvents = False
            Application.Undo
                MsgBox "ONLY NUMBERS ARE ALLOWED IN THIS CELL", vbOKOnly, "INVAILD VALUE" ' MsgBox row is Optional
            Application.EnableEvents = True
                Exit Sub
        End If
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top