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]

Re:

I could install Microsoft Visual Basic 6.0 Common Controls and verify under Additonal controls.
Now there is no bug in ListItem and ListSubitem.

With the vba code given on this board, the format of colum4 is changed in "# ###,00" but the different color is not yet shown for every other day in column B6:B106.

In Worksheets("dépenses") there is no color in columnA, B, C, D. In listview5 the data are taken from that Worksheets.
I use following vba code, but no color is shown :

Private Sub the_listview5_refresh()
    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), 46, 0
        .Add , , Cells(2, 2), 74, 2
        .Add , , Cells(2, 3), 270, 0
        .Add , , Cells(2, 4), 60, 1
        .ListItems.Clear
        For Each c In Sheets("dépenses").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

How to solve ?

news

 

[Andrew Poulsom]

Where are the colours you want to use?

 

[news]

Re: Andrew Poulsom

there are no color of the data or text in Worksheets("dépenses").

When the Userform is opened, then in the listview the format of the text or data taken from Worksheets("dépenses") is changed in another color.
means for every different day is another color,
for Each c In Sheets("dépenses").Range("A3:A" & Range("A65536").End(xlUp).Row) ' in the 2nd colum is the date

e.g.:
01.10.2009 = vbRed
02.10.2009 = vbGreen
02.10.2009 = vbGreen
03.10.2009 = vbRed
...

may be I did not yet explained well,

news

 

[news]

Hello to all of the foum,

I have a listview in a Userform where the data of of Worksheets("data").rows("A6:D106") figures in this listview as soon the Userform is opened,
and that the data in the listview are in a different color for every other day.

In the Userform the data of the workshet are without color and the date is in the 2nd column, in colum B:B.

I use the following vba code, but the color does not figure yet,
Something is missing that in the listview the dolor is shown for everey different day.

Private Sub the_listview5_refresh() 
With Me.ListView5
.View = 3
With .ColumnHeaders
.Add , , Cells(2, 1), 46, 0
.Add , , Cells(2, 2), 74, 2
.Add , , Cells(2, 3), 270, 0
.Add , , Cells(2, 4), 60, 1
End With
.ListItems.Clear
For Each c In Sheets("dépenses").Range("A3:A" & Range("A65536").End(xlUp).Row)
Set LI = .ListItems.Add(, , c.Value)
LI.ForeColor = &HFF&
For j = 1 To 2
Set LSI = LI.ListSubItems.Add(, , c.Offset(0, j).Value)
LSI.ForeColor = &HFF& 
Next j
Set LSI = LI.ListSubItems.Add(, , Format(c.Offset(0, 3).Value, "#,###.00"))
LSI.ForeColor = &HFF& '&H80000005
 
'if date = c, and c is a day more (c+1) then the color is changed
If c = c + 1 Then
Set LI = .ListItems.Add(, , c.Value)
LI.ForeColor = &H80000005
For j = 1 To 2
Set LSI = LI.ListSubItems.Add(, , c.Offset(0, j).Value)
LSI.ForeColor = &H80000005
Next j
Set LSI = LI.ListSubItems.Add(, , Format(c.Offset(0, 3).Value, "#,###.00"))
LSI.ForeColor = &H80000005
End If
Next c
End With
End Sub

Which vba code is to add or to modify that the colors are changing for every different day and that the data are sorted of the 2nd colum (=Date) in listview ?

news

 

[news]

Hello to all of the forum,

As I cannot solve that the listview of an Userform is function correctly, that the modified data are replaced in listview and Worksheet,
I am adding this post, to get some tools to solve.

If there are not data in Worksheets("dépenses"), then there is already a bug with the tri and the color.
For every different day should be another color, but this does not work.
If is clicked on an item in listview5, and then changed the amount in Textbox5, the amount should change in listview5 and in the Worksheets, but this does not function.
As well if is clicked on an item of listview, then afterwards clicked on the button "delete", the data should be deleted in listview5 and in the worksheets, but this does not funcion.
I add the link of the file ("data,xls") with several bugs.

http://www.cijoint.fr/cjlink.php?file=cj200911/cijJH9rAPG.xls

I do not know how to change that the listview function well,
thanks in advance for some tools to solve,

news

 

[news]

Bonjour à tous du forum,

Re:
reçu vba code suivant pour résoudre :


Sub Ini_lvw5()
Dim i As Long, X As Byte, plage As Range, C As Range, j As Integer, y As Integer
Dim anciennevaleur As String
Dim couleur As String
On Error Resume Next
With Sheets("dépenses")
Set plage = .Range("A3:A" & .Range("A65536").End(xlUp).Row)
End With

With ListView5
.ListItems.Clear
.Sorted = False
For Each C In plage
.ListItems.Add , , C.Value
'X = .ListItems.Count
For j = 1 To 4
.ListItems(.ListItems.Count).ListSubItems.Add , , C.Offset(0, j).Value
Next
.ListItems(.ListItems.Count).ListSubItems.Add , , C.Row
Next C
Tri ListView5, 1 'to sort listview
couleur = vbBlue
For i = 1 To ListView5.ListItems.Count
With .ListItems(i)
If .ListSubItems(1).Text = anciennevaleur Then
.ForeColor = couleur
For j = 1 To 4
.ListSubItems(j).ForeColor = couleur
Next
Else
anciennevaleur = .ListSubItems(1).Text
Select Case couleur
Case vbBlue
couleur = vbRed
Case vbRed
couleur = vbBlue
End Select
.ForeColor = couleur
For j = 1 To 4
.ListSubItems(j).ForeColor = couleur
Next
End If
End With
Next i
End With
End Sub

news

 

[Dannyh1]

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.

Hi Andrew,

Have come across this post from a while ago.

I have a an issue where I want to change the background(interior) colour rather than the font. I have tried replacing .ForeColor with .BackColor and .BackgroundColour, but neither seem to work.

Help appreciated.

Thanks,
Danny.

 

[Andrew Poulsom]

A ListItem doesn't have a BackColor property, but here is some code that you may be able to adapt:

[SOLVED] Listview and colored line

 

[Dannyh1]

A ListItem doesn't have a BackColor property, but here is some code that you may be able to adapt:

[SOLVED] Listview and colored line

Thanks Andrew,

I haven't got to grips with it yet, hopefully the user will accept font colour or I can see this taking me a day or 2 to decipher!