Celltextpaint & Ms Excel help

Getting Started
1

Hi,

I have a listbox with a thousand rows.
I want to inserting text into specific criteria onto it, by adding some code in celltextpaint event.

The code seems to works, but the problem isI have to scroll it down manually by clicking the scrollbar button.

my question is, how to keep the text there without scrolling manually.

And also I tried to exporting into an excel spreadsheet, the correct result only for the first 15 rows which is shown on the listbox.

I am sure the problem is coming from this celltextpaint which is not refresh automatically.
is there any other ways to handle this method?, I have tried to do it in a button, but does not work.

any help…

thanks
arief

2

Are you adding the data in the listbox via CellTextPaint or are you adding the data with AddRow and CellValueAt? It sounds like you aren’t really putting the data into the listbox cells, but rather just painting the values on demand.

3

Hi,
yes, I do adding the data via celltextpaint.
The Listbox has some data, but I want to add some text using celltextpaint method.

how to change this code by using CellValueAt, I have check The LR, but its not describe completely

here is the code,

if Listbox1.cell(row,0)=" " then
  dim gg as integer
  gg=row+2
  Listbox1.cell(row,0)=Listbox1.cell(gg,3)
  Listbox1.cell(row,1)=shop_code.text.trim
  Listbox1.cell(row,2)=customer_type.text.trim
  Listbox1.cell(row,4)=txt_date.Text.trim
  Listbox1.cell(row,10)=Listbox1.cell(gg,6)
  
  
  //calculate total start
  dim total as double
  Dim xRow,nRow,oCount As Integer
  xRow=Listbox1.ListCount
  oCount=0
  
  dim oo as integer
  for oo=0 to Listbox1.ListCount -1
    dim ggg,j,ah as integer
    dim cd as integer
    ggg=CDbl(listbox1.cell(oo,1))
    j=CDbl(listbox1.cell(oo,4))
    cd=ggg*j
    listbox1.cell(oo,5)=str(cd)
  next
  
  For nRow=0 to xRow-1
    Dim s,ss As String
    s=Listbox1.Cell(nRow,3)
    
    
    If s=Listbox1.cell(row,0) Then
      total = total + CDbl(Listbox1.Cell(nrow, 5).Trim)
    end if
    
    Listbox1.cell(row,12)= str(total)
    Listbox1.cell(row,12)=format(total,"###,##0")
  next

thanks
arief

4

CellValueAt is the new version (API2) of Cell that you are using.

I’m not clear on what you are trying to add with CellTextPaint though? You’d only want to use this to change the rendering of the text that is already in the cell. You could change the content, but you need to content for other purposes (like the Excel export). What are you doing in CellTextPaint?

5

The data is come from a csv file.
My goals is converting the data in listbox with some modification in celltextpaint event and exporting the result into an excel sheet without scrolling the list box manually.

Thanks
Arief

6

It sounds like you don’t need to use CellTextPaint since display is not your primary objective. You may not even need a listbox (do you even need to look at the data? If not, working with the data in an array in memory may be faster).Iff you do use a listbox, you should be able to add additional columns to the listbox and place the modified values in those columns, or just update the data in place in the cells. You would use CellValueAt for getting and setting the values in your data processing function.
CellTextPaint is only used to modify how the text in the cell is rendered. It is only triggered for a cell when that cell is painted (along with CellBackgroundPaint), which only occurs when the cell is visible and needs to be redrawn. You should not perform processing in this event.