vba - 记住最后一个条目的 VBA 用户表单文本框

Question

我是 VBA 新手，我有一个问题。我在excel中创建了一个用户表单。所以，我的问题是我想在文本框中输入值并提交，下次我启动用户表单时，它会记住用户表单中的最后一个条目。因此，例如，我有一个名为设施的文本框。我第一次输入值 UHN。一旦我关闭了用户表单。然后我再次打开用户窗体，值 UHN 已经填写，因此用户无需再次输入。因此，用户表单会记住文本框中的最后一个条目。

先感谢您！

我的vba代码如下：

Option Explicit

Private Sub AddlistButton_Click()
Dim emptyRow As Long

wsmenu.Activate

emptyRow = WorksheetFunction.CountA(Range("A:A"))

Me.Hide

wsfilms.Select

'Range("a1").End(xlDown).Offset(1, 0).Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
'Add facility name
ActiveCell.Value = FacilityBox
'Add service area
ActiveCell.Offset(0, 1).Value = ServiceAreaBox
'Add date
ActiveCell.Offset(0, 2).Value = DateBox
'Add time
ActiveCell.Offset(0, 3).Value = TimeBox.Value
'Add room
ActiveCell.Offset(0, 4).Value = RoomBox.Value

'Briefing Question 1
If Yes1.Value = True Then
ActiveCell.Offset(0, 5).Value = "Yes"
Else
ActiveCell.Offset(0, 5).Value = "No"
End If
'Timeout Question 1
If Yes2.Value = True Then
ActiveCell.Offset(0, 6).Value = "Yes"
Else
ActiveCell.Offset(0, 6).Value = "No"
End If
If Yes3.Value = True Then
'Debriefing Question 1
ActiveCell.Offset(0, 7).Value = "Yes"
Else
ActiveCell.Offset(0, 7).Value = "No"
End If
'Comment 1
ActiveCell.Offset(0, 8).Value = Comment1.Value

'Briefing Question 2
If Yes4.Value = True Then
ActiveCell.Offset(0, 9).Value = "Yes"
Else
ActiveCell.Offset(0, 9).Value = "No"
End If
'Timeout Question 2
If Yes5.Value = True Then
ActiveCell.Offset(0, 10).Value = "Yes"
Else
ActiveCell.Offset(0, 10).Value = "No"
End If
'Debriefing Question 2
If Yes6.Value = True Then
ActiveCell.Offset(0, 11).Value = "Yes"
Else
ActiveCell.Offset(0, 11).Value = "No"
End If
'Comment 2
ActiveCell.Offset(0, 12).Value = Comment2.Value

'Briefing Question 3
If Yes7.Value = True Then
ActiveCell.Offset(0, 13).Value = "Yes"
Else
ActiveCell.Offset(0, 13).Value = "No"
End If
'Timeout Question 3
If Yes8.Value = True Then
ActiveCell.Offset(0, 14).Value = "Yes"
Else
ActiveCell.Offset(0, 14).Value = "No"
End If
'Debriefing Question 3
If Yes9.Value = True Then
ActiveCell.Offset(0, 15).Value = "Yes"
Else
ActiveCell.Offset(0, 15).Value = "No"
End If
'Comment 3
ActiveCell.Offset(0, 16).Value = Comment3.Value

'Briefing Question 4
If Yes10.Value = True Then
ActiveCell.Offset(0, 17).Value = "Yes"
Else
ActiveCell.Offset(0, 17).Value = "No"
End If
'Timeout Question 4
If Yes11.Value = True Then
ActiveCell.Offset(0, 18).Value = "Yes"
Else
ActiveCell.Offset(0, 18).Value = "No"
End If
'Debriefing Question 4
If Yes12.Value = True Then
ActiveCell.Offset(0, 19).Value = "Yes"
Else
ActiveCell.Offset(0, 19).Value = "No"
End If
'Comment 4
ActiveCell.Offset(0, 20).Value = Comment4.Value

'Briefing Question 5
If Yes13.Value = True Then
ActiveCell.Offset(0, 21).Value = "Yes"
Else
ActiveCell.Offset(0, 21).Value = "No"
End If
'Timeout Question 5
If Yes14.Value = True Then
ActiveCell.Offset(0, 22).Value = "Yes"
Else
ActiveCell.Offset(0, 22).Value = "No"
End If
'Debriefing Question 5
If Yes15.Value = True Then
ActiveCell.Offset(0, 23).Value = "Yes"
Else
ActiveCell.Offset(0, 23).Value = "No"
End If
'Comment 5
ActiveCell.Offset(0, 24).Value = Comment5.Value

'Briefing Question 6
If Yes16.Value = True Then
ActiveCell.Offset(0, 25).Value = "Yes"
Else
ActiveCell.Offset(0, 25).Value = "No"
End If
'Timeout Question 6
If Yes17.Value = True Then
ActiveCell.Offset(0, 26).Value = "Yes"
Else
ActiveCell.Offset(0, 26).Value = "No"
End If
'Debriefing Question 6
If Yes18.Value = True Then
ActiveCell.Offset(0, 27).Value = "Yes"
Else
ActiveCell.Offset(0, 27).Value = "No"
End If
'Comment 6
ActiveCell.Offset(0, 28).Value = Comment6.Value

'Briefing Question 7
If Yes19.Value = True Then
ActiveCell.Offset(0, 29).Value = "Yes"
Else
ActiveCell.Offset(0, 29).Value = "No"
End If
'Timeout Question 7
If Yes20.Value = True Then
ActiveCell.Offset(0, 30).Value = "Yes"
Else
ActiveCell.Offset(0, 30).Value = "No"
End If
'Debriefing Question 7
If Yes21.Value = True Then
ActiveCell.Offset(0, 31).Value = "Yes"
Else
ActiveCell.Offset(0, 31).Value = "No"
End If
'Comment 7
ActiveCell.Offset(0, 32).Value = Comment7.Value

'Additional Question 8
If Yes22.Value = True Then
ActiveCell.Offset(0, 33).Value = "Yes"
Else
ActiveCell.Offset(0, 33).Value = "No"
End If
'Comment 8
ActiveCell.Offset(0, 34).Value = comment8.Value
'Completed by
ActiveCell.Offset(0, 35).Value = comment9.Value

MsgBox " Data was successfully added to row " & ActiveCell.Row
Unload Me

End Sub

Private Sub CancelButton_Click()
Me.Hide
'Unload FilmDetails
Unload Me
End Sub



Private Sub OptionButton1_Click()

End Sub

Private Sub UserForm_Initialize()

TimeBox.Text = Format(Time, "hh:mm AM/PM")
DateBox.Text = Format(Date, "dd mmm yyyy")
With ServiceAreaBox
.AddItem "Anaesthesiology"
.AddItem "Cardiac"
.AddItem "Endoscopy"
.AddItem "General"
.AddItem "Gynaecologic"
.AddItem "Neurosurgery"
.AddItem "Obstetrics"
.AddItem "Oncology"
.AddItem "Ophthalmic"
.AddItem "Oral and Maxillofacial and Dentistry"
.AddItem "Orthopaedic"
.AddItem "Otolaryngic (ENT)"
.AddItem "Plastic and Reconstructive"
.AddItem "Thoracic"
.AddItem "Transplant"
.AddItem "Urologic"
.AddItem "Vascular"
.AddItem "All Other "
End With


 End Sub

 Private Sub UserForm_Terminate()
 wsmenu.Select
 End Sub

 Private Sub Yes1_Click()

 End Sub

标签： vbatextboxuserform