Excel VBA 用户窗体，每次调用窗体时都需要创建新 ID 并将其保存在添加/保存按钮上单击

Excel VBA UserForm, Need to create new ID every time form is called and save it on Add/Save button click

提问by AlexB

I have created a simple UserForm to enter new customer details to the Customer List in the spreadsheet, form works fine except for one little thing, which is New Customer ID.

我创建了一个简单的用户表单，用于在电子表格中的客户列表中输入新客户详细信息，表单工作正常，除了一个小东西，即新客户 ID。

Basically what I need this for to do is once form is opened/called new customer ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.

基本上我需要做的是一旦打开表单/需要创建新的客户 ID，这可能是 Alfa 数字字符集，如 AA-01234、AA-01235、AA-01236 等。

Also, is there a way of posting newly added Customer ID in the MsgBoxalong with MsgBox "One record added to Customers List. New Customer ID is "

另外，是否有一种方法可以将新添加的客户 IDMsgBox与MsgBox "One record added to Customers List. New Customer ID is "

All of my attempts to create this are failing and causing errors, which I really cannot figure out since I am new to VBA and had never used it until now.

我创建它的所有尝试都失败并导致错误，我真的无法弄清楚，因为我是 VBA 的新手并且直到现在才使用它。

Please help me a little.

请帮我一点。

Here is my code, Customer ID is TextBox1.

这是我的代码，客户 ID 是 TextBox1。

Thanks in advance

提前致谢

Private Sub UserForm_Activate()
Dim iRow As Long
Dim ws As Worksheet

    Set ws = Worksheets("Customers")

    RefNo.Enabled = True
    'find last data row from database
    iRow = ws.Cells(Rows.Count, 8).End(xlUp).Row

    If ws.Range("A" & iRow).Value = "" Then
        RefNo.Text = "TAS1"
        ws.Range("A" & iRow).Value = RefNo
    Else
        RefNo.Text = "TAS" & Val(Mid(ws.Cells(iRow, 1).Value, 4)) + 1
        ws.Range("A" & iRow + 1).Value = RefNo
    End If
    TextBox1.Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
End Sub

Private Sub Addreccord_Click()
    Dim LastRow As Object

    Set LastRow = Range("Customers!A65536").End(xlUp)

    LastRow.Offset(1, 0).Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text
    LastRow.Offset(1, 3).Value = TextBox4.Text
    LastRow.Offset(1, 4).Value = TextBox5.Text
    LastRow.Offset(1, 5).Value = TextBox6.Text
    LastRow.Offset(1, 6).Value = TextBox7.Text
    LastRow.Offset(1, 7).Value = TextBox8.Text
    LastRow.Offset(1, 8).Value = TextBox9.Text
    LastRow.Offset(1, 9).Value = TextBox10.Text
    LastRow.Offset(1, 10).Value = TextBox11.Text

    MsgBox "One record added to Customers List"

    response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

    If response = vbYes Then
        TextBox1.Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        TextBox8.Text = ""
        TextBox9.Text = ""
        TextBox10.Text = ""
        TextBox11.Text = ""

        TextBox2.SetFocus

    Else
       Unload Me
    End If

End Sub
Private Sub Exitform_Click()
    End
End Sub
Sub ClearFields_Click()
    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
        End Select
    Next ctrl
End Sub

采纳答案by Brock Gion

Step 1: Create a Named Range

步骤 1：创建命名范围

To simplify your code, I would create a NamedRange called CustomerIDList.

为了简化您的代码，我将创建一个名为CustomerIDList的 NamedRange 。

So, instead of saying:

所以，而不是说：

    Range("Customers!A8:A65536") 

you'd be able to put:

你可以把：

    Range("CustomerIDList")

In this picture the rows are hidden, but notice how the range selected is called CustomerIDList.

在这张图片中，行是隐藏的，但请注意所选范围如何称为CustomerIDList。

Then, when the UserForm is activated, it will use a function to return AA-66763 (one more than the max value in CustomerIDList)

然后，当 UserForm 被激活时，它会使用一个函数返回 AA-66763（比CustomerIDList 中的最大值多一个）

Step 2: Use a custom function to split on hyphen

第 2 步：使用自定义函数在连字符上拆分

RegEx (Regular Expressions) could give you full control, but here's a solution using your own defined function.

RegEx（正则表达式）可以让您完全控制，但这里有一个使用您自己定义的函数的解决方案。

This function relies on Excel's built-in FIND() function and uses VBA's Right() and Len() functions.

此函数依赖于 Excel 的内置 FIND() 函数并使用 VBA 的 Right() 和 Len() 函数。

I'm assuming the following:

我假设如下：

• your Worksheet is named Customers
• Range("A8") is where your values start (same as saying row 8, column 1)
• Values in Column A are contiguous
• Format of Values is AA-01234

• 您的工作表名为“客户”
• Range("A8") 是您的值开始的地方（与第 8 行第 1 列相同）
• A 列中的值是连续的
• 值的格式为AA-01234

For this function to work, it requires five inputs (i.e. arguments):

要使此函数工作，它需要五个输入（即参数）：

• sheetName
• nameOfRange
• rowStart
• colStart

• delimeterToSplitOn

  CustomerIDListis a name I chose for the Range, but it could be anything you want.

  Private Sub UserForm_Activate()
  
      TextBox1.Value = "AA-" & GetCustomerId("Customers", "CustomerIDList", 8, 1, "-")
  
  End Sub
  

• 表名
• 范围名称
• 行开始
• 开始

• delimeterToSplitOn

  CustomerIDList是我为 Range 选择的名称，但它可以是您想要的任何名称。

  Private Sub UserForm_Activate()
  
      TextBox1.Value = "AA-" & GetCustomerId("Customers", "CustomerIDList", 8, 1, "-")
  
  End Sub
  

    Public Function GetCustomerId( ByVal sheetName As String, ByVal nameOfRange As String, ByVal rowStart As Long, ByVal colStart As Long, ByVal delimeterToSplitOn) As Long

       'Just creating a Range object, assigning it all the values of CustomerID, and naming the Range
        Dim r1 As Range

        Set r1 = Range(Cells(rowStart, colStart), Cells(rowStart, colStart).End(xlDown))

        With ActiveWorkbook.Names

            .Add Name:=nameOfRange, RefersTo:="=" & sheetName & "!" & r1.Address & ""

        End With


        'This array holds all original AlphaNumeric Values
        Dim AlphaNumericArr() As Variant

        'This array will hold only the Numeric Values
        Dim NumericArr() As Variant


        'Populate Array with all the values
        AlphaNumericArr = Range(nameOfRange)

        'Resize NumericArr to match the size of AlphaNumeric
        'Notice, this is an index of 1 because row numbers start at 1
        ReDim NumericArr(1 To UBound(AlphaNumericArr, 1))

        Dim R As Long
        Dim C As Long
        For R = 1 To UBound(AlphaNumericArr, 1) ' First array dimension is rows.
            For C = 1 To UBound(AlphaNumericArr, 2) ' Second array dimension is columns.

                'Uses one worksheet function: FIND()
                'Uses two VBA functions: Right() & Len()

                'Taking the original value (i.e. AA-123980), splitting on the hyphen, and assigning remaining right portion to the NumericArr
                NumericArr(R) = Right(AlphaNumericArr(R, C), Len(AlphaNumericArr(R, C)) - Application.WorksheetFunction.Find(delimeterToSplitOn, (AlphaNumericArr(R, C))))

            Next C
        Next R



        'Now that have an array of all Numeric Values, find the max value and store in variable
        Dim maxValue As Long
        Dim i As Long

        maxValue = NumericArr(1)

        For i = 1 To UBound(NumericArr)

            If maxValue < NumericArr(i) Then
            maxValue = NumericArr(i)
            End If

        Next

        'Add 1 to maxValue because it will show in UserForm for a new CustomerID
        GetCustomerId = maxValue + 1


    End Function

UPDATE:

更新：

This is how you would change your existing code so that it works. Notice, the MsgBox now shows the id, too.

这是您更改现有代码以使其正常工作的方式。注意，MsgBox 现在也显示了 id。

    Private Sub Addreccord_Click()
        Dim LastRow As Object

        Set LastRow = Range("CustomerIDList").End(xlDown)

        LastRow.Offset(1, 0).Value = "AA-" & GetCustomerId("Customers", "CustomerIDList", 8, 1, "-")

        LastRow.Offset(1, 1).Value = TextBox2.Text
        LastRow.Offset(1, 2).Value = TextBox3.Text
        LastRow.Offset(1, 3).Value = TextBox4.Text
        LastRow.Offset(1, 4).Value = TextBox5.Text
        LastRow.Offset(1, 5).Value = TextBox6.Text
        LastRow.Offset(1, 6).Value = TextBox7.Text
        LastRow.Offset(1, 7).Value = TextBox8.Text
        LastRow.Offset(1, 8).Value = TextBox9.Text
        LastRow.Offset(1, 9).Value = TextBox10.Text
        LastRow.Offset(1, 10).Value = TextBox11.Text

        MsgBox "One record added to Customers List.  New Customer ID is " & LastRow.Offset(1, 0).Value

回答by hatted

I shortened your code. I think your problem is in the Addreccord_Click()sub. Does this work for you?

我缩短了你的代码。我认为你的问题出在Addreccord_Click()潜艇上。这对你有用吗？

Private Sub CommandButton1_Click()
Dim LastRow As Range

    Set LastRow = Range("A8").End(xlDown)
    LastRow.Offset(1, 0).Value = WorksheetFunction.Max(Range("Customers!A8:A65536")) + 1
    LastRow.Offset(1, 1).Value = TextBox1.Text

    MsgBox "One record added to Customers List"

    response = MsgBox("Do you want to enter another record?", _
              vbYesNo)

    If response = vbYes Then

    Else
        Unload Me
    End If
End Sub