如何编写Excel VBA UserForm代码实现指定行录入及数据占用校验

修改思路 #

• 新增槽位号合法性校验，限制仅支持1-13号槽位操作
• 新增目标行数据占用校验，对应行已存在内容时直接拦截操作，不会覆盖原有数据
• 替换原有追加行逻辑，改为按用户选择的槽位号定位对应行填充数据
• 操作日志仍保留追加写入逻辑，无需校验占用

假设你表单中用于选择槽位号的控件为ComboBox7，刀具槽位数据从对应工作表的第2行开始依次存放1-13号槽位数据，修改后的完整代码如下：

Private Sub CommandButton1_Click()
    Dim wksDMG As Worksheet, wksHURCO As Worksheet
    Dim wksREC1 As Worksheet, wksREC2 As Worksheet
    Dim targetRow As Integer, slotNum As Integer
    Dim targetRng As Range
    
    Set wksDMG = Sheet4
    Set wksHURCO = Sheet3
    Set wksREC1 = Sheet2
    Set wksREC2 = Sheet5
    
    ' 槽位号格式、范围校验
    If Not IsNumeric(ComboBox7.Text) Then
        MsgBox "请选择有效槽位号", vbExclamation
        Exit Sub
    End If
    slotNum = CInt(ComboBox7.Text)
    If slotNum < 1 Or slotNum > 13 Then
        MsgBox "仅支持1-13号槽位操作", vbExclamation
        Exit Sub
    End If
    ' 槽位对应行计算：槽位1对应第2行，可根据实际表格起始行调整偏移量
    targetRow = slotNum + 1
    
    ' DMG机床选中逻辑
    If OptionButton1.Value = True Then
        ' 校验目标行是否已有数据
        If wksDMG.Cells(targetRow, "A").Value <> "" Then
            MsgBox "目标槽位已存在数据，操作已拦截", vbExclamation
            Exit Sub
        End If
        ' 填充DMG刀具表
        wksDMG.Cells(targetRow, "A").Value = ComboBox7.Text
        wksDMG.Cells(targetRow, "B").Value = ComboBox2.Text
        wksDMG.Cells(targetRow, "C").Value = ComboBox3.Text
        wksDMG.Cells(targetRow, "D").Value = ComboBox4.Text
        wksDMG.Cells(targetRow, "E").Value = ComboBox5.Text
        wksDMG.Cells(targetRow, "F").Value = ComboBox6.Text
        
        ' 填充DMG操作日志
        Set targetRng = wksREC1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        targetRng.Offset(0, 0).Value = ComboBox1.Text
        targetRng.Offset(0, 1).Value = TextBox1.Text
        targetRng.Offset(0, 2).Value = ComboBox7.Text
        targetRng.Offset(0, 3).Value = ComboBox2.Text
        targetRng.Offset(0, 4).Value = ComboBox3.Text
        targetRng.Offset(0, 5).Value = ComboBox4.Text
        targetRng.Offset(0, 6).Value = ComboBox5.Text
        targetRng.Offset(0, 7).Value = ComboBox6.Text
    End If
    
    ' HURCO机床选中逻辑
    If OptionButton2.Value = True Then
        ' 校验目标行是否已有数据
        If wksHURCO.Cells(targetRow, "A").Value <> "" Then
            MsgBox "目标槽位已存在数据，操作已拦截", vbExclamation
            Exit Sub
        End If
        ' 填充HURCO刀具表
        wksHURCO.Cells(targetRow, "A").Value = ComboBox7.Text
        wksHURCO.Cells(targetRow, "B").Value = ComboBox2.Text
        wksHURCO.Cells(targetRow, "C").Value = ComboBox3.Text
        wksHURCO.Cells(targetRow, "D").Value = ComboBox4.Text
        wksHURCO.Cells(targetRow, "E").Value = ComboBox5.Text
        wksHURCO.Cells(targetRow, "F").Value = ComboBox6.Text
        
        ' 填充HURCO操作日志
        Set targetRng = wksREC2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        targetRng.Offset(0, 0).Value = ComboBox1.Text
        targetRng.Offset(0, 1).Value = TextBox1.Text
        targetRng.Offset(0, 2).Value = ComboBox7.Text
        targetRng.Offset(0, 3).Value = ComboBox2.Text
        targetRng.Offset(0, 4).Value = ComboBox3.Text
        targetRng.Offset(0, 5).Value = ComboBox4.Text
        targetRng.Offset(0, 6).Value = ComboBox5.Text
        targetRng.Offset(0, 7).Value = ComboBox6.Text
    End If
End Sub

可调参数说明 #

• 若你的刀具表槽位1对应的行不是第2行，可修改targetRow = slotNum + 1中的偏移值，比如槽位1对应第3行就改为targetRow = slotNum + 2
• 若判断行是否被占用的依据不是A列，可修改wksDMG.Cells(targetRow, "A").Value <> ""中的列标识为对应列

内容的提问来源于stack exchange，提问作者Shane Cahalane