How to display part of Excel in VBA form

Question

I have a file in .csv format and from AS columns, it has some records, such as a table. My complete program will insert / delete / delete / add some rows, columns and edit cell values, etc. I managed to encode all the operations that I need, now I'm trying to integrate it with gui.

I want to display cells from Ax1 to the last column that has an entry in the form of a VBA user. How can i do this?

* ps: again my .csv file format and I am using Excel 2007

Answer 1

You can use a multi-column list to display data.

LOGIC

• Import text (Csv) to temp file
• Show data in multiline list
• Delete temporary table in Userform unload event

Import text (Csv) to temp file

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 this data in a multi-column list.

Show data in multi-column list

I take an example of 3 columns and before towing 20. Change if applicable

 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 temporary table in Userform unload event

To delete the temporary table, declare wsTemp at the top of the code so you can access this UserForm_QueryClose event. Full example

 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 

NTN