Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How To Display Part of Excel on VBA Form

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.

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?

*ps: again, my file's format is .csv and I am using Excel 2007

like image 441
t1w Avatar asked Nov 23 '12 10:11

t1w


1 Answers

You can use a multi column Listbox to show the data.

LOGIC

  1. Import the text (Csv) file in the temp sheet
  2. Show that data in the multicolumn Listbox
  3. Delete the temp sheet in the Userform unload event

Import the text (Csv) file in the temp sheet

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

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

enter image description here

Delete the temp sheet in the Userform unload event

To Delete the temp sheet, declare the wsTemp on the top of the code so that you can access that in the UserForm_QueryClose event. See this complete 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

HTH

like image 140
Siddharth Rout Avatar answered Sep 22 '22 17:09

Siddharth Rout