Kann,
I'm not an Excel expert, but what you are saying really makes no sense. Look at your code. Why would you want to tell a user he or she as entered and invalid value and then continue an write that invalid value the sheet?
Dim bInvalid As Boolean
bInvalid = False
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).row)
If c.Value = txt_BPName1 Then
MsgBox " Duplicate Found.Please enter unique Base Product"
txt_BPName1.SetFocus
bInvalid = True
Exit For
End If
Next
If Not bInvalid Then 'What is the point of running this code if you have already set the focus to txt_BPName1?
For Each c In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).row)
If c.Value = txt_SPName1 Then
MsgBox "Cell " & c.Address & " Duplicate sub Product Found."
txt_SPName1.SetFocus
bInvalid = True
Exit For
End If
Next
End If
'So even if the values in the textboxes validatated abuve are "Invalid", you still want to
'write them to your sheet???