stop increment numbers into textbox when I press close form or cancel button

[MKLAQ]

Hello

this code will increment numbers continuously when run the userform every time.

what I want when run the userform just show the last number is existed in cell A1 into textbox without increment numbers .

for instance if the number is BGH-02/23-1 in A1 , then will show in TEXTBOX1 BGH-02/23-1 and if I press commandbutton1 then will increment number in A1 ,TEXTBOX1 = BGH-02/23-2 , if I press commandbutton2 without press commandbutton1 then doesn't increment in textbox1, A1 should keep BGH-02/23-1 without increment and if I close form from window button without press commandbutton1 then also should not increment numbe and keep A1 ,TEXTBOX1 = BGH-02/23-1, if I press commandbutton1 and close the userform then will increment number in A1 ,TEXTBOX1 = BGH-02/23-2 , if I press commandbutton1 will increment number in A1 ,TEXTBOX1 = BGH-02/23-2 after that press commandbutton2 then should return to old number A1 ,TEXTBOX1 = BGH-02/23-1
, if I press commandbutton1 more than one time , then will pop up message for two choices . if press ok will continue increming numbers and if I press no ,then will not increment (just keep the last incrementing number)

Private Sub UserForm_Activate()
  Dim num As String
  Dim cell As Range
  Dim m As String, y As String
  
  Set cell = Sheets("Sheet1").Range("A1")    'Set the sheet name and any available cell
  
  num = cell.Value
  
  If Left(num, 3) <> "BGH" Or Mid(num, 4, 1) <> "-" Or _
     Mid(num, 7, 1) <> "/" Or Mid(num, 10, 1) <> "-" Then
    num = "BGH-" & Format(Date, "mm/yy") & "-1"
  Else
    y = Mid(num, 8, 2)
    m = Mid(num, 5, 2)
    If y <> Format(Date, "yy") Or m <> Format(Date, "mm") Then
      num = "BGH-" & Format(Date, "mm/yy") & "-" & Split(num, "-")(2) + 1
    Else
      num = Left(num, 10) & Split(num, "-")(2) + 1
    End If
  End If
  
  cell.Value = num
  TextBox1 = num
End Sub

I hope somebody help .

 

[rakesh seebaruth]

Private Sub UserForm_Activate()
    Dim num         As String
    Dim cell        As Range
    Dim m           As String, y As String
    
    Set cell = Sheets("Sheet1").Range("A1")
    
    num = cell.Value
    
    If Left(num, 3) <> "BGH" Or Mid(num, 4, 1) <> "-" Or _
       Mid(num, 7, 1) <> "/" Or Mid(num, 10, 1) <> "-" Then
    num = "BGH-" & Format(Date, "mm/yy") & "-1"
Else
    y = Mid(num, 8, 2)
    m = Mid(num, 5, 2)
    If y <> Format(Date, "yy") Or m <> Format(Date, "mm") Then
        num = "BGH-" & Format(Date, "mm/yy") & "-" & Split(num, "-")(2)
    Else
        num = Left(num, 10) & Split(num, "-")(2)
    End If
End If

TextBox1 = num
End Sub

Private Sub CommandButton1_Click()
    Dim num         As String
    Dim cell        As Range
    Dim m           As String, y As String
    Dim response    As Integer
    
    Set cell = Sheets("Sheet1").Range("A1")
    
    num = cell.Value
    
    If Left(num, 3) <> "BGH" Or Mid(num, 4, 1) <> "-" Or _
       Mid(num, 7, 1) <> "/" Or Mid(num, 10, 1) <> "-" Then
    num = "BGH-" & Format(Date, "mm/yy") & "-1"
Else
    y = Mid(num, 8, 2)
    m = Mid(num, 5, 2)
    If y <> Format(Date, "yy") Or m <> Format(Date, "mm") Then
        num = "BGH-" & Format(Date, "mm/yy") & "-" & Split(num, "-")(2)
    Else
        If Split(num, "-")(2) = Split(TextBox1.Text, "-")(2) Then
            response = MsgBox("Increment the number?", vbYesNo, "Confirm")
            If response = vbYes Then
                num = Left(num, 10) & Split(num, "-")(2) + 1
            Else
                Exit Sub
            End If
        Else
            num = Left(num, 10) & Split(num, "-")(2)
        End If
    End If
End If

cell.Value = num
TextBox1 = num
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

 

[MKLAQ]

everything is perfect except one thing .

if I press commandbutton1 will increment number in A1 ,TEXTBOX1 = BGH-02/23-2 after that press commandbutton2 then should return to old number A1 ,TEXTBOX1 = BGH-02/23-1

this should decrease incrementing from number has already increment after press commandbutton1 when press commandbutton2.

 

[rakesh seebaruth]

To achieve this, you can store the original value of the cell before incrementing it in a variable. Then, when you press CommandButton2, you can check if the value in the cell has changed, and if it has, you can set it back to the original value. Here's the updated code:

Private Sub UserForm_Activate()
    Dim num         As String
    Dim cell        As Range
    Dim m           As String, y As String
    Dim originalValue As String
    
    Set cell = Sheets("Sheet1").Range("A1")        'Set the sheet name and any available cell
    
    num = cell.Value
    originalValue = num
    
    If Left(num, 3) <> "BGH" Or Mid(num, 4, 1) <> "-" Or _
       Mid(num, 7, 1) <> "/" Or Mid(num, 10, 1) <> "-" Then
    num = "BGH-" & Format(Date, "mm/yy") & "-1"
Else
    y = Mid(num, 8, 2)
    m = Mid(num, 5, 2)
    If y <> Format(Date, "yy") Or m <> Format(Date, "mm") Then
        num = "BGH-" & Format(Date, "mm/yy") & "-" & Split(num, "-")(2) + 1
    Else
        num = Left(num, 10) & Split(num, "-")(2) + 1
    End If
End If

cell.Value = num
TextBox1 = num
End Sub

Private Sub CommandButton1_Click()
    Dim num         As String
    Dim cell        As Range
    
    Set cell = Sheets("Sheet1").Range("A1")
    
    num = cell.Value
    num = Left(num, 10) & Split(num, "-")(2) + 1
    
    cell.Value = num
    TextBox1 = num
End Sub

Private Sub CommandButton2_Click()
    Dim num         As String
    Dim cell        As Range
    
    Set cell = Sheets("Sheet1").Range("A1")
    
    If cell.Value <> originalValue Then
        cell.Value = originalValue
        TextBox1 = originalValue
    End If
End Sub

 

[MKLAQ]

thanks
not like post#2

it has become many problems !

if I run the userform and press commandbutton1 will should show copy to A1 . instead of copy to A1 =BGH-02-23-1 it will show BGH-02-23-24 and when close after copy to A1 will copy to A1= BGH-02-23-1 will start from the beginning when auto number without increment , it's wrong! , if I run form again and press commandbutton1 will not increment ( it will start from the beginning when auto number) instead of give A=BGH-02-23-2 will give A1 =BGH-02-23-1 it's wrong . shouldn't be( should increment A=BGH-02-23-2) and if I press command button2 when run the userform and show textbox1 =A=BGH-02-23-2 , then should decrease incrementing

A1 =BGH-02-23-1 but shows empty by clear the cell and textbox .
every thing is ok in post#2 except one thing just check post #3.