userform listbox columnheads

[Guest]

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
'<<=============

 

[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:

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.

 

[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
'<<=============

 

[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.

 

[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"

 

[Guest]

Norman,

May I have a look at your sample file also?

Thanks!

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Tom Ogilvy]

The code worked fine for me.

 

[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.

 

[Guest]

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