Change character when show in textbox on userform based on optionbutton

[MKLAQ]

Hello
I want changing the character when autonumbering in textbox based on optionbutton selection
the current code will show word "BGH-" & date based on this line

.Value = "BGH-" & Format$(Date, "mm-dd-1")

but now I want changing to

it depends on optionbutton name selection and the optionbutton name will match with sheet name where auto numbering for each sheet alone
when autonumbering in textbox will write the optionbutton name selection for instance SALES & NO 0004300 to become SALES NO 0004300 as in the picture above
and copy to sheet name is matched with optionbutton name selection in A1 cell and when click command button1 will increase the number like this

and if I click commandbutton2 then will decrease number like this

and if select PURCHASE will be like SALES for instance PURCHASE NO 0004300 but the the only different is OPTIONBUTTON NAME and should copy to sheet name into cell A1 is matched with optionbutton name
here is the whole codes

Option Explicit
Private flg As Boolean



Private Sub UserForm_Initialize()
    Dim x
    With Sheets("SALES").[a1]
        flg = .Value = ""
        If flg Then
            .Value = "BGH-" & Format$(Date, "mm-dd-1")
            Me.TextBox1 = .Value
        Else
            x = Split(Sheets("SALES").[a1], "-")
            x(UBound(x)) = x(UBound(x))
            .Value = Join(x, "-")
            Me.TextBox1 = .Value
        End If
        Me.Tag = .Value
    End With
    Me.CommandButton1.Enabled = Not flg
End Sub

Private Sub CommandButton1_Click()
    Dim x
    With Sheets("SALES").[a1]
        If Not flg Then
            x = Split(Sheets("SALES").[a1], "-")
            x(UBound(x)) = x(UBound(x)) + 1
            .Value = Join(x, "-")
            Me.TextBox1 = .Value
        End If
    End With
End Sub

Private Sub CommandButton2_Click()
    If Me.TextBox1 = Me.Tag Then Exit Sub
    Dim x
    With Sheets("SALES").[a1]
        If Not flg Then
            x = Split(Sheets("SALES").[a1], "-")
            x(UBound(x)) = x(UBound(x)) - 1
            .Value = Join(x, "-")
            Me.TextBox1 = .Value
        End If
    End With
End Sub

I hope from the experts give me some help.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Modyfying code to change showing way number in textbox on userform
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[MKLAQ]

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

no it's just here and attached link in your post , sorry!

 

[MKLAQ]

ok after many tries and tests and no answers from here or other forum !!! .I got the solution .
no need this

Private Sub UserForm_Initialize()
    Dim x
    With Sheets("SALES").[a1]
        flg = .Value = ""
        If flg Then
            .Value = "BGH-" & Format$(Date, "mm-dd-1")
            Me.TextBox1 = .Value
        Else
            x = Split(Sheets("SALES").[a1], "-")
            x(UBound(x)) = x(UBound(x))
            .Value = Join(x, "-")
            Me.TextBox1 = .Value
        End If
        Me.Tag = .Value
    End With
    Me.CommandButton1.Enabled = Not flg
End Sub

should be like this

Private Sub CommandButton1_Click()
  Dim x
  Dim i           As Long
    Dim sheetname   As String

    For i = 1 To 3
        With Me.Controls("OptionButton" & i)
            If .Value Then sheetname = .Caption: Exit For
        End With
    Next i

    With Worksheets(sheetname).[a1]
    'With Sheets("SALES").[a1]
        flg = .Value = ""
        If flg Then
            .Value = "NO 0004300"
            Me.TextBox1 = .Value
        Else
            x = Split(Worksheets(sheetname).[a1], "000")
            x(UBound(x)) = x(UBound(x)) + 1
            .Value = Join(x, "000")
            Me.TextBox1 = sheetname & " " & .Value
           
        End If
        Me.Tag = .Value
    End With
   ' Me.CommandButton1.Enabled = Not flg
End Sub

Private Sub CommandButton2_Click()
    If Me.TextBox1 = Me.Tag Then Exit Sub
    Dim x
    Dim i           As Long
    Dim sheetname   As String

    For i = 1 To 3
        With Me.Controls("OptionButton" & i)
            If .Value Then sheetname = .Caption: Exit For
        End With
    Next i

    With Worksheets(sheetname).[a1]
        If Not flg Then
            x = Split(Worksheets(sheetname).[a1], "000")
            x(UBound(x)) = x(UBound(x)) - 1
            .Value = Join(x, "000")
            Me.TextBox1 = sheetname & " " & .Value
        End If
    End With
End Sub

 

[MKLAQ]

here is the right code

Private Sub CommandButton1_Click()
  Dim x
  Dim i           As Long
    Dim sheetname   As String

    For i = 1 To 3
        With Me.Controls("OptionButton" & i)
            If .Value Then sheetname = .Caption: Exit For
        End With
    Next i

    With Worksheets(sheetname).[a1]
    'With Sheets("SALES").[a1]
        flg = .Value = ""
        If flg Then
            .Value = sheetname & " " & "NO 0004300"
            Me.TextBox1 = .Value
        Else
            x = Split(Worksheets(sheetname).[a1], "000")
            x(UBound(x)) = x(UBound(x)) + 1
            .Value = Join(x, "000")
            Me.TextBox1 = sheetname & " " & .Value
           
        End If
        Me.Tag = .Value
    End With
   ' Me.CommandButton1.Enabled = Not flg
End Sub

Private Sub CommandButton2_Click()
    If Me.TextBox1 = Me.Tag Then Exit Sub
    Dim x
    Dim i           As Long
    Dim sheetname   As String

    For i = 1 To 3
        With Me.Controls("OptionButton" & i)
            If .Value Then sheetname = .Caption: Exit For
        End With
    Next i

    With Worksheets(sheetname).[a1]
        If Not flg Then
            x = Split(Worksheets(sheetname).[a1], "000")
            x(UBound(x)) = x(UBound(x)) - 1
            .Value = Join(x, "000")
            Me.TextBox1 = sheetname & " " & .Value
        End If
    End With
End Sub

 

[MKLAQ]

just there is one problem when click commandbutton1 when increment the number will repeat writing option name twice
for instance when click commandbutton1 from the first time will be SALES NO 0004300 , but when click commandbutton1 again will be SALESSALES NO 0004301
the right way should be SALES NO 0004301, HOW I can fix it ?
I hope some body help .