VBA Code needed to remove first 4 digits

[Marsman]

I have a need for multiple users to work on a shared spreadsheet that will copying and pasting in account numbers.

with that said - what code could i use to place into the spreadsheet that would strip the first four digits of the number once they have pasted it into a particular column?

example...
in Column E the user would past in 1234567890123456

once they do that either CTRL V or Right Mouse click and Paste of the above number to end up with the result of 567890123456.

Code I am currently using depends on a manual run of a Macro but I would like to have it automatically do it...

Sub test()
Dim c As Range
For Each c In Range("E2:E" & Range("E" & Rows.Count).End(xlUp).Row)
    If Left(c, 2) Like "####" Then c = Mid(c, 3)
Next
End Sub

 

[Marsman]

OK, you have really confused me with your last post.

I don't understand what Password Protecting the sheet has to do with anything at all here.

Yes, I understand that the entries are numbers, but we need to format as Text so we do not lose leading zeroes.
That is exactly what this line of code that I provided does.

cell.NumberFormat = "@"

This line actually formats it as Text, not number (the "@" is an alpha character telling it to format as Text, not number - do NOT change the word "NumberFormat" to "TextFormat", that is not valid syntax!").

If you are still having some sort of issue, please provide an actual example of exactly what is happening.

I know, doesn't make sense to me as well...

When the sheet has the Password removed it works as needed, but this is not an option

 

[Joe4]

What is your intention with protecting the sheet?
What are you trying to protect against?
Which options are checked when you protect the sheet?
It might just be a matter of unchecking the "Format cells" box.

What people often do when having VBA run against protecting sheets is have the VBA code temporarily unprotect the sheet long enough for VBA to make its changes, then re-protect it.

So the code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Find all entred in cells in column E
    Set rng = Intersect(Target, Range("E:E"))
    
'   Exit if no updated cells in column E
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    For Each cell In rng
'       See if entry length in 16
        If Len(cell) = 16 Then
'           Temporarily disable events and unprotect sheet
            Application.EnableEvents = False
            ActiveSheet.Unprotect Password:="password"
'           Chop off first 4 characters
            cell.NumberFormat = "@"
            cell = Mid(cell, 5, 12)
'           Re-enable events and re-protect sheet
            Application.EnableEvents = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
        End If
    Next cell
    
End Sub

Note that I probably don't have the right settings (to match what you have checked). You can get that by turning on the Macro Recorder, and recording yourself turning on Password Protection, stopping the Recorder, and viewing the Code you just recorded. Then drop the code into my code above, and replace my references of "password" with your password.

 

[mole999]

I have a need for multiple users to work on a shared spreadsheet that will copying and pasting in account numbers.

then why protect the sheet people are working on, or is this a particular range unprotected ?

 

[Marsman]

What is your intention with protecting the sheet?
What are you trying to protect against?
Which options are checked when you protect the sheet?
It might just be a matter of unchecking the "Format cells" box.

What people often do when having VBA run against protecting sheets is have the VBA code temporarily unprotect the sheet long enough for VBA to make its changes, then re-protect it.

So the code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Find all entred in cells in column E
    Set rng = Intersect(Target, Range("E:E"))
   
'   Exit if no updated cells in column E
    If rng Is Nothing Then Exit Sub
   
'   Loop through updated cells
    For Each cell In rng
'       See if entry length in 16
        If Len(cell) = 16 Then
'           Temporarily disable events and unprotect sheet
            Application.EnableEvents = False
            ActiveSheet.Unprotect Password:="password"
'           Chop off first 4 characters
            cell.NumberFormat = "@"
            cell = Mid(cell, 5, 12)
'           Re-enable events and re-protect sheet
            Application.EnableEvents = True
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
        End If
    Next cell
   
End Sub

Note that I probably don't have the right settings (to match what you have checked). You can get that by turning on the Macro Recorder, and recording yourself turning on Password Protection, stopping the Recorder, and viewing the Code you just recorded. Then drop the code into my code above, and replace my references of "password" with your password.

that addition to the code works great... thanks again.

to answer your questions

What is your intention with protecting the sheet?

to keep folks i work with from changing any format with headers and columns

What are you trying to protect against?

same as previous answer

Which options are checked when you protect the sheet?

only the headers are checked off for Protection, All other fields are able to have text entered or are setup with Data Validation Rules

It might just be a matter of unchecking the "Format cells" box.

not sure how to answer this one. but the piece you added for Password protection works great.

 

[Marsman]

then why protect the sheet people are working on, or is this a particular range unprotected ?

the reason is to protect the format of the way the spreadsheet looks. Column widths, headers etc.

 

[Joe4]

You do realize that you can project just certain areas of you spreadsheet, right?
When you Protect your sheet, only the "Locked" cells are protected.
So, if you first select ALL the cells of your sheet, then right-click, select Format cells, go to the Protection tab and uncheck the "Locked" box, this will unlock all the cells on the sheet.
Then, highlight just the cells you want to protect (i.e. the header rows), go to Format cells, Protections, and check the "Locked" box. Now when you Protect the sheet, it will only Protect that area, and it shouldn't affect your macro.

Or, you can simply use the method I suggested and have your macro unprotect your spreadsheet while it makes it updates, then re-protect it when it is done make the changes.