Selected Answer
Hi Leap
In the attached revised file. I've reused your code (moving it sometimes).
Firstly at the top level I've declared a new variable Opt to store the button selection (if any):
Dim Opt As String
The form is launched with nothing in the text box (awaiting a button selection) and the Copy... button is grey and disabled:
Private Sub UserForm_Initialize()
'reset button selection variable
Opt = ""
' write to form
Me.TextBox1.Text = Opt
' disable button and set to grey
With Me.CommandButton2
.BackColor = -2147483633
.Enabled = False
End With
End Sub
When a button is clicked, its caption is passed to another sub; (e.g. for OptionButton1):
Private Sub OptionButton1_Click()
'Pass button caption to sub
ShowNextInv (Me.ActiveControl.Caption)
End Sub
(The same line of code is used for the other 2 option buttons).
That sub ShowNextInv picks the right worksheet (provided your option button captions match- I didn't cycle through sheets altering the captions when initialising the form), adds the prefix to the next invoice number and enables the Copy... button (making it green). Additions to your (moved) code are in bold:
Private Sub ShowNextInv(Capt As String)
Dim ss As String, Nxt As String, LstRw As Long
' enable and set button to light green
With Me.CommandButton2
.BackColor = 12648384
.Enabled = True
End With
' work on the sheet based on the button
With Worksheets(Capt)
' set prefix for invoice number
ss = Capt & " trade/ "
' find last used row in column E
LstRw = .Range("E" & .Rows.Count).End(xlUp).Row
' create next invoice number
If LstRw = 1 Then
Nxt = ss & 1000
Else
Nxt = ss & Right(.Cells(LstRw, 5).Value, 4) + 1
End If
End With
' write to form
Me.TextBox1.Text = Nxt
' save button selection for later
Opt = Capt
End Sub
If you click the Copy... button, it runs this:
Private Sub CommandButton2_Click()
Dim LstRw As Long
If Opt = "" Then Exit Sub
With Worksheets(Opt)
' find last used row in column E
LstRw = .Range("E" & .Rows.Count).End(xlUp).Row
' write to cell
.Cells(LstRw + 1, 5).Value = Me.TextBox1.Text
End With
' reset the form
Call UserForm_Initialize
End Sub
Finally if you click the Close button, this runs:
Private Sub CommandButton1_Click()
'reset button colour
Me.OptionButton1.BackColor = -2147483633
Me.Hide
Unload Me
End Sub
Hope this makes sense and fixes your problem.,
