Excel VBA -具有添加/删除对象功能的动态用户表单
Excel VBA -具有添加/删除对象功能的动态用户表单
提问于 2015-11-09 16:50:49
我正在尝试创建一个用户表单，这样人们就可以随心所欲地放置任意数量的请求，并能够删除他们不再需要的请求以及其他功能。在添加-删除-添加函数之后，在删除动态创建的对象时，我遇到了问题。
下面的代码有一个添加到userform中的对象的片段，以及对userform和已经嵌入在userform中的对象的维度更改。以下不包括其他定义的部分。
Dim RemoveButtonArray() As New Class_RemoveRequest
For i = Last To Last
Set AddRemoveButton = GenPurchaseRequest.Controls.Add("Forms.Image.1", "btnRemove" & ObjID)
With AddRemoveButton
'properties
End With
Set AddRemoveLabel = GenPurchaseRequest.Controls.Add("Forms.Label.1", "lblRemove" & ObjID)
With AddRemoveLabel
'properties
End With
Set AddRequest = GenPurchaseRequest.Controls.Add("Forms.Frame.1", "Frame" & ObjID)
With AddRequest
'properties
.Caption = "Purchase Request - " & ObjID
End With
With AddRequestButton
.Top = 168 + (126 * i)
.Left = 18
End With
With SubmitButton
.Top = 168 + (126 * i)
.Left = 200
End With
With CancelButton
.Top = 168 + (126 * i)
.Left = 381
End With
With GenPurchaseRequest
.ScrollHeight = 200 + (126 * i)
.ScrollTop = 200 + (126 * i)
End With
ReDim Preserve RemoveButtonArray(0 To i)
Set RemoveButtonArray(i).RemoveButton = AddRemoveButton
Next i
ObjID = ObjID + 1
Last = Last + 1它工作得很好，表单中的所有内容都是正确的。当用户删除请求时，下面的代码也可以正常工作：
Public WithEvents RemoveButton As MSForms.Image
Private Sub RemoveButton_click()
Dim ConfirmRemoval As Integer
Dim rbRefNo As String
Dim rbRefNoConvert As Integer
ConfirmRemoval = MsgBox("Are you sure you would like to remove this request?", vbYesNo)
If ConfirmRemoval = vbYes Then
rbRefNo = Mid(Me.RemoveButton.Name, 10)
rbRefNoConvert = CInt(rbRefNo)
With GenPurchaseRequest
If Last > 1 Then
.Controls.Remove ("Frame" & rbRefNo)
.Controls.Remove ("btnRemove" & rbRefNo)
.Controls.Remove ("lblRemove" & rbRefNo)
For i = rbRefNoConvert + 1 To Last - 1
.Controls("Frame" & i).Top = .Controls("Frame" & i).Top - 126
.Controls("btnRemove" & i).Top = .Controls("btnRemove" & i).Top - 126
.Controls("lblRemove" & i).Top = .Controls("lblRemove" & i).Top - 126
Next i
.AddRequestButton.Top = .AddRequestButton.Top - 126
.SubmitButton.Top = .SubmitButton.Top - 126
.CancelButton.Top = .CancelButton.Top - 126
.ScrollTop = .ScrollTop - 126
.ScrollHeight = .ScrollHeight - 126
Last = Last - 1
Else
MsgBox "There is only one active Purchase Request."
End If
End With
Else
'do nothing
End If
End Sub然后，用户可以返回，添加其他请求，以及删除他们不再需要的更多请求。当他们添加更多请求，然后尝试删除删除后直接添加的最后一个请求时，就会出现问题。例如:我添加了4个请求，然后删除了第二个请求。然后我添加了另一个请求，但希望删除第四个请求，但是，remove按钮不再工作。
我认为问题在于，一旦remove按钮函数被调用，我需要重新定义用于存储删除按钮的数组，但是我不知道如何做到这一点。我目前的尝试是：
For j = 0 To Last
If j = rbRefNoConvert Then
j = j + 1
Else
ReDim RemoveButtonArray(0 To j)
Set RemoveButtonArray(j).RemoveButton = AddRemoveButton
End If
Next j但是这个对象引用是不正确的，我不知道如何正确地引用它。我试着引用控件本身，但这不起作用。
我对类模块、数组和动态用户表单的使用非常陌生，很抱歉出现了冗长的问题！
任何帮助都是非常感谢的！
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-11-11 15:25:16
我尝试了几件事：
(1)将对我想要删除的控件的引用设置为数组中的nothing。
(2)将控件添加到集合而不是动态数组。
上述任何一种方法都不起作用，所以我使用了最后一种方法。
(3)清除需要删除的控件的文本值。然后，使用for循环，在想要删除的控件之后，将控件的所有文本值移到上面的框架中。然后，我删除了最后一个控件，重新定义了数组(当用户再次单击按钮以添加另一组控件时)，并重新定义我的计数器。代码如下所示。
Public WithEvents RemoveButton As MSForms.Image
Private Sub RemoveButton_click()
'Defines appropriate variables
Dim ConfirmRemoval As Integer
Dim rbRefNo As String
Dim rbRefNoConvert As Integer
'Asks user for input to remove a control
ConfirmRemoval = MsgBox("Are you sure you would like to remove this request?", vbYesNo)
If ConfirmRemoval = vbYes Then
'Extracts the name identifier from the control to be removed and also converts it into a number
rbRefNo = Mid(Me.RemoveButton.Name, 10)
rbRefNoConvert = CInt(rbRefNo)
With GenPurchaseRequest
If ObjID > 1 Then
'Loops through the dynamic form controls and adjusts the user-inputs to account for the removed control
For i = rbRefNoConvert To ObjID - 1
If i < (ObjID - 1) Then
.Controls("txtVendor" & i).Text = .Controls("txtVendor" & i + 1).Text
.Controls("txtItem" & i).Text = .Controls("txtItem" & i + 1).Text
.Controls("txtQuantity" & i).Text = .Controls("txtQuantity" & i + 1).Text
.Controls("txtProject" & i).Value = .Controls("txtProject" & i + 1).Value
.Controls("txtCatalog" & i).Text = .Controls("txtCatalog" & i + 1).Text
.Controls("txtDate" & i).Value = .Controls("txtDate" & i + 1).Value
Else
.Controls("txtVendor" & i).Text = .Controls("txtVendor" & i).Text
.Controls("txtItem" & i).Text = .Controls("txtItem" & i).Text
.Controls("txtQuantity" & i).Text = .Controls("txtQuantity" & i).Text
.Controls("txtProject" & i).Value = .Controls("txtProject" & i).Value
.Controls("txtCatalog" & i).Text = .Controls("txtCatalog" & i).Text
.Controls("txtDate" & i).Value = .Controls("txtDate" & i).Value
End If
Next i
'Removes selected remove button and associated form controls
.Controls.Remove ("Frame" & ObjID - 1)
.Controls.Remove ("AddRequestOptions" & ObjID - 1)
'Re-formats userform to adjust for removed controls
.AddRequestButton.Top = .AddRequestButton.Top - 126
.CopyRequestButton.Top = .CopyRequestButton.Top - 126
.SubmitButton.Top = .SubmitButton.Top - 126
.CancelButton.Top = .CancelButton.Top - 126
.ScrollTop = .ScrollTop - 126
.ScrollHeight = .ScrollHeight - 126
'Adjusts the object identifier variable to account for removed control
ObjID = ObjID - 1
Else
MsgBox "There is only one active Purchase Request."
End If
End With
Else
'do nothing
End If
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33614084
复制相关文章
相似问题
腾讯云开发者
