如何在 VBA 表单上显示部分 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13527540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To Display Part of Excel on VBA Form
提问by Timur Aykut YILDIRIM
I have a file on .csv format and from A-S columns, it has some records like a table. My complete program will insert/remove/delete/add some rows, columns and editing cell values etc. I managed to code all the operations that i need, now i'm trying to integrate it with a gui.
我有一个 .csv 格式和 AS 列的文件，它有一些像表格一样的记录。我的完整程序将插入/删除/删除/添加一些行、列和编辑单元格值等。我设法编写了我需要的所有操作，现在我正在尝试将它与 gui 集成。
What I want is to display cells from Ax1 to the last column that has record on VBA user form. How can i do that?
我想要的是显示从 Ax1 到 VBA 用户表单上有记录的最后一列的单元格。我怎样才能做到这一点？
*ps: again, my file's format is .csv and I am using Excel 2007
*ps：同样，我的文件格式是 .csv，我使用的是 Excel 2007
回答by Siddharth Rout
You can use a multi column Listbox to show the data.
您可以使用多列列表框来显示数据。
LOGIC
逻辑
- Import the text (Csv) file in the temp sheet
- Show that data in the multicolumn Listbox
- Delete the temp sheet in the Userform unload event
- 在临时表中导入文本 (Csv) 文件
- 在多列列表框中显示该数据
- 删除用户表单卸载事件中的临时表
Import the text (Csv) file in the temp sheet
在临时表中导入文本 (Csv) 文件
Private Sub CommandButton1_Click()
Dim wb As Workbook, wbTemp As Workbook
Dim wsTemp As Worksheet
Set wb = ThisWorkbook
Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")
wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)
Set wsTemp = ActiveSheet
wbTemp.Close SaveChanges:=False
End Sub
And now you can display that data in a multicolumn listbox.
现在您可以在多列列表框中显示该数据。
Show that data in the multicolumn Listbox
在多列列表框中显示该数据
I am taking an example of 3 Columns and up till tow 20. Change as applicable
我以 3 列到 20 列为例。根据情况进行更改
Private Sub CommandButton1_Click()
Dim wb As Workbook, wbTemp As Workbook
Dim wsTemp As Worksheet
Set wb = ThisWorkbook
Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")
wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)
Set wsTemp = ActiveSheet
wbTemp.Close SaveChanges:=False
With ListBox1
.ColumnCount = 3
.ColumnWidths = "50;50;50"
.RowSource = wsTemp.Range("A1:C20").Address
End With
End Sub
SCREENSHOT
截屏
Delete the temp sheet in the Userform unload event
删除用户表单卸载事件中的临时表
To Delete the temp sheet, declare the wsTempon the top of the code so that you can access that in the UserForm_QueryCloseevent. See this complete example
要删除临时表，请wsTemp在代码顶部声明 ，以便您可以在UserForm_QueryClose事件中访问它。看到这个完整的例子
Option Explicit
Dim wsTemp As Worksheet
Private Sub CommandButton1_Click()
Dim wb As Workbook, wbTemp As Workbook
Set wb = ThisWorkbook
Set wbTemp = Workbooks.Open("C:\MyCsv.Csv")
wbTemp.Sheets(1).Copy After:=wb.Sheets(wb.Sheets.Count)
Set wsTemp = ActiveSheet
wbTemp.Close SaveChanges:=False
With ListBox1
.ColumnCount = 3
.ColumnWidths = "50;50;50"
.RowSource = wsTemp.Range("A1:C20").Address
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
End Sub
HTH
HTH
