none
Excel 2010 - Userform - VBA How to stop ‘Job No’ from duplicating itself on next empty row RRS feed

  • Question

  • 

    Hi there
    Thank you in advance for taking the time to check this out.

    Objective:
    To prevent duplication of incident numbers in the datasheet, and format the job number with a prefix of Inc- at the beginning. I currently have the cell customization set to “Inc”General but that only inserts the prefix in the cells on the datasheet, but is not showing in the disabled textbox in the userform.

    The Problem
    I have a ‘Job Number’ that is generated each time the form is opened and when the ‘Save’ button is clicked the data from the form is transferred over

    The job number is generated from the previous entry +1 (auto incrementing the old fashioned way).
    The problem arises when the ‘Save’ button is pressed repeatedly, the same job number and data is duplicated on the datasheet.

    Is there some way to ensure that the number generated is unique, and if the ‘Save’ button is repeatedly pressed that it will just over-ride the existing information?

    The number format currently used is 20150003 (incremented by 1). But what I’d like to be displayed in the form is Inc- 20150003

    The following code is in the form_initialize procedure.

    Me.txtSEC_INC_No.Enabled = True
                               Dim irow As Long
                               Dim ws As ws_Incident_Details
                               Set ws = ws_Incident_Details
                               
                               'find last data row from database'
                               irow = ws.Cells(Rows.Count, 1) _
                               .End(xlUp).Row
                            
                               If ws.[a2].Value = "" Then
                                   Me.txtSEC_INC_No.Text = 0 ' If no value in Col A, it will return a 0
                                                            
                               Else
                                   Me.txtSEC_INC_No.Text = ws.Cells(irow, 1).Value + 1
                               End If

    I’d be really grateful if someone could help me out, or perhaps direct me to where I might find some coding that will achieve the result I am seeking.

    I have just uploaded the latest version My Sample form is linked to my Dropbox so you can see how it currently works (or doesn't work) .

    With much gratitude,
    TheShyButterfly


    Hope you have a terrific day, theShyButterfly

    Tuesday, March 31, 2015 8:03 AM

All replies

  • I have just uploaded the latest version My Sample form is linked to my Dropbox so you can see how it currently works (or doesn't work) .

    Please download this file, open it click the button to show the form, click the Next/Prev and play around, then have a look into the code of the form... just a few lines.

    https://dl.dropboxusercontent.com/u/35239054/Order%20Log%20Sample.xlsm

    The trick is to use the TAG property of the controls, you can do the same in your form.

    Andreas.

    Tuesday, March 31, 2015 3:24 PM
  • Hi Andreas,

    Thank you for the link, as always I appreciate any help you provide me :)

    I have had many looks at the file you have made available ... I've 'played with it' and tried to comprehend what bits of it might be useful and adaptable to my needs as mentioned in my post.

    Sorry, but, you are blowing away my tiny mind!

    I am striving to improve my VBA but ... I am far from anywhere near in understanding the code that you have in your file. I feel really bad in saying that, but I am not a pretender, and will acknowledge when I am over my head.

    I was thinking "simplified" :) ...

    • using something like a 'find' sub routine (which I haven't mastered yet), that would look at the datasheet, verify whether the Incident Number already exists, and if so, if there are any changes, that the textboxes that have been changed are overwritten the existing data in that same row, and if no changes have been made then the user can cancel/clear the fields of the form without losing any data in the sheet.

    Does what I am asking make sense to you?

    In playing with your form (basically just adding (New) some data - but not putting anything in the Job No field, expecting it to automatically generate the next incremental number on the next empty row .... but that didn't happen.

    When I hit Save (the New item), it saved the data in Column 201, instead of row 8 or 11. But I believe that because you have formulas in Column A (to row 200), that is why it is adding the information there .... and doesn't put any data in the Job No.

    So I don't know if it my application that is playing up, or that there is a glitch in the code. 

    I have been researching on the TAG property and I am still trying to come to terms with that one ... I have tried a mock form and followed the instructions and copied code, but it didn't really enlighten me as to what or how to take advantage of this property option.

    Cheers,

    ShyButterfly (Grasshopper)


    Hope you have a terrific day, theShyButterfly

    Tuesday, April 7, 2015 3:41 AM
  • I am striving to improve my VBA but ... I am far from anywhere near in understanding the code that you have in your file. I feel really bad in saying that, but I am not a pretender, and will acknowledge when I am over my head.

    I was thinking "simplified" :) ...

    Don't worry, also Rom wasn't build in a day. :-)

    I already answered the question about the duplication of the Job number in this thread:
    https://social.msdn.microsoft.com/Forums/de-DE/52f3c62f-b26e-4573-b7c2-8e7203786d7f/excel-2010-vba-userforms-vlookup-via-textbox-display-result-in-another-textbox?forum=exceldev

    So let us talk a little about the TAG property, thinking "simplified" and how to save the data:

    Most people start with code like this when they start there first Userform:

    Cells(MyRowNumber, 1) = txtBoxA
    Cells(MyRowNumber, 2) = txtBoxB
    etc. many many lines till
    Cells(MyRowNumber, 56) = txtBoxWhatEver

    And then, after Version 1.0, they realize that they also want to load data from a row into the form. And they copy all the lines and exchange the parts before and after the "=" like this:

    txtBoxA = Cells(MyRowNumber, 1)
    txtBoxB = Cells(MyRowNumber, 2)
    etc. many many lines till
    txtBoxWhatEver = Cells(MyRowNumber, 56)

    And maybe you have another 56 lines to "clear" the Userform, and maybe more lines... over 150 lines just for this... that is really tremendous. I will not be too harsh, if it works, then it's okay.

    But often many people struggle when they look into the code because, which column in the sheet is written by this line?
      Cells(MyRowNumber, 56) = txtBoxWhatEver

    I've often seen that people change the code to this:
      Range("A" & MyRowNumber) = txtBoxA
      Range("B" & MyRowNumber) = txtBoxB
    etc.  till
      Range("BD" & MyRowNumber) = txtBoxWhatEver
    which is more clearly, but you must revise 150 lines!

    And that is the point for the TAG property, which is in fact just a string. So when we write the column name ("A", "B", etc.) into the TAG property of a control, you can change the code to this:

      Range(txtBoxA.Tag & MyRowNumber) = txtBoxA
      Range(txtBoxB.Tag & MyRowNumber) = txtBoxB
    etc.

    And now the 1st trick, we can use a loop and visit all controls at once:

      Dim C As MSForms.Control
      For Each C In Me.Controls
        If C.Tag <> "" Then
          Range(C.Tag & MyRowNumber) = C
        End If
      Next

    And when we want to load data from a row into the form, it's the same, just the other direction:

      Dim C As MSForms.Control
      For Each C In Me.Controls
        If C.Tag <> "" Then
          C = Range(C.Tag & MyRowNumber)
        End If
      Next

    And to clear the form is also the same:

      Dim C As MSForms.Control
      For Each C In Me.Controls
        If C.Tag <> "" Then
          C = ""
        End If
      Next

    So we can remove over 150 lines and do the same with just the 18 lines above. Isn't that a simplification? Think about that for a while.

    :
    :
    :
    :
    :
    :

    Ready for the next trick? ;-)

    As the TAG property is readable and writeable we can use Sub UserForm_Initialize and save a lot of manual work:

    Private Sub UserForm_Initialize()
      Me.txtBoxA.Tag = "A"
      Me.txtBoxB.Tag = "B"
      'etc. till
      Me.txtBoxWhatEver.Tag = "BD"
    End Sub

    No time to waste, here comes the next one. ;-)

    In your file, you can have named ranges, but always have headings! And so we can get the column name e.g. from a named range:

      Me.txtBoxWhatEver.Tag = GetColumnName(Range("WhatEver"))

    Function GetColumnName(ByVal R As Range) As String
      Dim S As String
      S = R.Address(1, 0)
      GetColumnName = Left(S, InStr(S, "$") - 1)
    End Function

    Or you can use Range.Find and search for the header int the sheet and get the column name directly.

    The benefit is that your form works even when the user change the layout of the sheet!

    Simple as it gets (almost).

    Andreas.


    Tuesday, April 7, 2015 3:24 PM
  • Andreas, you are amazing, thank you for taking the time to explain it to me.

    There alot of grey matter on the outside as well as on the inside (I'm no spring chicken! LOL).

    I will need to digest this ... I'm at work now, so I will give it a more concentrative effort when I get home :)

    I understand the logic of it (it makes sense to reduce the amount of code, and to make it more meaningful to yourself and others who may at a later stage need to modify it.

    It's just this is the first instance that I have seen any coding with the 'TAG' and it threw me.

    I am trying not to waste your time, and appreciate that you are doing this voluntarily, as are all those who make the effort to help us who struggle, thank you for sharing your wisdom :) 

    I'll let you know how I get along :)

    Most gratefully yours,

    Shybutterfly (grasshopper)


    Hope you have a terrific day, theShyButterfly

    Wednesday, April 8, 2015 3:58 AM
  • Hi Andreas,

    Sorry for the delayed response, just touching base with you to let you know I am still trying to conquer this :) - so my lack of communication is not because I have forgotten or don't care, it's just a matter of comprehension and then application/implementation that takes time (for me anyhow).

    I'll report back success/failure or errors shortly.

    Cheers,

    ShyButterfly (from Grasshopper to turtle :) )


    Hope you have a terrific day, theShyButterfly

    Monday, April 13, 2015 4:12 AM