如何使用VBA自动选择弹出窗口 [英] how to select Pop Up automatically by using VBA

问题描述

我已将数据导入Excel工作表。我想通过刷新数据使我导入的数据保持最新。在此步骤中，Dialogbox将显示并要求用户选择我要刷新的文件，我必须选择一个文件进行刷新，然后单击导入按钮。但我不希望用户看到路径文件。因此，我创建了一个命令按钮，供用户刷新数据。我的问题是什么是使这个过程自动运行的查询

I have imported a data into excel sheet. I want to make my imported data up to date by refreshing data. At this step, Dialogbox will show and ask user to select the file which I want to refresh and I have to choose a file for refreshing and click "Import" Button. but I dont want user see the path file. Thus I create a command button for user to refresh the data. My question is what is the query to make this process run autometically

推荐答案

这很简单;）



步骤：

1）转到代码窗口（ALT + F11）

2）插入新的UserForm（使用Insert-> UserForm菜单）

3）添加：

- ListBox（位于UserForm区域的顶部）

- 2个按钮（一个用于取消操作和一个确定操作）

4）设置UserForm属性：

- 标题 = 选择文件...

5）设置ListBox属性：

- BackColor = & H8000000F& （按钮面）

- BorderStyle = fmBorderStyleNone

- ListStyle = fmListStyleoption

- MultiSelect = fmMultiSelectSingle

- SpecialEffect = fmSpecialEffectFlat

6）设置Button1属性：

- 姓名 = CmdCancel

- 标题 = 取消

- 取消 = 真

7）设置Button2属性：

- 名称 = CmdOK

- 标题 = 确定

8）将以下代码复制并粘贴到UserForm1模块：

It's simple ;)

Steps to do:
1) Go to Code window (ALT+F11)
2) Insert new UserForm (use Insert->UserForm menu)
3) Add:
- ListBox (at the top of UserForm area)
- 2 Buttons (one for Cancel action and one for OK action)
4) Set UserForm properties:
- Caption = "Select file..."
5) Set ListBox properties:
- BackColor = &H8000000F& (Button Face)
- BorderStyle = fmBorderStyleNone
- ListStyle = fmListStyleoption
- MultiSelect = fmMultiSelectSingle
- SpecialEffect = fmSpecialEffectFlat
6) Set Button1 properties:
- Name = CmdCancel
- Caption = "Cancel"
- Cancel = True
7) Set Button2 properties:
- Name = CmdOK
- Caption = "OK"
8) Copy and paste below code to UserForm1 module:

Option Explicit

Private Sub CmdCancel_Click()
Unload Me
End Sub


Private Sub CmdOK_Click()

MsgBox "Selected file: '" & Me.ListBox1.Value & "'", vbInformation, "Information..."

Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer

With Me.ListBox1
    .ColumnCount = 2
    .ColumnWidths = .Width - 24 & ";0" '
    .BoundColumn = 2
    For i = 1 To 3
        .AddItem ""
        .Column(0, .ListCount - 1) = "ShortFileName_" & i
        .Column(1, .ListCount - 1) = "FullFileName_" & i
    Next i
End With

End Sub

9）在 ImportButton_Click 事件中添加以下行：

9) Inside ImportButton_Click event add this line:

UserForm1.Show

10）测试它！

10) Test it!

这篇关于如何使用VBA自动选择弹出窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！