excel - VBA Excel - 功能卡住

Question

我是新的 ti VBA，我想执行以下功能，希望有人可以帮助我

当我单击我的函数时，我需要设置一个从单元格 A2 开始的宏，会出现一个对话框，我可以在其中输入相关信息并将其插入到相关单元格中

将数据插入 3 个字段(B2、C2、D2)

然后选择 B3 我可以再次按下我的按钮再次做同样的变薄

继承人我的代码到目前为止

Dim StartCell As Integer

 Private Sub Cancel_Click()
     Unload GarageDimensions

End Sub

Private Sub LengthBox_Change()

If LengthBox.Value >= 15 Then
    MsgBox "Are you sure? You do realise it is just a garage!"
    Exit Sub
End If

End Sub
Private Sub Submit_Click()
'This code tells the text entered into the job reference textbox to be inserted _
into the first cell in the job reference column.

StartCell = Cells(1, 2)

Sheets("Data").Activate
If IsBlankStartCell Then


    ActiveCell(1, 1) = JobRef.Text
     ActiveCell.Offset(0, 1).Select

ActiveCell(1, 1) = LengthBox.Value
 ActiveCell.Offset(0, 1).Select

    ActiveCell(1, 1) = ListBox1.Value
    ActiveCell.Offset(0, 1).Select


    ActiveCell(1, 1) = ListBox1.Value * LengthBox.Value

Else
     Range("A1").End(xlDown).Offset(1, 0).Select

End If


Unload GarageDimensions

End Sub
Private Sub UserForm_Initialize()

With ListBox1
    .AddItem "2.2"
    .AddItem "2.8"
    .AddItem "3.4"
End With

     ListBox1.ListIndex = 0

End Sub

提前感谢您的回答

亚当

Answer 1

您不需要 Private Sub LengthBox_Change()事件。您可以设置文本框的最大字符LengthBox在 Design Mode或在 UserForm_Initialize()正如我在下面所做的那样。

此外，如果您硬编码 Startcell那么每次运行用户窗体时，数据将从 A2 开始，如果那里有任何数据，那么它将被覆盖。而是尝试找到可以写入的最后一个可用行。

顺便说一句，这是您正在尝试的( UNTESTED )吗？

Option Explicit

Dim StartCell As Integer
Dim ws As Worksheet

Private Sub UserForm_Initialize()
    Set ws = Sheets("Data")

    With ListBox1
        .AddItem "2.2"
        .AddItem "2.8"
        .AddItem "3.4"
        .ListIndex = 0
    End With

    LengthBox.MaxLength = 14
End Sub

Private Sub Submit_Click()
    With ws
        '~~> Find the first empty row to write
        StartCell = .Range("A" & Rows.Count).End(xlUp).Row + 1

        .Range("A" & StartCell).Value = Val(Trim(ListBox1.Value)) _
        * Val(Trim(LengthBox.Value))

        .Range("B" & StartCell).Value = JobRef.Text
        .Range("C" & StartCell).Value = LengthBox.Value
        .Range("D" & StartCell).Value = ListBox1.Value
    End With

    Unload Me
End Sub

Private Sub Cancel_Click()
    Set ws = Nothing
    Unload Me
End Sub