Private Sub UserForm_Initialize() Dim intI As Integer For intI = 1 To [tblProduct].Rows().Count Step 1 cboProduct1.AddItem [tblProduct].Rows(intI).Columns(1).Value Next ' intI End Sub
Private Sub lblAdd_Click() Dim Product As Control Dim Quantity As Control Dim Price As Control Dim intTop As Integer intTop = 98 ' https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/add-method-microsoft-forms g_intCount = g_intCount + 1 Set Product = frmInput.Controls.Add("Forms.ComboBox.1", "cboProduct" & g_intCount + 1, True) With Product .Top = intTop + (g_intCount * 22) .Left = 6 End With For intI = 1 To [tblProduct].Rows().Count Step 1 Controls("cboProduct" & g_intCount + 1).AddItem [tblProduct].Rows(intI).Columns(1).Value Next 'intI Set Quantity = frmInput.Controls.Add("Forms.TextBox.1", "txtQuantity" & g_intCount + 1, True) With Quantity .Top = intTop + (g_intCount * 22) .Left = 84 End With Set Price = frmInput.Controls.Add("Forms.TextBox.1", "txtPrice" & g_intCount + 1, True) With Price .Top = intTop + (g_intCount * 22) .Left = 162 End With
Lưu ý: Cần khai báo biết Global Private g_intCount As Integer
Private Sub cmdSubmit_Click() Dim intHeader As Integer Dim intIDRows As Integer Dim intID As Integer Dim intNoRows As Integer intHeader = 8 intIDColumns = 2 If Len([tblOrder].Rows(1).Columns(1).Value) = 0 Then intID = 1 Else intNoRows = [tblOrder].Rows().Count intID = [tblOrder].Rows(intNoRows).Columns(1).Value + 1 End If For intI = 1 To 3 Step 1 Cells(intHeader + intNoRows + intI, intIDColumns).Value = intID Cells(intHeader + intNoRows + intI, intIDColumns + 1).Value = txtCustomer.Value Cells(intHeader + intNoRows + intI, intIDColumns + 2).Value = txtMobile.Value Cells(intHeader + intNoRows + intI, intIDColumns + 3).Value = txtEmail.Value Cells(intHeader + intNoRows + intI, intIDColumns + 4).Value = Controls("cboProduct" & intI).Value Cells(intHeader + intNoRows + intI, intIDColumns + 5).Value = Controls("txtQuantity" & intI).Value Cells(intHeader + intNoRows + intI, intIDColumns + 6).Value = Controls("txtPrice" & intI).Value Cells(intHeader + intNoRows + intI, intIDColumns + 7).Value = Val(Controls("txtQuantity" & intI).Value) * Val(Controls("txtPrice" & intI).Value) Next ' intI End Sub
Download file Dynamic_Input.xlsm
Đăng ký ngay Khóa học VBA Cơ bản đến Nâng cao để Ứng dụng hiệu quả Macro & VBA trong nâng cao hiệu xuất công việc