userform listbox columnheads

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.

Hi Borg,

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


---
Regards,
Norman


"borg" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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"

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" <[email protected]> wrote in message
news:[email protected]...
> 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"
>

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"

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" <[email protected]> wrote in message
news:[email protected]...
> 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"
>
>
>
>
>

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"

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

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"

Norman,

May I have a look at your sample file also?

Thanks!

Regards,
Edwin Tam
[email protected]
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
>
>
>

The code worked fine for me.

--
Regards,
Tom Ogilvy


"Helmut Weber" <[email protected]> wrote in message
news:[email protected]...
> 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"

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"

No, that is not supported.

--
Regards,
Tom Ogilvy

"Helmut Weber" <[email protected]> wrote in message
news:[email protected]...
> 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"
>
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>