引用VBA中另一个用户窗体的过程生成标签中的值

Question

我正在创建一个UI，它可以从多组数据中插入、删除和显示信息。用户单击命令按钮启动程序，输入一些数据，然后userform1使用这些数据创建userform2。在使用userform2时，我希望用户能够根据在userform2上生成的txtbox中输入的信息更新userform和excel表上显示的信息。我遇到的问题是，当我试图从UserForm2引用从UserForm1为UserForm2生成的txtbox和标签时，它找不到它们。我也使用了for循环来命名它们，所以我认为它应该像Rag2.cation或UserForm2一样简单！Rag2.Capton.UserForm1:

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Private Sub CANCELBUTT_Click()
Unload Me
End Sub
Public Sub InsertBUTT_Click()
Dim check As Range
If LINBOX.Value <> "" And NOMBOX.Value <> "" Then
Set check = Columns("A:A").Find(What:=LINBOX.Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If check Is Nothing Then
Dim BlankRow As Long
BlankRow = Range("A65536").End(xlUp).Row + 1
Cells(BlankRow, 1).Value = LINBOX.Value
Cells(BlankRow, 2).Value = NOMBOX.Value
End If
Else
MsgBox "Both LIN and Nomenclature are required to insert a new LIN"
End If
Unload Me
UserForm1.Show
End Sub
Private Sub LINBOX_DropButtonClick()
Dim cl As Range
With ActiveSheet
For Each cl In Range([A3].CurrentRegion.Columns(1).Address)
If cl.Value <> "" Then
With LINBOX
.AddItem cl.Value
End With
End If
Next cl
End With
End Sub
Private Sub LINBOX_Change()
Dim Rng As Range
Dim cat As Integer
' Create a new Combo Box for the overhead categories
UserForm1.Controls.Add "Forms.ComboBox.1", "CATBOX", True
UserForm1!CATBOX.Visible = False
With UserForm1!CATBOX
.Height = 20
.Width = 150
.Left = 100
.Top = 40
End With
If LINBOX.Value <> "" Then
' Find the position of the LIN and display the corresponding Nomenclature
Set Rng = Columns("A:A").Find(What:=LINBOX.Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Rng Is Nothing Then
'Do nothing
Else
NOMBOX = ActiveSheet.Cells(Rng.Row, Rng.Column + 1).Value
UserForm1!CATBOX.Visible = True
' Fill the combo box with the Category Titles
With ActiveSheet
For cat = 1 To 999
col = ActiveSheet.Cells(1, cat).Value
If col <> "" Then
With UserForm1!CATBOX
.AddItem col
End With
End If
Next cat
End With
End If
End If
End Sub

Private Sub OKBUTT_Click()
Dim Rng As Range, SubRng As Range, subVal As Range, Rngr As Range
Dim Rw As Long, ColSt As Long, ColEnd As Long, i As Long, ScatNo As Long
Dim Rag As Object, Rag2 As Object, Rag3 As Object
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Label the new userform
If (UserForm1!LINBOX.Value = "") Then
MsgBox "Your Query wasn't found at this time"
ElseIf (UserForm1!NOMBOX.Value = "") Then
MsgBox "Your Query wasn't found at this time"
ElseIf (UserForm1!CATBOX.Value = "") Then
MsgBox "Your Query wasn't found at this time"
ElseIf (UserForm1!LINBOX.Value = "") And (UserForm1!NOMBOX.Value = "") And (UserForm1!CATBOX.Value = "") Then
MsgBox "Your Query wasn't found at this time"
Else
Set LINB = UserForm2.Controls.Add("Forms.Label.1", "LINB", True)
With LINB
.Caption = LINBOX.Value
.Left = 10
.Width = 50
.Top = 5
End With
Set NOMB = UserForm2.Controls.Add("Forms.Label.1", "NOMB", True)
With NOMB
.Caption = NOMBOX.Value
.Left = 10
.Width = 200
.Top = 15
End With

Set CATB = UserForm2.Controls.Add("Forms.Label.1", "CATB", True)
With CATB
.Caption = UserForm1!CATBOX.Value
.Left = 400
.Width = 200
.Top = 5
End With
''''''''''''''''''''''''''''''Merged Columns Start & End''''''''''''''''''''''''''''''''''''''''''''''''''
With ActiveSheet
'' Find the Category in the first row
Set Rng = .Rows(1).Find(What:=UserForm1!CATBOX.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'' If the category is not found, then it won't go through the code
If Rng Is Nothing Then Exit Sub
'' Determine the range of the merged columns
Set Rng = Rng.MergeArea
Set rngStart = Rng.Cells(1, 1)
Set rngEnd = Rng.Cells(Rng.Rows.Count, Rng.Columns.Count)
Rw = Rng.Row + Rng.Rows.Count
ColSt = Rng.Column
ColEnd = Rng.Column + Rng.Columns.Count - 1
Debug.Print Rw, ColSt, ColEnd
'' Use the merged column range to determine the length of the parser, then print the value
''      to the new userform
'''''''''''''''''''''''''''''''''''''''SUBCAT TITLE''''''''''''''''''''''''
Set Rng = .Range(.Cells(Rw, ColSt), .Cells(Rw, ColEnd))
ScatNo = 0
'' Establish a row counter
a = 0
For Each SubRng In Rng
If SubRng.Value <> "" Then
ScatNo = ScatNo + 1
'' Create a label an give it the subcatagory value
Set Rag = UserForm2.Controls.Add("Forms.Label.1", "Scat" & ScatNo)
Rag.Caption = SubRng.Value & ":"
'' Check if ScatNo is part of the new row or not, anything >7 is, anything <7 is not
If a > 0 Then
'' Establish when to create a new row (every 7th data set)
If (ScatNo Mod 7) = 0 Then
Rag.Left = 30
Rag.Width = 50
Rag.Top = 40 + (a * 20)
'' Make the following ScatNos part of the same row
Else
Rag.Top = 40 + (a * 20)
Rag.Left = ((ScatNo + 1) - (a * 7)) * 125 - 85
Rag.Width = 50
If ((ScatNo + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
'' If ScatNo is less than 7
ElseIf a = 0 Then
Rag.Left = ScatNo * 90
If Rag.Left = 90 Then
Rag.Left = 30
Rag.Top = 40
Rag.Width = 50
Else
Rag.Left = ScatNo * 125 - 85
Rag.Top = 40
Rag.Width = 50
If ((ScatNo + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
End If
End If
Next
''''''''''''''''''''''''''''''''''''''''''SUBCAT Values & TXTBOX'''''''''''''''''''''''''''''
Set Rngr = Columns("A:A").Find(What:=LINBOX.Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If (Rngr Is Nothing) Then
MsgBox "Your Query wasn't found at this time"
Else
Set subVal = .Range(.Cells(Rngr.Row, ColSt), .Cells(Rngr.Row, ColEnd))
scat = 0
'' Establish a row counter
a = 0
For Each vale In subVal
scat = scat + 1
Set Rag2 = UserForm2.Controls.Add("Forms.Label.1", "ScatV" & ScatNo)
Set Rag3 = UserForm2.Controls.Add("Forms.TextBox.1", "ScatUp" & ScatNo)
Rag2.Caption = vale.Value
'' Check if ScatNo is part of the new row or not, anything >7 is anything <7 is not
If a > 0 Then
'' Establish when to create a new row (every 7th data set)
If (scat Mod 7) = 0 Then
Rag2.BackColor = RGB(200, 200, 200)
Rag2.Left = 70
Rag3.Left = 90
Rag2.Width = 50
Rag3.Width = 50
Rag2.Top = 40 + (a * 20)
Rag3.Top = 40 + (a * 20)
'' Make the following Scats part of the same row
Else
Rag2.BackColor = RGB(200, 200, 200)
Rag2.Top = 40 + (a * 20)
Rag3.Top = 40 + (a * 20)
Rag2.Left = ((scat + 1) - (a * 7)) * 125 - 35
Rag3.Left = ((scat + 1) - (a * 7)) * 125 - 15
Rag2.Width = 50
Rag3.Width = 50
If ((scat + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
'' If Scat is less than 7
ElseIf a = 0 Then
Rag2.Left = scat * 125 - 25
Rag2.BackColor = RGB(200, 200, 200)
If Rag2.Left = 100 Then
Rag2.Left = 70
Rag3.Left = 90
Rag2.Top = 40
Rag3.Top = 40
Rag2.Width = 50
Rag3.Width = 50
Else
Rag2.Left = scat * 125 - 35
Rag3.Left = scat * 125 - 15
Rag2.Top = 40
Rag3.Top = 40
Rag2.Width = 50
Rag3.Width = 50
If ((scat + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
UserForm2.Show
End If
End With
End If
End Sub

用户表单2:

Private Sub CANCELBUTT_Click()
Unload Me
End Sub
Private Sub DELETEBUTT_Click()
Dim RngD As Range
Set RngD = Columns("A:A").Find(What:=UserForm1!LINBOX.Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Rows(RngD.Row).EntireRow.Delete
Unload Me
End Sub
Private Sub InsertBUTT_Click()
Dim Rng As Range, SubRng As Range, subVal As Range, Rngr As Range
Dim Rw As Long, ColSt As Long, ColEnd As Long, i As Long, ScatNo As Long
Dim Rag As Object, Rag2 As Object, Rag3 As Object
''''''''''''''''''''''''''''''Merged Columns Start & End''''''''''''''''''''''''''''''''''''''''''''''''''
With ActiveSheet
'' Find the Category in the first row
Set Rng = .Rows(1).Find(What:=UserForm1!CATBOX.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'' If the category is not found, then it won't go through the code
If Rng Is Nothing Then Exit Sub
'' Determine the range of the merged columns
Set Rng = Rng.MergeArea
Set rngStart = Rng.Cells(1, 1)
Set rngEnd = Rng.Cells(Rng.Rows.Count, Rng.Columns.Count)
Rw = Rng.Row + Rng.Rows.Count
ColSt = Rng.Column
ColEnd = Rng.Column + Rng.Columns.Count - 1
Debug.Print Rw, ColSt, ColEnd
'' Use the merged column range to determine the length of the parser, then print the value
''      to the new userform
''''''''''''''''''''''''''''''''''''''''''SUBCAT Values & TXTBOX'''''''''''''''''''''''''''''
Set Rngr = Columns("A:A").Find(What:=UserForm1!LINBOX.Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If (Rngr Is Nothing) Then
MsgBox "Your Query wasn't found at this time"
Else
Set subVal = .Range(.Cells(Rngr.Row, ColSt), .Cells(Rngr.Row, ColEnd))
scat = 0
'' Establish a row counter
a = 0
For Each vale In subVal
scat = scat + 1
If UserForm2!Rag3.Value <> "" Then
vale.Value = UserForm2!Rag3.Value
UserForm2!Rag2.Caption = UserForm2!Rag3.Value
UserForm2!Rag3.Value = ""
Else
UserForm2!Rag2.Caption = vale.Value
End If
'' Check if ScatNo is part of the new row or not, anything >7 is anything <7 is not
If a > 0 Then
'' Establish when to create a new row (every 7th data set)
If (scat Mod 7) = 0 Then
Rag2.BackColor = RGB(200, 200, 200)
Rag2.Left = 70
Rag3.Left = 90
Rag2.Width = 50
Rag3.Width = 50
Rag2.Top = 40 + (a * 20)
Rag3.Top = 40 + (a * 20)
'' Make the following Scats part of the same row
Else
Rag2.BackColor = RGB(200, 200, 200)
Rag2.Top = 40 + (a * 20)
Rag3.Top = 40 + (a * 20)
Rag2.Left = ((scat + 1) - (a * 7)) * 125 - 35
Rag3.Left = ((scat + 1) - (a * 7)) * 125 - 15
Rag2.Width = 50
Rag3.Width = 50
If ((scat + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
'' If Scat is less than 7
ElseIf a = 0 Then
Rag2.Left = scat * 125 - 25
Rag2.BackColor = RGB(200, 200, 200)
If Rag2.Left = 100 Then
Rag2.Left = 70
Rag3.Left = 90
Rag2.Top = 40
Rag3.Top = 40
Rag2.Width = 50
Rag3.Width = 50
Else
Rag2.Left = scat * 125 - 35
Rag3.Left = scat * 125 - 15
Rag2.Top = 40
Rag3.Top = 40
Rag2.Width = 50
Rag3.Width = 50
If ((scat + 1) Mod 7) = 0 Then
a = a + 1
End If
End If
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If
End With
End Sub

很明显，我对UserForm2感到沮丧，因为我提供的代码显示我试图简单地重写已经存在的标签和txtboxes，但即使这样也不起作用。

示例数据

上面的链接是我试图使用的数据的一个例子(对不起，我还没有代表嵌入图片)。感谢所有的帮助！

Answer 1

您似乎试图通过单击工作表上的按钮来创建UserForm1，通过单击UF1上的按钮创建UserForm2。然后希望UF2更新UF1，UF1更新工作表。您的代码没有做到这一点，因为工作表中既没有对象UF1，也没有对象UF2，实际上也没有对象。例如，

Dim Ws As Worksheet
Dim Uf1 as UserForm1
Dim Uf2 As UserForm2
Set Ws = ActiveSheet
Set Uf1 = New UserForm1
Set Uf2 = New UserForm2        ' actually to be declared only later in the code

现在，您可以使用Uf1.Show来显示UserForm1，使用Uf2.Hide来隐藏它。请记住，Unload Uf1会从内存中删除表单，而当它被隐藏时，情况并非如此。你可以在隐藏后再次显示它，也可以卸载它并创建一个新的实例，这取决于你想做什么

在使用Set New命令创建它之后，在Show之前，在隐藏它之后，但在Unload之前，您可以访问每个用户窗体对象的所有控件，如Uf1.TextBox1.Text以进行读取或写入。Ws.Cells(1,1).Value = Uf1.TextBox1.Text将文本框的内容传输到工作表的单元格A1。