# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  userform listbox columnheads

## borg

Hi,

I used rowsource to set the data into a listbox and then set the columnheads
to true hoping that the first row of the data would be fill into the header.
But no such luck.  Here is what I've tried:

with userform1
.listbox1.rowsource="A1:E25"
.columnheads=true
end with

Is there a way to make the column headers be the data in cells "A1:E1"?

Thanks.

----------


## Norman Jones

Hi Borg,

Try:
'=============>>
Private Sub UserForm_Activate()
With Me.ListBox1
.RowSource = "A2:E25"
.ColumnHeads = True
.ColumnCount = 4
End With
End Sub
'<<=============


---
Regards,
Norman


"borg" <borg@discussions.microsoft.com> wrote in message
news:300D50EE-AE67-4CD6-AD85-040D9B9DF70C@microsoft.com...
> Hi,
>
> I used rowsource to set the data into a listbox and then set the
> columnheads
> to true hoping that the first row of the data would be fill into the
> header.
> But no such luck.  Here is what I've tried:
>
> with userform1
>    .listbox1.rowsource="A1:E25"
>    .columnheads=true
> end with
>
> Is there a way to make the column headers be the data in cells "A1:E1"?
>
> Thanks.
>

----------


## Helmut Weber

Hi Borg

quote from Joanthan West, Word MVP:

>There's a bug in VBA, and the ColumnHeads property doesn't work right.
>You're supposed to be able to fill the individual column headings of a
>listbox or combobox with text, but you can't

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

----------


## Norman Jones

Hi Helmut,

> quote from Joanthan West, Word MVP:

>>There's a bug in VBA, and the ColumnHeads property doesn't work right.
>>You're supposed to be able to fill the individual column headings of a
>>listbox or combobox with text, but you can't

In my experience, column headings work providing that the listbox is filled
using the RowSource property and that the first row assigned to the property
is the row immediately below the desired headings.

Whilst I am unaware of the broader context of the Joanthan West quote, I
experience no problem in displaying the required column headings.


---
Regards,
Norman


"Helmut Weber" <nbhymsjxdgcn@mailinator.com> wrote in message
news:u0c1421ml28f11utrv9jqv6a8jsdh7po7j@4ax.com...
> Hi Borg
>
> quote from Joanthan West, Word MVP:
>
>>There's a bug in VBA, and the ColumnHeads property doesn't work right.
>>You're supposed to be able to fill the individual column headings of a
>>listbox or combobox with text, but you can't
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"
>

----------


## Helmut Weber

Hi Norman,

you are right, indeed.

Maybe forms react differently in Excel and Word,
should have tested it. :-(.

With Me.ListBox1
.RowSource = "A1:A30"
.ColumnHeads = True
End With

creates a columnhead "Column A".

Any idea how to modify or retrieve it?

Thx.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

----------


## Norman Jones

Hi Helmut,

> creates a columnhead "Column A".

> Any idea how to modify or retrieve it?

Perhaps, something like:

'=============>>
Private Sub CommandButton1_Click()
Dim rng As Range

Set rng = Range(Me.ListBox1.RowSource)
MsgBox rng(0, 1).Value
rng(0, 1).Value = "New"
MsgBox rng(0, 1).Value
Me.ListBox1.RowSource = Me.ListBox1.RowSource

End Sub
'<<=============


---
Regards,
Norman


"Helmut Weber" <nbhymsjxdgcn@mailinator.com> wrote in message
news:4he1429p1e7fsbl5m823ms78gcaua8o41v@4ax.com...
> Hi Norman,
>
> you are right, indeed.
>
> Maybe forms react differently in Excel and Word,
> should have tested it. :-(.
>
>    With Me.ListBox1
>        .RowSource = "A1:A30"
>        .ColumnHeads = True
>    End With
>
> creates a columnhead "Column A".
>
> Any idea how to modify or retrieve it?
>
> Thx.
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"
>
>
>
>
>

----------


## Helmut Weber

Hi Norman,

hmm...

seems to set the value of the first item in the list,
but not the columnhead.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

----------


## Norman Jones

Hi Helmut,

> hmm...
>
> seems to set the value of the first item in the list,
> but not the columnhead.

Conversely, the siggested code retrieves, changes and re-retrieves the
column header for me.

Should you wish, I can send you my test file.

---
Regards,
Norman

----------


## Helmut Weber

Hi Norman,

>Should you wish, I can send you my test file.

please do,
however hard I try,
Excel seems to stay a mystery to me.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

----------


## Edwin Tam

Norman,

May I have a look at your sample file also?

Thanks!

Regards,
Edwin Tam
edwintam@vonixx.com
http://www.vonixx.com


"Norman Jones" wrote:

> Hi Helmut,
>
> > hmm...
> >
> > seems to set the value of the first item in the list,
> > but not the columnhead.
>
> Conversely, the siggested code retrieves, changes and re-retrieves the
> column header for me.
>
> Should you wish, I can send you my test file.
>
> ---
> Regards,
> Norman
>
>
>

----------


## Tom Ogilvy

The code worked fine for me.

--
Regards,
Tom Ogilvy


"Helmut Weber" <nbhymsjxdgcn@mailinator.com> wrote in message
news:aol1425sfkpq0kc9iuscq9fs28gocss7d6@4ax.com...
> Hi Norman,
>
> hmm...
>
> seems to set the value of the first item in the list,
> but not the columnhead.
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"

----------


## Helmut Weber

Hi,

thanks everybody.

I see, the value from the first cell in the column
becomes the columnheader! I phantasized of a way
to have the columnheader independant of the values in the column.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

----------


## Tom Ogilvy

No, that is not supported.

--
Regards,
Tom Ogilvy

"Helmut Weber" <nbhymsjxdgcn@mailinator.com> wrote in message
news:cmd242hobar8gihcd3bncvjet6l849dlu9@4ax.com...
> Hi,
>
> thanks everybody.
>
> I see, the value from the first cell in the column
> becomes the columnheader! I phantasized of a way
> to have the columnheader independant of the values in the column.
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"
>
>
>
>

----------


## Spike

Any Help?

I have a listbox in a word form. i want the rowsource to reference my excel
document. If i do this in excel VBA it works fine, but when i try it in word
VBA it will not. The following are the two lines that work in excel but not
word.

wbUserFile.ActiveSheet.Cells(2, 2).Resize(5).Select
lstGraphInfo.RowSource = MyExcel.Selection.Address(External:=True)

thanks

--
Jeff "Spike" Zapinski



"Norman Jones" wrote:

> Hi Borg,
>
> Try:
> '=============>>
> Private Sub UserForm_Activate()
>     With Me.ListBox1
>         .RowSource = "A2:E25"
>         .ColumnHeads = True
>         .ColumnCount = 4
>     End With
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "borg" <borg@discussions.microsoft.com> wrote in message
> news:300D50EE-AE67-4CD6-AD85-040D9B9DF70C@microsoft.com...
> > Hi,
> >
> > I used rowsource to set the data into a listbox and then set the
> > columnheads
> > to true hoping that the first row of the data would be fill into the
> > header.
> > But no such luck.  Here is what I've tried:
> >
> > with userform1
> >    .listbox1.rowsource="A1:E25"
> >    .columnheads=true
> > end with
> >
> > Is there a way to make the column headers be the data in cells "A1:E1"?
> >
> > Thanks.
> >
>
>
>

----------

