Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

incerement number in last cell for column based on userform

0

Hello

I  want  when  press button COPY , then  should  copy  to  last  cell in column E  from textbox1 , if  press button CANCEL or CLOSE   ,then  should  not copy  to last  cell in column E .  from  the  first  time when  run  the  userform  will  create number  trade/ 1000 in textbox1 ,  if  I  press button  COPY , then  will copy  to cell E2  and  if  I  run again the  form will incerement based on cell E2 , then  will show  trade/ 1001 in  textbox1    if  I  press button  COPY , then  will copy  to cell E3 and  so  on .  from  the  first  time when  run  the  userform  will  create number  trade/ 1000 in textbox1   if I press button CANCEL or CLOSE    the  userform , then shouldn't  copy  to  column E  and  become E2 is  empty and if  I  press button COPY  will  create number  trade/ 1000  in textbox1 and  copy  to  sheet in E2  for  column E  and  if  I  run again the  form will incerement trade/ 1001 in textbox1 , if I press button CANCEL or CLOSE   the  userform , then shouldn't  copy  to  column E  and  become E3 is  empty .

I  hope  this  is  not  confused.

excel
MS Office Version: 2016
posted by: leap (rep: 46) on Wed Oct 26, 2022 at 4:55 am
Answer
Discuss

Discussion

Hi Leap

Have you attached the correct file? I ask since the UserForm sub CommandButton2_Click() seems to make little attempt to do what your question asks.
John_Ru (rep: 6142) Oct 26, '22 at 4:41 pm
Hi John 
I  attached orginal file  with just  addion  new button COPY.
leap (rep: 46) Oct 26, '22 at 5:00 pm
I think you missed my point- are you really asking one of us to code the button for you (without trying to do so yourself)? If so, I have little time in the next 3 days so doubt I can help this time. 
John_Ru (rep: 6142) Oct 26, '22 at 5:12 pm
truly  I  don't  understand  you   which  button  talk  about  if  talk  about   COPY     the  first  file  is  really existed  some  lines of  codes  what I'm  trying  to  do  what  I  ask  for  it . so doubt I can help this time.  this  is not  problem .I'm  still search  for  way . if  you can  help, you're welocme  , if  not  thanks  for  your  time 
leap (rep: 46) Oct 27, '22 at 2:45 am
Leap. Please see my Answer.

Also, kindly correct the question title to read "Increment..."
John_Ru (rep: 6142) Oct 27, '22 at 8:52 am
Add to Discussion

Answers

0
Selected Answer

Leap

In the attached file, I made your Initialize and CommandButton2_Click macros very similar (as below). The difference (in bold) is that the Click macro writes to the cell too.

Private Sub CommandButton2_Click()

Dim ss As String, Nxt As String, LstRw As Long

' set prefix for invoice number
ss = "trade/ "
' find last used row in column D
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
' write to form
Me.TextBox1 = Nxt
' write to cell
Cells(LstRw + 1, 5).Value = Nxt

End Sub


Private Sub UserForm_Initialize()

Dim ss As String, Nxt As String, LstRw As Long

' set prefix for invoice number
ss = "trade/ "
' find last used row in column D
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
' write to form
Me.TextBox1 = Nxt

End Sub

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Thu Oct 27, 2022 at 8:52 am
Discuss

Discussion

truly  I  thought  will use UserForm_QueryClose  and  put  some  complicated  procedures  in this event when  close  the  userform  and  doesn't  inecerement in this  case .
 it  works  as  what  I want .
thanks  very  much  for  your  help .
leap (rep: 46) Oct 27, '22 at 10:01 am
Glad it worked for you. Thanks for selecting my Answer, Leap
John_Ru (rep: 6142) Oct 27, '22 at 10:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login