Is it possible to convert this MS Access routine into a Excel macro?
What I would like to do is copy the part number column of an Excel
spreadsheet into a column next to it then add a "C-" as a prefix to
the part number and if the part number is over 16 characters to add
"(OVER)" to the end of it (we will use this to manually convert
these). The Access routine below looks at the length of the part
number then if it is under 16 characters it slaps a "C-" in front of
it and if it is 17 or more characters it pops-up with a warning
message. I don't know how much of this is applicable for a macro. I
haven't tried something like this in Excel and could use some help.
Thanks

Private Sub Part_Number_BeforeUpdate(Cancel As Integer)
Dim strPIM As String
Dim strMsg As String

If IsNull([Part Item Master]) Then
strPIM = [Part Number]
[Value] = strPIM
[Generic] = strPIM
If Len([Part Number]) < 17 Then
[Part Item Master] = "C-" & strPIM
Else
strPIM = Left([Part Number], 16)
strMsg = "The part number is over 16 characters therefore the
GP PIM will" _
& " have to be entered manually"

Response = MsgBox(strMsg, vbOKOnly, "Add GrowthPower Item
Master")

[Part Item Master] = "C-" & strPIM

' [Part Item Master].SetFocus

End If

End If



End Sub

Assuming the part number is in column A,

Private Sub PartNumber_Replace()

Dim lRow As Long

lRow = 1
With ActiveSheet
While .Range("A" & lRow) < ""
If Len(.Range("A" & lRow)) < 16 Then
.Range("B" & lRow) = "C-" & .Range("A" & lRow)
Else
.Range("B" & lRow) = .Range("A" & lRow) & "(OVER)"
End If
lRow = lRow + 1
Wend

End With

End Sub

You can get the same results by using a formula in column B:

=IF(LEN($A1)<16, "C-"&$A1, $A1 & "(OVER)")


"turtle" wrote:

Is it possible to convert this MS Access routine into a Excel macro?
What I would like to do is copy the part number column of an Excel
spreadsheet into a column next to it then add a "C-" as a prefix to
the part number and if the part number is over 16 characters to add
"(OVER)" to the end of it (we will use this to manually convert
these). The Access routine below looks at the length of the part
number then if it is under 16 characters it slaps a "C-" in front of
it and if it is 17 or more characters it pops-up with a warning
message. I don't know how much of this is applicable for a macro. I
haven't tried something like this in Excel and could use some help.
Thanks

Private Sub Part_Number_BeforeUpdate(Cancel As Integer)
Dim strPIM As String
Dim strMsg As String

If IsNull([Part Item Master]) Then
strPIM = [Part Number]
[Value] = strPIM
[Generic] = strPIM
If Len([Part Number]) < 17 Then
[Part Item Master] = "C-" & strPIM
Else
strPIM = Left([Part Number], 16)
strMsg = "The part number is over 16 characters therefore the
GP PIM will" _
& " have to be entered manually"

Response = MsgBox(strMsg, vbOKOnly, "Add GrowthPower Item
Master")

[Part Item Master] = "C-" & strPIM

' [Part Item Master].SetFocus

End If

End If



End Sub

Sounds a lot easier to just do it with formulas...
Let's assume part number is column A, and startd in cell A2
in B2:
=if(LEN(A2)16,"ERROR","C-"&A2)
Will show ERROR in the cells with over 16 characters.
You can then, say, highlight column B, Select Format -Conditonal Formatting
Select Cell Value is Equal To
"ERROR"
Click Format...
and, say, make the font red so the Errors will show up nice and easy.

OR

you can select your column A
got to Data- validation
Allow Text Length
Data:
less than 17
Select Error Alert tab
enter your desired error message.


"turtle" wrote:

Is it possible to convert this MS Access routine into a Excel macro?
What I would like to do is copy the part number column of an Excel
spreadsheet into a column next to it then add a "C-" as a prefix to
the part number and if the part number is over 16 characters to add
"(OVER)" to the end of it (we will use this to manually convert
these). The Access routine below looks at the length of the part
number then if it is under 16 characters it slaps a "C-" in front of
it and if it is 17 or more characters it pops-up with a warning
message. I don't know how much of this is applicable for a macro. I
haven't tried something like this in Excel and could use some help.
Thanks

Private Sub Part_Number_BeforeUpdate(Cancel As Integer)
Dim strPIM As String
Dim strMsg As String

If IsNull([Part Item Master]) Then
strPIM = [Part Number]
[Value] = strPIM
[Generic] = strPIM
If Len([Part Number]) < 17 Then
[Part Item Master] = "C-" & strPIM
Else
strPIM = Left([Part Number], 16)
strMsg = "The part number is over 16 characters therefore the
GP PIM will" _
& " have to be entered manually"

Response = MsgBox(strMsg, vbOKOnly, "Add GrowthPower Item
Master")

[Part Item Master] = "C-" & strPIM

' [Part Item Master].SetFocus

End If

End If



End Sub