djemy1975
11-27-2018, 07:51 AM
Dear developpers,
I have a userform based on listview display of three sheets ,and I want to make some statistics on the form showing the numbers of ocuurence of certain text values in a specified column (in sheets("AU")and (47) in listview.
as follows :
N° of projects "closed" ..... 'the result was wrong number as I think it takes from one sheet only
N° of projects "OPEN"......
N° of projects "to close".....
I have tried with this syntaxe in my userform initialize ,but to no avail:
Private Sub UserForm_Initialize()
Call Create_Lists
ChartNum = 1
Dim WS As Worksheet
Dim lngRow As Long
Dim lvwItem As listItem
Dim lngCol As Long
Dim rSheet As Range
With ComboBox6
.List = Array("Basic to Sustain", "Specific to sustain", "Improve-performance")
End With
With ComboBox1
.List = Application.WorksheetFunction.Transpose(Worksheets("Basic to Sustain").Cells(1, 1).CurrentRegion.Rows(1))
End With
With ListView1
.View = lvwReport
.Gridlines = True
.HideSelection = False
.FullRowSelect = True
.HotTracking = True
.HoverSelection = False
.ColumnHeaders.Clear
Set WS = Worksheets("Basic to Sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion
rSheet.EntireColumn.AutoFit
For lngCol = 1 To rSheet.Columns.Count
.ColumnHeaders.Add , , WS.Cells(1, lngCol).Value, WS.Columns(lngCol).ColumnWidth * 10
Next lngCol
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
Set WS = Worksheets("Specific to sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
Set WS = Worksheets("Improve-performance")
Set rSheet = WS.Cells(1, 1).CurrentRegion
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
End With
Call CondFormat
Me.TextBox2.Value = ListView1.ListItems.Count
LV_AutoSizeColumn ListView1
'count the payment status
'Here is my problem:
Label28 = Application.WorksheetFunction.CountIf(rSheet, "clôturé")
Label29 = Application.WorksheetFunction.CountIf(rSheet, "en cours")
Label7 = Application.WorksheetFunction.CountIf(rSheet, "a clôturer")
Label30 = Application.WorksheetFunction.CountIf(rSheet, "En attende Validation Group")
End Sub
Your help is highly appreciated
I have a userform based on listview display of three sheets ,and I want to make some statistics on the form showing the numbers of ocuurence of certain text values in a specified column (in sheets("AU")and (47) in listview.
as follows :
N° of projects "closed" ..... 'the result was wrong number as I think it takes from one sheet only
N° of projects "OPEN"......
N° of projects "to close".....
I have tried with this syntaxe in my userform initialize ,but to no avail:
Private Sub UserForm_Initialize()
Call Create_Lists
ChartNum = 1
Dim WS As Worksheet
Dim lngRow As Long
Dim lvwItem As listItem
Dim lngCol As Long
Dim rSheet As Range
With ComboBox6
.List = Array("Basic to Sustain", "Specific to sustain", "Improve-performance")
End With
With ComboBox1
.List = Application.WorksheetFunction.Transpose(Worksheets("Basic to Sustain").Cells(1, 1).CurrentRegion.Rows(1))
End With
With ListView1
.View = lvwReport
.Gridlines = True
.HideSelection = False
.FullRowSelect = True
.HotTracking = True
.HoverSelection = False
.ColumnHeaders.Clear
Set WS = Worksheets("Basic to Sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion
rSheet.EntireColumn.AutoFit
For lngCol = 1 To rSheet.Columns.Count
.ColumnHeaders.Add , , WS.Cells(1, lngCol).Value, WS.Columns(lngCol).ColumnWidth * 10
Next lngCol
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
Set WS = Worksheets("Specific to sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
Set WS = Worksheets("Improve-performance")
Set rSheet = WS.Cells(1, 1).CurrentRegion
For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)
For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow
End With
Call CondFormat
Me.TextBox2.Value = ListView1.ListItems.Count
LV_AutoSizeColumn ListView1
'count the payment status
'Here is my problem:
Label28 = Application.WorksheetFunction.CountIf(rSheet, "clôturé")
Label29 = Application.WorksheetFunction.CountIf(rSheet, "en cours")
Label7 = Application.WorksheetFunction.CountIf(rSheet, "a clôturer")
Label30 = Application.WorksheetFunction.CountIf(rSheet, "En attende Validation Group")
End Sub
Your help is highly appreciated