Adding column headers to listbox

[devatu]

Hello all,

I just found the columnhead function and set it to TRUE.
But I didnt know how to add information in the headers so I tried to look it up on the internet.
Soon it became clear to me that you need to use the RowSource for it and that it can only be done by using a range from an excel worksheet and not from within the vba code itself.
This is all fine to me, but when I have data in a sheet called sheet1, and use the following code:

Generate_Add_Form.ListBox1.RowSource = "A2:G2"

it will show the names I have in the excelform as if it was the first record on my listbox, and the rest of my records have disappeared. So it is not in the columnheaders above it.
Does anyone know how to fix this?

Thanks in advance and cheers,

Devatu

[Greg M]

Hi there,

If the values for your header texts are contained in Cells C5:E5 and your data values are in Cells C6:E10, you should define the RowSource Property of your ListBox as $C$6:$E:$10. The Column Headers for the ListBox will be taken from the row above this range.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[devatu]

Hey Greg,

Thanks for your quick reply.

I populate the listbox by data from my Access database, so not from data in a excel workbook.
The only thing I have in my excel workbook are the names for the headers, so not the actual data itself.
Is it not possible to do it than?

[Norie]

No, as you stated in your original post the only way to have column headers in a listbox is to populate it using RowSource.

There are alternatives however, for example you could create a set of small labels positioned above the columns of the listbox, or you could add another one row listbox positioned above the main listbox and populate with the field names.

[devatu]

Hmm okay, that's a pity then. Thanks for another helpful reply Norie.

A second question ( I don't know if I should make another thread for this, I suppose not.)

I have the following code:

Private Sub CommandButton7_Click()
	Dim Filepath As String
	Dim Filename As String
	Filename = InputBox("Please give a filename for the FMECA file.")
	Filepath = "C:\Users\Devin\Desktop\" & Filename & ".xlsx"
	Workbooks.Add.SaveAs (Filepath)
	With Workbooks(Filepath).Sheets("Sheet1")
		.Range("A1") = "test"
	End With
End Sub

But it gives me an error, with something about that the subscript is out of reach.

What I want to do, is open a new excel file, the user gives an input name and then I want to write something in a cell in the newly made workbook.
Everyone on the internet says that you have to use a range or cells, but I can't manage to get it working..

[Norie]

Really should be a new thread, but the answer is straightforward.

When using Workbooks(...) you only need the workbook name, Filepath includes the path and filename.

Try replacing Workbooks(FilePath) with Workbooks(FileName & ".xlsx").

Or create a reference to the new workbook.

Dim wbNew As Workbook

    ' other code

    Set wbNew = Workbooks.Add

    With wbNew
        .SaveAs Filepath
	With .Sheets("Sheet1")
            .Range("A1") = "test"
	End With
    End With

[devatu]

Thanks for the reply Norie, it works like a charm.

Sorry for not posting it in a new thread.

Cheers!