Pasting values doesn't trigger the VBA script

stlouis

New Member
Joined
Aug 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I found a great piece of code on here a while back, and thank the author very much! It formats MAC addresses into the standard looking format from typed text with no colons or capital letters to capitalized and verified as hex. The problem is when I paste a column of MACs into the worksheet, it won't run unless I double click each cell and go down the column. I saw this failure in another thread on here, and the solution provided was that each cell needs to be processed in an array. I am really stumped in trying to incorporate this solution into this MAC address script. Can anyone help me with this? Here is the MAC script:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
  
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
   If IsEmpty(Target) _
    Or Target.Cells.Count > 1 _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
   'in case they typed - in as part of the entry
   tempMACAddress = UCase(Trim(Replace(Target.Text, sepChar, "")))
   'check for special case: entry is all digits, no alpha and
   'is less than 12 characters.  Means we probably dropped leading zeros
   'put them back
   If Len(tempMACAddress) < macLength Then
     For LC = 1 To Len(tempMACAddress)
       If Mid(tempMACAddress, LC, 1) < "0" _
        Or Mid(tempMACAddress, LC, 1) > "9" Then
         Exit For ' exit early
       End If
     Next
     If LC = Len(tempMACAddress) + 1 Then
       'were all digits, add leading zeros
       tempMACAddress = _
        String(macLength - Len(tempMACAddress), "0") & tempMACAddress
     End If
   End If
   If Len(tempMACAddress) <> macLength Then
     MsgBox "The entry does not conform to MAC Address length of " & macLength, _
      vbOKOnly + vbExclamation, "Invalid Entry"
     Exit Sub
   End If
   'validate as all valid characters
   For LC = 1 To Len(tempMACAddress)
     If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
       MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
        vbOKOnly + vbExclamation, "Invalid Entry"
       Exit Sub
     End If
   Next
   'looks valid, format it up
   Application.EnableEvents = False ' to prevent re-entry to this routine
   Target = Left(tempMACAddress, 2) & sepChar _
    & Mid(tempMACAddress, 3, 2) & sepChar _
    & Mid(tempMACAddress, 5, 2) & sepChar _
    & Mid(tempMACAddress, 7, 2) & sepChar _
    & Mid(tempMACAddress, 9, 2) & sepChar _
    & Right(tempMACAddress, 2)
   Application.EnableEvents = True
 End Sub

and here is the thread that resolved the problem in another script (post 7 is the solution):
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,062
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try changing this
VBA Code:
   If IsEmpty(Target) _
    Or Target.Cells.Count > 1 _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
to
VBA Code:
   If IsEmpty(Target) _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
 

stlouis

New Member
Joined
Aug 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try changing this
VBA Code:
   If IsEmpty(Target) _
    Or Target.Cells.Count > 1 _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
to
VBA Code:
   If IsEmpty(Target) _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
Thanks for the help! I guess my question is how to meld the unrelated solution from the second reference script into something that will work on the first script (the first script is the one I want to use; the second link was just for reference because it was claimed to be a solution for someone else). My problem is I can't figure out how (or where) to pass the first script into an array like the author did on the second script. Or if you know of a better solution to make a group of pasted cells appear as typed data so the script runs, that is my goal here. Thanks again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,062
Office Version
  1. 365
Platform
  1. Windows
I haven't looked at the link, just suggested a change to the code you posted so that it would work on more than 1 cell, however forgot to add a loop
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
  
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
   If IsEmpty(Target) _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
   Dim Cl As Range
   For Each Cl In Intersect(Target, Range("E:E"))
      'in case they typed - in as part of the entry
      tempMACAddress = UCase(Trim(Replace(Target.Text, sepChar, "")))
      'check for special case: entry is all digits, no alpha and
      'is less than 12 characters.  Means we probably dropped leading zeros
      'put them back
      If Len(tempMACAddress) < macLength Then
        For LC = 1 To Len(tempMACAddress)
          If Mid(tempMACAddress, LC, 1) < "0" _
           Or Mid(tempMACAddress, LC, 1) > "9" Then
            Exit For ' exit early
          End If
        Next
        If LC = Len(tempMACAddress) + 1 Then
          'were all digits, add leading zeros
          tempMACAddress = _
           String(macLength - Len(tempMACAddress), "0") & tempMACAddress
        End If
      End If
      If Len(tempMACAddress) <> macLength Then
        MsgBox "The entry does not conform to MAC Address length of " & macLength, _
         vbOKOnly + vbExclamation, "Invalid Entry"
        Exit Sub
      End If
      'validate as all valid characters
      For LC = 1 To Len(tempMACAddress)
        If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
          MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
           vbOKOnly + vbExclamation, "Invalid Entry"
          Exit Sub
        End If
      Next
   
      'looks valid, format it up
      Application.EnableEvents = False ' to prevent re-entry to this routine
      Cl = Left(tempMACAddress, 2) & sepChar _
       & Mid(tempMACAddress, 3, 2) & sepChar _
       & Mid(tempMACAddress, 5, 2) & sepChar _
       & Mid(tempMACAddress, 7, 2) & sepChar _
       & Mid(tempMACAddress, 9, 2) & sepChar _
       & Right(tempMACAddress, 2)
   Next Cl
   Application.EnableEvents = True
 End Sub
 

stlouis

New Member
Joined
Aug 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I haven't looked at the link, just suggested a change to the code you posted so that it would work on more than 1 cell, however forgot to add a loop
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
 
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
   If IsEmpty(Target) _
    Or Target.Column <> Range(maEntryColumn & 1).Column _
    Or Target.Row < maFirstRow Then
     Exit Sub ' no work to do
   End If
   Dim Cl As Range
   For Each Cl In Intersect(Target, Range("E:E"))
      'in case they typed - in as part of the entry
      tempMACAddress = UCase(Trim(Replace(Target.Text, sepChar, "")))
      'check for special case: entry is all digits, no alpha and
      'is less than 12 characters.  Means we probably dropped leading zeros
      'put them back
      If Len(tempMACAddress) < macLength Then
        For LC = 1 To Len(tempMACAddress)
          If Mid(tempMACAddress, LC, 1) < "0" _
           Or Mid(tempMACAddress, LC, 1) > "9" Then
            Exit For ' exit early
          End If
        Next
        If LC = Len(tempMACAddress) + 1 Then
          'were all digits, add leading zeros
          tempMACAddress = _
           String(macLength - Len(tempMACAddress), "0") & tempMACAddress
        End If
      End If
      If Len(tempMACAddress) <> macLength Then
        MsgBox "The entry does not conform to MAC Address length of " & macLength, _
         vbOKOnly + vbExclamation, "Invalid Entry"
        Exit Sub
      End If
      'validate as all valid characters
      For LC = 1 To Len(tempMACAddress)
        If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
          MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
           vbOKOnly + vbExclamation, "Invalid Entry"
          Exit Sub
        End If
      Next
  
      'looks valid, format it up
      Application.EnableEvents = False ' to prevent re-entry to this routine
      Cl = Left(tempMACAddress, 2) & sepChar _
       & Mid(tempMACAddress, 3, 2) & sepChar _
       & Mid(tempMACAddress, 5, 2) & sepChar _
       & Mid(tempMACAddress, 7, 2) & sepChar _
       & Mid(tempMACAddress, 9, 2) & sepChar _
       & Right(tempMACAddress, 2)
   Next Cl
   Application.EnableEvents = True
 End Sub
thanks again for the help! So deleting the line "Or Target.Cells.Count > 1 _" as suggested causes a run-time error 94: invalid use of Null.
If I leave it in, I get the script to work as previous without the cl loop, but not as hoped with block paste. Does the next command need to distinguish between next and next Cl and next LC? I'm really appreciative for the help! Also, I am learning as I go, so sorry if that is way off!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
Try these 2 changes:
1.1) Replace (IsEmpty - only works on single cell)
VBA Code:
    If IsEmpty(Target) _
        Or Target.Column <> Range(maEntryColumn & 1).Column _
        Or Target.Row < maFirstRow Then
        Exit Sub ' no work to do
    End If

1.2) With this (check if all cells in target are blank)
VBA Code:
    If Target.Cells.Count = Application.CountBlank(Target) _
        Or Target.Column <> Range(maEntryColumn & 1).Column _
        Or Target.Row < maFirstRow Then
        Exit Sub ' no work to do
   End If

2.1) Replace (Target)
VBA Code:
tempMACAddress = UCase(Trim(Replace(Target.Text, sepChar, "")))

2.2) With this (Cl)
VBA Code:
tempMACAddress = UCase(Trim(Replace(Cl.Text, sepChar, "")))
 

stlouis

New Member
Joined
Aug 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

thanks to both of you guys for steering me on the right path! Both of your ideas worked! Thanks a lot! But then i couldn't leave well enough alone! I added some more changes and it seems to have created another problem. I wanted to plan for the situation where a mac address could be imported as one of these possibilities:
spaces A1 A2 A3 A4 A5 A6
colons A1:A2:A3:A4:A5:A6
dashes A1-A2-A3-A4-A5-A6
so I thought using 3 if-then statements to instr() for those 3 characters and replace them with null before applying formatting would solve the issue. It did, but my trim function now has no effect. so entering " A1:A2:A3:A4:A5:A6 "returns an error of being over 12 characters. Shouldn't the trim function take care of that? It did until the 3 if-thens came into play.
Also, why doesn't the if-thens take care of the odd scenario of mixed separators, like "A1:A2:A3-A4-A5 A6"? Not that it could likely happen, but my troubleshooting found that. It gives the error of too many characters.

Thanks for the teaching and all of your help. I REALLY appreciate it!

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
 
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
       If Target.Cells.Count = Application.CountBlank(Target) _
        Or Target.Column <> Range(maEntryColumn & 1).Column _
        Or Target.Row < maFirstRow Then
        Exit Sub ' no work to do
   End If
   Dim Cl As Range
   For Each Cl In Intersect(Target, Range("E:E"))
      'in case they typed some separator character in as part of the entry
       If InStr(Cl.Text, " ") Then
        tempMACAddress = UCase(Trim(Replace(Cl.Text, " ", "")))
       ElseIf InStr(Cl.Text, "-") Then
        tempMACAddress = UCase(Trim(Replace(Cl.Text, "-", "")))
       ElseIf InStr(Cl.Text, ":") Then
        tempMACAddress = UCase(Trim(Replace(Cl.Text, ":", "")))
       Else: tempMACAddress = UCase(Trim(Replace(Cl.Text, sepChar, "")))
       End If
      'check for special case: entry is all digits, no alpha and
      'is less than 12 characters.  Means we probably dropped leading zeros
      'put them back
      If Len(tempMACAddress) < macLength Then
        For LC = 1 To Len(tempMACAddress)
          If Mid(tempMACAddress, LC, 1) < "0" _
           Or Mid(tempMACAddress, LC, 1) > "9" Then
            Exit For ' exit early
          End If
        Next
        If LC = Len(tempMACAddress) + 1 Then
          'were all digits, add leading zeros
          tempMACAddress = _
           String(macLength - Len(tempMACAddress), "0") & tempMACAddress
        End If
      End If
      If Len(tempMACAddress) <> macLength Then
        MsgBox "The entry does not conform to MAC Address length of " & macLength, _
         vbOKOnly + vbExclamation, "Invalid Entry"
        Exit Sub
      End If
      'validate as all valid characters
      For LC = 1 To Len(tempMACAddress)
        If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
          MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
           vbOKOnly + vbExclamation, "Invalid Entry"
          Exit Sub
        End If
      Next
  
      'looks valid, format it up
      Application.EnableEvents = False ' to prevent re-entry to this routine
      Cl = Left(tempMACAddress, 2) & sepChar _
       & Mid(tempMACAddress, 3, 2) & sepChar _
       & Mid(tempMACAddress, 5, 2) & sepChar _
       & Mid(tempMACAddress, 7, 2) & sepChar _
       & Mid(tempMACAddress, 9, 2) & sepChar _
       & Right(tempMACAddress, 2)
   Next Cl
   Application.EnableEvents = True
 End Sub

mac adddress converter.xlsm
ABCDEF
53NetgearGS110TPA1:A2:A3:A4:A5:A6192.168.0.239
54A2:A2:A3:A4:A5:A6
55E3:E3:E3:E3:E3:E3
56A1:A1:A1:A1:A1:A1
57B1:B2:B3:B4:B5:B6
5800:13:CB:01:58:C2
5900:13:CB:00:49:E2
6000:13:CB:02:98:8B
6100:13:CB:02:A7:4F
6200:13:CB:02:7A:51
6300:13:CB:01:5A:19
6400:13:CB:01:AE:53
6500:13:CB:FF:C8:DB
6600:13:CB:01:7D:F5
6700-13-CB-01-5B-E4
6800:00:00:00:00:00
6900:00:00:00:00:00
7000:13:CB:01:43:35
710013CB00F377
720013CB00E885
730013CB00F0AA
740013CB00C5D9
750013CB08143C
760013CB008CA9
770013CB004811
780013CB004830
790013CB0044F3
800013CB0044DE
810013CB002141
820013CB034290
830013CB00560C
840013CB033C58
850013CB084C7A
860013CB3403F6
870013CB015B7E
880013CB08551B
890013CB015342
Sheet1
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
See if the code below gives you any ideas, I suspect there will be shorter ways of doing it (maybe using regex).
This will be take care of mixed delimiters. You could do it as one line but I find this easier to follow and maintain.

I did not find an issue with Trim so there is something else going on there.
(Mind you by having the replace " " last, trim is probably unnecessary)

VBA Code:
Sub testReplace()

    Dim str As String
    Dim strLen As Long
    
    str = " A1:A2:A3:A4:A5:A6 "
    
    str = Replace(str, ":", "")
    str = Replace(str, "-", "")
    str = Replace(str, " ", "")
    str = UCase(Trim(str))
    strLen = Len(str)

    Debug.Print str, strLen
End Sub

Debug.Print produced this

1629784909163.png
 

stlouis

New Member
Joined
Aug 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thanks to all that have helped me with this script to verify MAC addresses that are entered or batch pasted from another source. It was a fun first script to cobble together from advice and other's donated script on this website! In that fashion, I'd like to share the finished product with the community.
This script verifies that the input is valid hexadecimal, pads leading zeros only if the MAC entered is solely numeric (because the leading zeros might have been truncated due to number rules at time of entry), formats the end product in standard groups of 2 separated by colons (or you can change the delimiter character to whatever), deletes leading or trailing spaces, converts other common delimiters (space " " and dash "-") or simply converts speedy data entry if you didn't enter any delimiter, it will throw in the colons for you.

THANKS ALL!

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'assumes just 1 column for the MAC address entries
 'but could be adapted for more

'change these constants to define your worksheet
   Const maEntryColumn = "E" ' MAC addresses entered into this column
   Const maFirstRow = 2 ' first row that could have MAC address in it
 
   Const sepChar = ":"  ' to put between each pair of address parts
   Const macLength = 12 ' # characters in MAC address
   Const macValidCharacters = "ABCDEF01234567890"
 'working variables
   Dim LC As Integer ' loop counter
   Dim tempMACAddress As String
       If Target.Cells.Count = Application.CountBlank(Target) _
        Or Target.Column <> Range(maEntryColumn & 1).Column _
        Or Target.Row < maFirstRow Then
        Exit Sub ' no work to do
   End If
   Dim Cl As Range
   For Each Cl In Intersect(Target, Range("E:E"))
      'in case they typed some separator character in as part of the entry
        tempMACAddress = Replace(Cl.Text, ":", "")
        tempMACAddress = Replace(tempMACAddress, "-", "")
        tempMACAddress = Replace(tempMACAddress, " ", "")
        tempMACAddress = UCase(tempMACAddress)
      'check for special case: entry is all digits, no alpha and
      'is less than 12 characters.  Means we probably dropped leading zeros
      'put them back
      If Len(tempMACAddress) < macLength Then
        For LC = 1 To Len(tempMACAddress)
          If Mid(tempMACAddress, LC, 1) < "0" _
           Or Mid(tempMACAddress, LC, 1) > "9" Then
            Exit For ' exit early
          End If
        Next
        If LC = Len(tempMACAddress) + 1 Then
          'were all digits, add leading zeros
          tempMACAddress = _
           String(macLength - Len(tempMACAddress), "0") & tempMACAddress
        End If
      End If
      If Len(tempMACAddress) <> macLength Then
        MsgBox "The entry does not conform to MAC Address length of " & macLength, _
         vbOKOnly + vbExclamation, "Invalid Entry"
        Exit Sub
      End If
      'validate as all valid characters
      For LC = 1 To Len(tempMACAddress)
        If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then
          MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _
           vbOKOnly + vbExclamation, "Invalid Entry"
          Exit Sub
        End If
      Next
  
      'looks valid, format it up
      Application.EnableEvents = False ' to prevent re-entry to this routine
      Cl = Left(tempMACAddress, 2) & sepChar _
       & Mid(tempMACAddress, 3, 2) & sepChar _
       & Mid(tempMACAddress, 5, 2) & sepChar _
       & Mid(tempMACAddress, 7, 2) & sepChar _
       & Mid(tempMACAddress, 9, 2) & sepChar _
       & Right(tempMACAddress, 2)
   Next Cl
   Application.EnableEvents = True
 End Sub
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,650
Office Version
  1. 365
Platform
  1. Windows
Thank you for sharing and for the feedback. Glad we could help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,036
Messages
5,767,766
Members
425,431
Latest member
Sayson

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