change color and format in listview

[news]

Hello to all of this forum,

in a Useform I have a listview with 4 columns ( No., Date, description, amount)

For every different date is another color of police of that row
and the 4th colum is format : ####,00

I am usind following vba code, but it does not function well, ( attached Excel file )
How to solve ?
thanks for response,

news

 

[news]

the vba code using, but does not function well :

Private Sub Useform_Initialize()
Dim y As Byte, j As Byte, i As Byte
Dim c As Range
With Me.ListView5
.view=3
With.columnHeaders
.Add,,Cells(2,1),,
.Add,,Cells(2,2),,
.Add,,Cells(2,3),,
.Add,,Cells(2,4),,
End With
.ListItems.Clear

For each c in Sheets("data").Range("a3:a" & Range("a65536").end(xlUp).Row
y=y+1
.ListItems.Add,,c
.ListItems.ForeColor = c.Font.Color
For j = 1 to 3
.ListItems.ListSubItems.Add,,c.Offset(0,j)
.ListItems.ListSubItems.Add,,format(Sheets("data").Cells(c,4), "# ###,00")
.ListItems.ListSubItems(j).ForeColor = c.Offset(0,j).Font.Color
Next j
Next c
End Sub

 

[Andrew Poulsom]

Welcome to the Board.

This worked for me:

Private Sub UserForm_Initialize()
    Dim j As Byte, i As Byte
    Dim c As Range
    Dim LI As ListItem
    Dim LSI As ListSubItem
    With Me.ListView5
        .View = 3
        With .ColumnHeaders
            .Add , , Cells(2, 1).Value
            .Add , , Cells(2, 2).Value
            .Add , , Cells(2, 3).Value
            .Add , , Cells(2, 4).Value
        End With
        .ListItems.Clear
        For Each c In Sheets("data").Range("A3:A" & Range("A65536").End(xlUp).Row)
            Set LI = .ListItems.Add(, , c.Value)
            LI.ForeColor = c.Font.Color
            For j = 1 To 2
                Set LSI = LI.ListSubItems.Add(, , c.Offset(0, j).Value)
                LSI.ForeColor = c.Offset(0, j).Font.Color
            Next j
            Set LSI = LI.ListSubItems.Add(, , Format(c.Offset(0, 3).Value, "#,###.00"))
            LSI.ForeColor = c.Offset(0, 3).Font.Color
        Next c
    End With
End Sub

I had to change the number format to match my UK settings. Adjust to suit.

 

[news]

Hello,

thanks for response,

I copied the vba code in the excel file and when I started the macro to open the Userform, I got the message :
"error compiler, type undefined, is not defined from the user"

Dim LI As ListItem
Dim LSI As ListSubItem

when I put Dim n05 As ... then there was not listed : ListItem and ListSubitem

There is listed : As ... Listcolumn or Listrow

Does it means some reference are missing ? or not last version of Excel ?

I do not know how to solve ?

news

 

[Andrew Poulsom]

Those objects appear in intellisense if I add a ListView control from Microsoft Windows Common Controls 6.0 (mscomctl.ocx).

 

[news]

Re:

so I have to download that, Microsoft Visual Basic 6.0 Common Controls,

if it does not figure in reference,

http://www.microsoft.com/downloads/...98-51D0-41C1-BB14-64662F5F62FE&displaylang=en

I will download and then try it again to run the macro,

news

 

[Andrew Poulsom]

The code you posted seems to refer to a ListView control from Common Controls because you are using the ListItems and ListSubItems collections. I merely inserted the control on a UserForm and modified your code slightly so that it worked.

What was the source of your control?

 

[news]

Hello to all of the forum board,

Re:
Well I used another version of vba code with case and so:

For Each c In Sheets("dépenses").Range("a3:a" & Range("a65536").End(xlUp).Row)
y = y + 1
Select Case c.Offset(0, 1)
Case DateIs = Sheets("ref.").Range("A3").Value
couleur = vbRed
Case DateIs > Sheets("ref.").Range("A4").Value
couleur = vbGreen
Case DateIs > Sheets("ref.").Range("A5").Value
couleur = vbYellow
...
End Select
.ListItems.Add , , c
.ListItems.ForeColor = couleur
...

but as I have to repeat the vbacode 360 times ( as in colum A6:A there are about 360 rows) it is better to change.

I copied a listviewformat and this part of code "ListItems and ListSubItems collections" in the macro I am using.
Actualy when I create an Userform then is shown the tool box to add a Textbox, a CommandButton, ...
but there is no listview in the tool box.

So I copied the listview from some other file, that I have a listview in the Userform I am using.

I downloaded the update of Microsoft Windows Common Controls 6.0 (mscomctl.ocx),
but it didn't change anything. When I compile the VBA project, I still get the bug with ListItems and ListSubItems.

How to solve that, that there is no more a bug in that macro which put a different color for every other day and format of ###,00 in column4 of listview ?
How to get the listview in the tool box of VBA project ? or where do I find the listview in VBAproject, so that I can add a listview in a Userform ?
How does Microsoft Windows Common Controls 6.0 functions, as I downloaded it and started to run the application ?

thanks in advance for solution or some vba code to solve,

news

 

[Andrew Poulsom]

To get a ListView Control in your ToolBox, choose Tools|Additional Controls from the menu in the VBE. Scroll down to Microsoft ListView Control, check it and click OK.

 

[news]

Re:
thanks for already getting some response and your explanation in general about using VBA and the tools existing.

I checked on VBA Porject under tools,
but I cannot click on Additional Controls as it is not activiated.

I use Office 2002 student version, and downloaded several uptdate afterwards.
Is this due of this Office/Excel version ?,
and that some update or tools are missing, that does not figures in vba project also the Additional Controls,

news