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:
and here is the thread that resolved the problem in another script (post 7 is the solution):
www.mrexcel.com
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 Suband here is the thread that resolved the problem in another script (post 7 is the solution):
Trigger Macro when data pasted into cells
I have a fully functioning macro which multiplies numbers entered it a range of cells by -1 'MINUS FIG COSTSPrivate Sub Macro43(ByVal Target As Range) If Intersect(Target, Range("U13:X156")) Is Nothing Then Exit Sub If Target.Count > 1 Then Exit Sub If Target.Value > 0 Then Target...
