时间戳

首页 > 解决方案 > 将用户表单数据自动传输到下一个空白行

excel - 将用户表单数据自动传输到下一个空白行

问题描述

我试图找出我哪里出错但没有成功。

当我单击submit按钮时，我能够从用户窗体中捕获数据，并将此数据传输到 Excel 工作表的第一行。但是当我清除用户表单并尝试输入新数据并单击submit按钮时，它会将新数据传输到旧数据（第一行），它不会自动移动到第二行。

我已经检查了所有可能性，请帮助我在代码中出错的地方。

下面是提交按钮的代码。

Private Sub btnsave_Click()


Dim WB As Workbook
Dim ssheet as Worksheet

Set WB = Workbooks.Open("C:\Users\Gateways.xlsx")
Set ssheet = WB.Worksheets("Proc")

Dim nr As Integer
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 'keep a count of the ID for later use Loop

'Populate the new data values into the 'Data' worksheet 'Next ID number

ssheet.Cells(nr, 3) = Me.cmbls
ssheet.Cells(nr, 4) = Me.tbProject
ssheet.Cells(nr, 5) = Me.cmbbase
 If cmbbase.Value = "" Then
 
      MsgBox "You must complete base", vbCritical
      Exit Sub
 
  End If

ssheet.Cells(nr, 6) = Me.tbenovia
  If tbenovia.Value = "" Then
 
      MsgBox "You must complete Enovia no", vbCritical
      Exit Sub
 
  End If

ssheet.Cells(nr, 7) = Me.tbaxalant
ssheet.Cells(nr, 8) = Me.tbmaterial
ssheet.Cells(nr, 9) = Me.tbpart
ssheet.Cells(nr, 10) = Me.tbrequest
ssheet.Cells(nr, 11) = Me.cmbtask1
ssheet.Cells(nr, 12) = Me.cmbdetail1
ssheet.Cells(nr, 13) = Me.tbdetail2
ssheet.Cells(nr, 14) = Me.tbtools
ssheet.Cells(nr, 18) = Me.cmblocation
ssheet.Cells(nr, 19) = Me.tbadd
ssheet.Cells(nr, 15) = Me.cmburgent
ssheet.Cells(nr, 2) = CDate(DTPicker1)
ssheet.Cells(nr, 17) = CDate(DTPicker2)
ssheet.Cells(nr, 20) = CDate(DTPicker3)
If radioyes = True Then
    ssheet.Cells(nr, 16) = "Yes"
Else
    ssheet.Cells(nr, 16) = "No"
End If
ActiveWorkbook.Save

End Sub

标签： excelvbauserform

解决方案

推荐阅读