excel - 显示之前在用户表单上输入的数据并允许编辑数据

Question

我正在创建一个院子管理系统来跟踪很多停车位。

用户表单允许用户单击地图中打开的某个点（这将是命令按钮），然后单击更新，这将拉出另一个用户表单以填写有关前往该点的卡车的信息。

一旦用户填写了第二个用户表单，该点就会从绿色变为红色，以表明它已被填写。目前，无法编辑为地点输入的数据。

例如，我可能需要将卡车状态从空车更改为满车。

我认为最好的方法是

• 如果一个点是红色的（已满并且在用户表单中输入了信息），那么它将自动拉出第二个用户表单，并显示已经输入的数据，因此如果需要更改某些内容，他们只需在文本框中输入并再次按更新.
• 如果该位置是空的，它应该拉起第一个要求更新或清除该位置的用户窗体。

我不确定这是否可能或最有效的方式来完成我编辑已输入信息的目标，而无需再次填写整个用户表单。

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdClear_Click()
    ActiveCell.ClearContents
    ActiveCell.Interior.Color = vbGreen
    Unload Me
End Sub

Private Sub cmdUpdate_Click()
    UpdateInfo.Show
    Unload Me
End Sub

Private Sub CommandButton1_Click()
    UpdateInfo.Show
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    lblInfo = ActiveCell.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set rngBDock = Range("BX7:CO7")
Set rngBulk = Range("BZ21:CG21")
Set rngTransT = Range("BF17:BY17")
Set rngTransT1 = Range("BG21:BY21")
Set rngTDock = Range("BL7:BW7")
Set rngEDock = Range("CP7:CT7")
Set rngNDock = Range("CU7:DC7")
Set rngFence = Range("CQ13:CV13")
Set rngNSide = Range("CW13:DB13")
Set rngGEO = Range("BG28:DD28")
Set rngNight = Range("CH21:DD21")
Set rngNewT = Range("DK31:DK65")
Set rngNewTl = Range("DI31:DI65")
Set rngOff = Range("BN40:CL40")
Set rngOffl = Range("BN42:CL42")
Set rng = Union(rngBDock, rngBulk, rngTransT, rngTransT1, rngTDock, rngEDock, rngNDock, rngFence, rngNSide, rngGEO, rngNight, rngNewT, rngNewTl, rngOff, rngOffl)

If Not Intersect(Target, rng) Is Nothing Then
    CellInfo.Show
    'ActiveCell.Value = cellFill
    If Not IsEmpty(ActiveCell.Value) Then
        Call RealTimeTracker
    End If
End If

Private Sub cmdOkUpdate_Click()

Dim i As Integer, j As Integer

For i = 0 To lbxOption.ListCount - 1
    If lbxOption.Selected(i) Then j = j + 1
Next i
If j = 0 Then
    MsgBox "Please select an option. ", , "Warning"
    Unload Me
    UpdateInfo.Show
ElseIf j = 1 Then
    NoFill = False
End If

strBOL = txtBOL.Value
strID = txtID.Value
details = txtDet.Value
opt = lbxOption.Value
currtime = time()
today = Format(Now(), "MM/DD/YYYY")
emp = TextBox1.Value

With ActiveCell
    spot = .Offset(-1, 0)
    If Len(spot) = 0 Then
        spot = .Offset(1, 0)
    Else
        spot = spot
    End If
End With

If NoFill = True Then
    cellFill = ""
ElseIf NoFill = False Then
    With Sheet5
        .Range("A1").Value = "Time"
        .Range("B1").Value = "Date"
        .Range("C1").Value = "Location"
        .Range("D1").Value = "Category"
        .Range("E1").Value = "BOL"
        .Range("f1").Value = "Trailer #"
        .Range("g1").Value = "Details"
        .Range("H1").Value = "EE Name"
    
        .Range("A2").EntireRow.Insert
        .Range("A2").Value = currtime
        .Range("B2").Value = today
        .Range("C2").Value = spot
        .Range("D2").Value = opt
        .Range("E2").Value = strBOL
        .Range("F2").Value = strID
        .Range("G2").Value = details
        .Range("H2").Value = emp
    
        .Columns("A:H").AutoFit
    End With

    If Not IsEmpty(opt) Then
        cellFill = opt & " " & vbCrLf & "BOL (last 5 digits): " & strBOL & " " & vbCrLf & "Trailer # " & strID & " " & vbCrLf & details & "EE Name" & emp & " " & vbCrLf
        ActiveCell.Value = cellFill
        Call RealTimeTracker
    End If
    
End If

Unload Me
Sheet1.Activate

End Sub

标签： excelvbaedituserformcommandbutton