时间戳

首页 > 解决方案 > 单击 TextBox 打开 FileDialog - 在运行时

excel - 单击 TextBox 打开 FileDialog - 在运行时

问题描述

我正在使用表单在运行时生成 3 组文本框。我正在尝试实现这一点：当用户单击组“txtboxe”的文本框时，会打开一个 FileDialog，以便用户可以选择文件。选择文件后，我想获取文件路径。

问题是，通常，我会使用一个子类 - textboxe_Click - 但我需要它在现有模块中工作，并且每个文本框的名称是在运行时生成的。

所以...有什么想法吗？我尝试多次使用带有“控件”命令的东西，但没有任何成功。我设法从每个文本框中选择数据，但不能控制对文本框组的单击或更改。

我在下面提供代码。

Dim i As Long
Dim o As Long


Dim number As Long

number = InputBox("How many docs?", "Number of docs")


Dim txtB1 As Control
Dim txtB2 As Control

For i = 1 To number
    Set txtB1 = Controls.Add("Forms.Textbox.1")
    With txtB1
    .Name = "txtbox" & i
    .Height = 16
    .Width = 30
    .Left = 60
    .Top = 20 + (i * 40)
    .Value = i
    .Locked = True
    .BackColor = &H80000000
    End With
    
    Set txtB2 = Controls.Add("Forms.Textbox.1")
    With txtB2
    .Name = "txtboxw" & i
    .Height = 18
    .Width = 234
    .Left = 162
    .Top = 20 + (i * 40)
    Debug.Print .Name
    End With

 Set txtB3 = Controls.Add("Forms.Textbox.1")
    With txtB3
    .Name = "txtboxe" & i
    .Height = 18
    .Width = 264
    .Left = 420
    .Top = 20 + (i * 40)
    Debug.Print .Name
    End With

Next i

    

End Sub

Private Sub CommandButton3_Click()
Dim p As Long
For p = 1 To number

cells(p + 1, 1) = Controls("txtbox" & p).Value
cells(p + 1, 2) = Controls("txtboxw" & p).Value
cells(p + 1, 3) = Controls("txtboxe" & p).Value

Next p


End Sub ````

标签： excelvba

解决方案

使用动态创建的控件注册事件处理程序很棘手。

但是您可以使用类模块和WithEvent变量来做到这一点。

首先，将以下代码添加为 Class Module ControlEvent。

Option Explicit

Private WithEvents targetCtrl As MSForms.TextBox
 
Public Sub SetCtrl(new_ctrl As MSForms.TextBox)
    Set targetCtrl = new_ctrl
End Sub

' You can add arbitrary event handlers for TextBox as ``targetCtrl_(Event handler name)``
Private Sub targetCtrl_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show() Then
            ' You can access the control with the targetCtrl class variable
            targetCtrl.Value = .SelectedItems(1)
        End If
    End With
End Sub

然后，将 UserForm 中的代码修改为以下内容。

我添加了一个数组ctrls来存储ControlEvent类实例，并使用方法将创建的控件设置为WithEvents类变量SetCtrl。

Private ctrls As Variant    ' Added

Sub CommandButton2_Click()
    Dim i As Long
    Dim o As Long


    Dim number As Long

    number = InputBox("How many docs?", "Number of docs")
    
    Dim txtB1 As Control
    Dim txtB2 As Control
    Dim txtB3 As Control
    
    ReDim ctrls(0 To number - 1)
    
    For i = 1 To number
        Set txtB1 = Controls.Add("Forms.Textbox.1")
        With txtB1
        .Name = "txtbox" & i
        .Height = 16
        .Width = 30
        .Left = 60
        .Top = 20 + (i * 40)
        .Value = i
        .Locked = True
        .BackColor = &H80000000
        End With
        
        Set txtB2 = Controls.Add("Forms.Textbox.1")
        With txtB2
        .Name = "txtboxw" & i
        .Height = 18
        .Width = 234
        .Left = 162
        .Top = 20 + (i * 40)
        Debug.Print .Name
        End With
    
        Set txtB3 = Controls.Add("Forms.Textbox.1")
        With txtB3
        .Name = "txtboxe" & i
        .Height = 18
        .Width = 264
        .Left = 420
        .Top = 20 + (i * 40)
        Debug.Print .Name
        End With
    
        Set ctrls(i - 1) = New ControlEvent  ' Added
        ctrls(i - 1).SetCtrl txtB3           ' Added
    Next i
End Sub

推荐阅读