Prevent duplicate entries By Button in column A Excel VBA

[kar2rost]

Hello good day
I want to create the number (According to the value of g1) in column A in the last line by using the button
If there is already data in column a, I want to send a message that this number is available and not allow it to be created
And if it is possible for you to select the same repeated line and show it ,so that I can change its value right away
Thanks

Sub commandbutton_clickl()

Dim i As Long
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("sheet1")
Set EvalRange = Range("A:a")

Dim iRow As Integer

iRow = Sh.cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sh.Range("a" & iRow).Value = Range("g1").Value

If Not IsError(i = WorksheetFunction.Match(Range("g1").Value, Range("A:A"), 0)) Then

i = WorksheetFunction.Match(Range("g1").Value, Range("A:A"), 0)

cells(i + 0, 2).Activate

Else

End If


End Sub

 

[DanteAmor]

Try this:

Sub commandbutton_clickl()
  Dim sh As Worksheet
  Dim f As Range
  
  Set sh = ThisWorkbook.Sheets("sheet1")
  Set f = sh.Range("A:A").Find(sh.Range("G1"), , xlValues, xlWhole, , , False)
  
  If f Is Nothing Then
    With sh.Range("A" & sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row)
      .Value = sh.Range("G1").Value
      .Offset(0, 1).Select
    End With
  Else
    MsgBox "this number is available"
    f.Select
  End If
End Sub

 

[kar2rost]

AMAZING tank u bro @DanteAmor

 

[kar2rost]

@DanteAmor
Hello again
I want to lock the sheet so that don't change the insert new numbers
But after locking the sheet, I can't create a new row and have error
What should I do if I want all the registered cells to be locked upwards after pressing the button and the rest of the cells are open so that they can take values?

Another issue is whether there is a code that can only enter numbers in column a and does not accept other values such as text

Thankful

 

[DanteAmor]

What should I do if I want all the registered cells to be locked upwards after pressing the button and the rest of the cells are open so that they can take values?

Do the following:

1. If the worksheet is protected, do the following:
   1. On the Review tab, click Unprotect Sheet (in the Changes group).
      
      
      
      Click the Protect Sheet button to Unprotect Sheet when a worksheet is protected.
   2. If prompted, enter the password to unprotect the worksheet.
2. Select the whole worksheet by clicking the Select All button.
   
   
3. On the Home tab, click the Format Cell Font popup launcher. You can also press Ctrl+Shift+F or Ctrl+1.
   
   
   
   
   
4. In the Format Cells popup, in the Protection tab, uncheck the Locked box and then click OK.
   
   
   
   This unlocks all the cells on the worksheet when you protect the worksheet.

Then try this macro:

Sub commandbutton_clickl()
  Dim sh As Worksheet
  Dim f As Range
 
  Set sh = ThisWorkbook.Sheets("sheet1")
  Set f = sh.Range("A:A").Find(sh.Range("G1"), , xlValues, xlWhole, , , False)
 
  If f Is Nothing Then
    sh.Unprotect
    With sh.Range("A" & sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row)
      .Value = sh.Range("G1").Value
      .Offset(0, 1).Select
      .Locked = True
    End With
    sh.Protect
  Else
    MsgBox "this number is available"
    f.Select
  End If
End Sub

----------------------

Another issue is whether there is a code that can only enter numbers in column a and does not accept other values such as text

For that.
Put the following code in the details of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Not IsNumeric(Target.Value) Then
      MsgBox "Only numeric values"
      Application.EnableEvents = False
      Target.Value = ""
      Application.EnableEvents = True
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[kar2rost]

tanks a lot you are genius @DanteAmor

i have this code for button as forty

Sub Button42_Click()
 ActiveCell.Value = Range("m1").Value
End Sub

after copy

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Not IsNumeric(Target.Value) Then
      MsgBox "Only numeric values"
      Application.EnableEvents = False
      Target.Value = ""
      Application.EnableEvents = True
    End If
  End If
End Sub

in the sheet its work and BUT
If by chance the cells of column A are clicked and then the button that I put the code on is clicked, it will give an error and say that it must be a number.
But it also empties the desired cell and the previous value is deleted from it
There is an way to just give an error and never allow accidental changes to the cells of column A when click on each button

 

[kar2rost]

@DanteAmor For another purpose
I want the name registers to be applied only in column b and not in any other cells