Re: Colouring of Listview rows based on criteria in colmuns value
Hey,
Thanks a lot for the help and also learn new things here, its working very good, just I have make a small change
If rngData(i, 2).Value > rngData(i, 3).Value Then
Change to
If rngData(i, 2).Value < rngData(i, 3).Value Then
Attaching herewith working workbook
Re: Colouring of Listview rows based on criteria in colmuns value
Hello
Kindly find the attachment for workbook along with userform and code. The intension here is if Available stock is less then cutoff then change the colour of respective row in listview.
Hello,
I wish to show the low alert rows colour differently in listview. That means whenever the sum of balance is equal to or lower than vaule in sum of low alert the figure in sum of balance gets into different colour or the row gets different colour, can anybody help to sort out the same
The Code use for this is as follows:
Private Sub UserForm_Activate()
'Set some of the properties for the ListView
'Set some of the properties for the ListView
UserForm4.TextBox1.Value = Format(Now(), "dd/mm/yyyy")
UserForm4.Label2 = UserForm4.TextBox1.Value
TextBox2.Text = Sheet7.Cells(1, 3)
UserForm4.Label3 = UserForm4.TextBox2.Value
With Me.ListView1
.Gridlines = True
.HideColumnHeaders = False
.View = lvwReport
.ColumnHeaders.Add , , "Polymer Version", 155
.ColumnHeaders.Add , , "Avilable Quantity (Kg)", 175
.ColumnHeaders.Add , , "Wastage Quantity (Kg)", 175
.ColumnHeaders.Add , , "Cutoff (Kg)", 175
End With
With Me.ListView2
.Gridlines = True
.HideColumnHeaders = False
.View = lvwReport
.ColumnHeaders.Add , , "", 0
.ColumnHeaders.Add , , "", 0
.ColumnHeaders.Add , , "Job Number", 80
.ColumnHeaders.Add , , "Hybrid/ Variety", 100
.ColumnHeaders.Add , , "Batch Number", 80
.ColumnHeaders.Add , , "Quantity of Seeds Coated (Kg)", 160
.ColumnHeaders.Add , , "Polymer Used", 100
End With
'Call the sub to fill the ListView1
Call LoadListView1
'Call the sub to fill the ListView2
Call LoadListView2
End Sub
Private Sub LoadListView1()
'Declare the variables
Dim wksSource As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long
Dim ColCount As Long
Dim i As Long
Dim j As Long
Dim k As Long
'Set the source worksheet
Set wksSource = Worksheets("Stock Pivot")
'Set the source range
Set rngData = wksSource.Range("A6").CurrentRegion
'Count the number of rows in the source range
RowCount = rngData.Rows.Count
'Count the number of columns in the source range
ColCount = rngData.Columns.Count
'Fill the ListView1
For i = 3 To RowCount
Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
For j = 2 To ColCount
Select Case j
Case 2 To 6
LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")
Case Else
LstItem.ListSubItems.Add Text:=rngData(i, j).Value
End Select
Next j
Next i
For k = 1 To ColCount
Select Case k
Case 2 To 6
If LstItem.ListSubItems.Add.Text = rngData(1, 2).Value > LstItem.ListSubItems.Add.Text = rngData(1, 4).Value Then
LstItem.ListSubItems.Add.ForeColor = vbRed
Else
LstItem.ListSubItems.Add.ForeColor = vbGreen
End If
End Select
Next k
End Sub
Private Sub LoadListView2()
'Declare the variables
Dim wksSource As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim LstItem As ListItem
Dim RowCount As Long
Dim ColCount As Long
Dim i As Long
Dim j As Long
'Set the source worksheet
Set wksSource = Worksheets("Coating Status")
'Set the source range
Set rngData = wksSource.Range("A2").CurrentRegion
'Count the number of rows in the source range
RowCount = rngData.Rows.Count
'Count the number of columns in the source range
ColCount = rngData.Columns.Count
'Fill the ListView2
For i = 3 To RowCount
Set LstItem = Me.ListView2.ListItems.Add(Text:=rngData(i, 1).Value)
For j = 2 To ColCount
Select Case j
Case 6 To 7
LstItem.ListSubItems.Add Text:=Format(rngData(i, j).Value, "0.00")
Case Else
LstItem.ListSubItems.Add Text:=rngData(i, j).Value
End Select
Next j
Next i
End Sub[TABLE="class: grid, width: 500, align: left"]
[TABLE="width: 105"]
Row Labels
[/td]
[/TABLE]
[TABLE="width: 185"]
Sum of Available Stock (KG )
[/td]
[/TABLE]
[TABLE="width: 108"]
Sum of Wastage
[/td]
[/TABLE]
[TABLE="width: 113"]
Sum of Low Alert
[/td]
[/TABLE]
[TABLE="width: 79"]
[TD="class: xl65, width: 105, bgcolor: transparent"]Blue Ver.3
[TD="class: xl65, bgcolor: transparent"]Blue Ver.4[/TD]
[TD="class: xl65, bgcolor: transparent"]Blue Ver.6[/TD]
[TD="class: xl65, bgcolor: transparent"]Green L202[/TD]
[TD="class: xl65, bgcolor: transparent"]Magenta Ver.3[/TD]
[TD="class: xl65, bgcolor: transparent"]Mixing Solution[/TD]
[TD="class: xl65, bgcolor: transparent"]Orange Ver.3[/TD]
[TD="class: xl65, bgcolor: transparent"]Orange Ver.6[/TD]
[TD="class: xl65, bgcolor: transparent"]Red Ver.3[/TD]
[TD="class: xl65, bgcolor: transparent"]Red Ver.4[/TD]
[TD="class: xl65, bgcolor: transparent"]Yellow Ver.3[/TD]
[TD="class: xl65, bgcolor: transparent"]Yellow Ver.4[/TD]
[/TABLE]
[/TD]
[TABLE="width: 185"]
[TD="align: right"]3030
[TD="align: right"]150[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]185[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]394.28[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]55[/TD]
[/TABLE]
[/TD]
[TABLE="width: 108"]
[TD="align: right"]0
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TABLE]
[/TD]
[TABLE="width: 113"]
[TD="align: right"]75
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[/TABLE]
[/TD]
[/TABLE]
Hi,
I tried using following code to import data from Listview to excel sheet, but it overwrites data instead of pasting the data to last row/ new rows, Can anybody guide here:
Private Sub CommandButton2_Click()
Dim LastRow As Object
Dim i, j As Integer
Set LastRow = Sheet10.Range("a65536").End(xlUp)
For i = 1 To ListView1.ListItems.count
Sheet10.Cells(i + 1, 1) = ListView1.ListItems(i).Text
For j = 1 To 1
Sheet10.Cells(i + 1, 2) = ListView1.ListItems(i).ListSubItems(1).Text
Next j
Next i
End SubHello,
I a struggling to import the items (data) in listview to excel sheet, with the followin code I am able to import the data in excel sheet but each time it over write the earlier data. I wish to copy the data in new row or last row. The code is as follows:
Private Sub CommandButton1_Click()
Dim i As Long
Dim r As Long
Dim c As Long
Sheet14.Activate
'List the selected items and subitems starting at Row 2
r = 3
With Me.ListView1
For i = 1 To .ListItems.count
If .ListItems(i).Checked Then
Cells(r, "A").Value = .ListItems(i).Text
For c = 1 To .ListItems(i).ListSubItems.count
Cells(r, c + 1).Value = .ListItems(i).ListSubItems(c).Text
Next c
r = r + 1
End If
Next i
End With
Unload Me
UserForm26.Show
End SubRe: Copy selected Listitems from Listview to excel sheet
Hello,
The followin code works perfectly for Listbox but when tried for listview its not working can anybody suggest the necessary changes in it.
Private Sub CommandButton1_Click()
Dim i As Long, j As Long
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
j = j + 1
Worksheets("Sheet1").Range("A" & i + 2).Resize(1, 6).Copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
MsgBox j & " Rows Copied To Sheet 2 - Have a look if you dont beleive me"
End SubRe: Copy selected Listitems from Listview to excel sheet
Can I get a help with the above code
Re: Copy selected Listitems from Listview to excel sheet
I am really unable to understand, Can anybody help me to sort out
Private Sub CommandButton4_Click()
Sheet14.Activate
Dim objExcel As New Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim i As Integer
Dim j As Integer
Set bkWorkBook = objExcel.ActiveWorkbook
Set shWorkSheet = ActiveWorkbook.ActiveSheet
For i = 1 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(1, Chr(64 + i)) = ListView2.ColumnHeaders(i)
Next
For i = 1 To ListView2.ListItems.count
shWorkSheet.Cells(i + 2, "A") = ListView2.ListItems(i).Text
ListView2.SelectedItem.EnsureVisible
For j = 2 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(i + 2, Chr(64 + j)) = ListView2.ListItems(i).SubItems(j - 1)
Next
Next
objExcel.Visible = True
End SubRe: Copy selected Listitems from Listview to excel sheet
Hello,
I am really not very good in VBA, I
have attach your code as you have send it as a example to try it out. so I thought it will serve my purpose.
In my first question itself I requested the help to copy selected items in listview to excelsheet.
Once again request to kindly help to sort out, I am willing to learn the new things.
Re: Copy selected Listitems from Listview to excel sheet
It is showing the selected items but copying all the items to sheet
Private Sub CommandButton4_Click()
Dim i As ListItem
For Each i In ListView2.ListItems
If i.Checked Then
MsgBox "Item " & i.Index & " is checked..."
End If
Next
Sheet14.Activate
Dim item As ListItem
For Each item In ListView2.ListItems
If item.selected = True Then
Dim objExcel As New Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim j As Integer
Set bkWorkBook = objExcel.ActiveWorkbook
Set shWorkSheet = ActiveWorkbook.ActiveSheet
For iTemp = 1 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(1, Chr(64 + iTemp)) = ListView2.ColumnHeaders(iTemp)
Next
For iTemp = 1 To ListView2.ListItems.count
shWorkSheet.Cells(iTemp + 2, "A") = ListView2.ListItems(iTemp).Text
ListView2.SelectedItem.EnsureVisible
For j = 2 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(iTemp + 2, Chr(64 + j)) = ListView2.ListItems(iTemp).SubItems(j - 1)
Next
Next
objExcel.Visible = True
End If
Next
End SubRe: Copy selected Listitems from Listview to excel sheet
You know your are the best teacher I have meet here, your are actually teaching me in the sense one teacher should teach a student. This is working fine but still the problem is there its not copying the selected items to dedicated sheet
Re: Copy selected Listitems from Listview to excel sheet
Hello,
The selection is through checkbox, I am unable to sort out, your advice will definatel help in making it happen
[ATTACH=CONFIG]61623[/ATTACH]
Re: Copy selected Listitems from Listview to excel sheet
Hello,
I was able to now copy the data from listview to the activeworkbook and dedicated sheet also with following code. But the question is how to copy only selected items from listview, can anybody suggest the code for the same:
Private Sub CommandButton4_Click()
Sheet14.Activate
Dim objExcel As New Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim i As Integer
Dim j As Integer
Set bkWorkBook = objExcel.ActiveWorkbook
Set shWorkSheet = ActiveWorkbook.ActiveSheet
For i = 1 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(1, Chr(64 + i)) = ListView2.ColumnHeaders(i)
Next
For i = 1 To ListView2.ListItems.count
shWorkSheet.Cells(i + 2, "A") = ListView2.ListItems(i).Text
ListView2.SelectedItem.EnsureVisible
For j = 2 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(i + 2, Chr(64 + j)) = ListView2.ListItems(i).SubItems(j - 1)
Next
Next
objExcel.Visible = True
End SubRe: Copy selected Listitems from Listview to excel sheet
Oaky but what should be the code than to make it happen to copy in the same workbook in dedicated sheet and only selected items from listview
Hello,
I would like to copy the slected listitems from listview to a dedicated excel sheet Sheet 14 (Selection) in the active workbook. I am trying with following code but with this code its opening new workbook and copying the whole listview can any body help to sort out
Private Sub CommandButton4_Click()
Dim objExcel As New Excel.Application
Dim bkWorkBook As Workbook
Dim shWorkSheet As Worksheet
Dim i As Integer
Dim j As Integer
Set objExcel = New Excel.Application
Set bkWorkBook = objExcel.Workbooks.Add
Set shWorkSheet = bkWorkBook.ActiveSheet
For i = 1 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(1, Chr(64 + i)) = ListView2.ColumnHeaders(i)
Next
For i = 1 To ListView2.ListItems.count
shWorkSheet.Cells(i + 2, "A") = ListView2.ListItems(i).Text
For j = 2 To ListView2.ColumnHeaders.count
shWorkSheet.Cells(i + 2, Chr(64 + j)) = ListView2.ListItems(i).SubItems(j - 1)
Next
Next
objExcel.Visible = True
End SubRe: Delete range of Excel rows by using command button from Userform
Thanks working perfectly
Re: Activesheet Refresh All status on messenger box or on userform
I am still looking for some help, can anybody help me to sort out this.
Re: Activesheet Refresh All status on messenger box or on userform
I have tried following code but its showing error
For i = 1 To ActiveWorkbook.Connections.Count
ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery = False
'MsgBox ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery
Next
ActiveWorkbook.RefreshAllHello,
I have a excelsheet which get update from server after refersh all. The data after referesh need to be in userform lisatview. But what happens is that the listview userform is displayed & refresh is working in background hence updated data cant be displayed in userform listview. I am able to open userform after a delay of 1 minutes but sometime if the data is huge it does not get updated, so userform open after 1 minute without updated data. Can anybody help to sort out.
Is it possible to show the achitveworkbook referesh status on messener box or on userform, so that user will be aware that referesh is still active and as soon as activeworkbook refresh completed desired userform opens.
Re: Delete range of Excel rows by using command button from Userform
With this code getting Compile error:
Expected: list separator or)