Prevent userform from transfering data if duplicate value

Weasley0307

New Member
Joined
Dec 25, 2023
Messages
6
Office Version
  1. 2021
Platform
  1. Windows
Hi. I have a userform for transfering the data into sheet1. But I want to make sure the userform does not save the data if the OC/SC No. textbox's value is duplicated with the existing data in column C of the sheets. As shown in picture below. I want to have a pop up msgbox saying that there is duplicated value with column C and it does not transfer the data, which means it will exit the private save data sub that i have.
1706497680803.png
1706497766874.png
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Add the following code to your "Save" button.

Change TextBox4 to the name of your textbox OC/SC No.

Rich (BB code):
Private Sub CommandButton1_Click()
  Dim f As Range
  
  With TextBox4
    If .Value = "" Then
      MsgBox "Enter OC/SC No. "
      .SetFocus
      Exit Sub
    End If
    Set f = Range("C:C").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "there is duplicated value with column C and it does not transfer the data"
      .SetFocus
      Exit Sub
    End If
  End With
 
  '
  '
  'Here continues your code to pass the data to the sheet.
 
 
End Sub

Note:
If you have questions about how to adapt the code, it would be a good idea to put all your code and the names of each control here.
You should put this in the original post, this would help provide you with a better answer.

;)
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

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
Back
Top