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

auto number in textbox and specific cell

0

hello  

actully  I  try  make  a macro    I  know  this  is  not  logic  what  I'm writing  about my code  ,  I  just would  reach   my  idea  maybe  some  body  understand  me  , indeed  I  have  no  experience  desighns the codes ,   so  I  would  when  I  run  the  userform     shows  the  number in textbox1  and  in  the  same  time   in e2   i put  the  number   it  should be like  this 

trade/1000  every  time I  run the  userform   then incerment  the  value  trade/1001

,trade1002 ,..... and so  on 

Private Sub UserForm_Initialize()
Dim ss As String
Dim r As Integer
ss = "trade"
r = 1000
Me.TextBox1.Value = ss & "/" & r
  Range("e2").Value = Me.TextBox1.Value + 1
End Sub

thanks in advance

Answer
Discuss

Answers

0
Selected Answer

Hi Leap and welcome to the Forum.

Please try the modified version of your file (attached). I've done what I think you want- update the numeric part of the invoice number (each time the button is pressed) and put it back into cell E2 (but see below). 

The changes to your code are in bold below.

Revision 1: Added an initial If Then/Else decision (in bold) to allow for E2 being blank initially (as per discussion point from user).

When the userform is called again, instead of setting r to 1000, I've incremented the number by one by 1) extracting the last four characters of the contents of what's in E2 but using the RIGHT function and 2) adding 1.

In the next line, you need to change the Text property of TextBox1 (not the Value property). I've also taken the formal step of converting the integer r to a srtring (using the Str() function).

Then E2 's value can be set to that string, the Text in the Textbox. Here's the code:

Private Sub UserForm_Initialize()
Dim ss As String
Dim r As Integer
ss = "trade"

If Range("E2") = "" Then 'Check if this is the first user of the form
    r = 1000
  Else
  r = Right(Range("e2").Value, 4) + 1 ' not, add 1 to last value
End If

Me.TextBox1.Text = ss & "/" & Str(r)
Range("E2").Value = Me.TextBox1.Text 'write this value to sheet(and later write other default values)

End Sub

I've also added a Close button to your Userform (and this is the code behind it- where Unload will allow the Initialize process to be repeated next time you click the button on your worksheet).Here's that code:

Private Sub CommandButton1_Click()
Me.Hide
Unload Me

End Sub

I've put the value in E2 in each time (as you asked) but I guess you will really want to put 1001, 1002 etc. in cells below E2. If so, take a look at Don's tutorial about finding the last used row in a given column Get the Last Row using VBA in Excel

Hope this helps.Good luck!

Discuss

Discussion

thanks  John     but  you   seem   misunderstood   or  I  don't   explain  clearly    I put   the  number   in  e2   based   on  textbox    in the first   time  the  cell e2 should  be empty   and  when   I  run  userform     should   show   in  e2    based   what  show   in  textbox   I  hope   to  understand  me 
leap (rep: 46) Jan 25, '21 at 2:43 pm
Leap, sorry but I don't understand.

1) If E2 is empty to begin with, do you just want to put a default value in the textbox (e.g.  "Trade/1000") and put that in E2?

2) Or do you want to allow the user to overwrite it (e.g. "Domestic/500") and then put that in E2?

3) On the second (and later) use of the User form, do you want Trade/1001 (1002, 1003 etc.) to appear in the textbox then moved to E2 (like my code does)?

4) Have you considered using a simpler Inputbox (rather than a Userform)? 

Please try to explain clearly what you want.
John_Ru (rep: 6102) Jan 25, '21 at 3:25 pm
about   point  1   yes  this  is  what  i  want    and   after  that   should  use   in  point  3   incerment   1001 ,1002, 1003... etc  every  time  runs  the  userform  about  point  4   the  reason     use  userform   I  want   to create   form  of  invoice     and   copy   all  of  data     from  userform  to  sheet   I don'  think  this  is  a good  idea  to  use  inputbox     may be  making  the  program  is  ery  heavy  when  contain userform   and  input  box  , you  know   more  than  me    
leap (rep: 46) Jan 25, '21 at 3:58 pm
Thanks Leap.

Please see my revised Answer (Revision 1 note) and changed file/ code.

On point 4, you're right (given what you ain to do). Hope this is what you want (or you can now see how to change things in your code)
John_Ru (rep: 6102) Jan 25, '21 at 4:18 pm
very  impressive  !  many   thanks   for  your   code 
leap (rep: 46) Jan 25, '21 at 4:48 pm
Thanks Leap. Hope you enjoy learning VBA. Don has created some great tutorials on this site- they should help you a lot.
John_Ru (rep: 6102) Jan 25, '21 at 4:55 pm
Add to Discussion
0

Hello Leopard,

You only need to use the Initialize or Activate event to set the TextBox because you didn't create the form properly. It's better to create the form when you click the button and set the text box before you show it.

Private Sub CommandButton1_Click()
    ' 161

    Const NumFmt        As String = """Trade/ """ & "0000"
    Const BaseNum       As Integer = 1000
    Const Target        As String = "E2"

    Dim Form        As UserForm1            ' better to name the form
    Dim InvNum      As Long

    InvNum = Val(ActiveSheet.Range(Target).Value) + 1
    If InvNum < BaseNum Then InvNum = InvNum + BaseNum

    Set Form = New UserForm1                ' Initialize event fires now
    With Form
        .TextBox1.Value = Format(InvNum, NumFmt)
        .Show                               ' Activate event fires now

        ' code continues here when the form is closed:-
        If Val(.Tag) = True Then            ' check if 'OK' was pressed
            With ActiveSheet.Range(Target)
                .Value = InvNum
                .NumberFormat = NumFmt
            End With
        End If
    End With

    Unload Form
    Set Form = Nothing
End Sub

It's important to understand that the code gives control to the form with the Show command and until the form is hidden. After that it continues to run with the command after Show. At that time all of the form is still in memory. Only the display is hidden. So, you can take the value of the invoice number and write it to the sheet at that time.

The advantage of this is that you can avoid writing the number to the sheet if you cancelled the invoice. So, you need some message to send back to the calling procedure to tell it if it's OK to write the number to the sheet or not. To send this message you create a command button on the form. I called it CmdOK. When this button is clicked the code below runs. Note that it must be installed in Userform1's code module.

Private Sub CmdOK_Click()
    ' 161

    With Me
        .Tag = -1
        .Hide
    End With
End Sub

As you see, the code does only two things. It assigns a value of -1 to the form's Tag property, and then hides the form. Of course, once the form is hidden the code resumes below the Show command. There the Tag property's value is checked. If it's True (meaning -1) the new invoice number is written to the form.

Observe that the cell never gets the value of "Trade /1001". Its value is just 1001 and the word "Trade/ " is added in the number format, which is the same for the cell and the text box.

I attach a workbook in which all of the above is implemented.

To stop the code from failing when exited irregularly the following procedure must be added to the form's code module.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' 161

    If CloseMode = vbFormControlMenu Then
        Cancel = -1
        With Me
            .Tag = vbNullString
            .Hide
        End With
    End If
End Sub
Discuss

Discussion

@variatus  thanks  for  your  code  and   explaination   but  if   i  don't  press ok   and  close   the   userform  it  gives  error  
leap (rep: 46) Jan 26, '21 at 10:55 am
Indeed that is so. My apologies. I have amended my answer to take care of that.
Variatus (rep: 4889) Jan 26, '21 at 9:22 pm
@variatus     thanks   for  another solution 
leap (rep: 46) Jan 27, '21 at 1:52 pm
I'm glad we got it to work. Please, as a guide to others, mark the answer as "Selected".
Variatus (rep: 4889) Jan 27, '21 at 6:57 pm
Add to Discussion


Answer the Question

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