Stop button flicker & slow paint on screen scroll down an Excel spreadsheet RRS feed

  • Question


    My VB.NET (VS 2010) project dynamically adds buttons to a few spreadsheets within the project ThisWorkbook class on load and on user command.

    The buttons are small (20x20 - 30x30) but there could be up to 100 of them (depending on the user's needs). They're a custom class which inherits the Button class & has a bunch of formats set to them, one of which is a graphicPath so I could shape the button however I wanted.

    SO THE ISSUE.....S

    1. Scrolling becomes very laggy. I understand with a lot of controls (and no double buffering), I'd expect to see the background of the colour appear and then the button paint itself but this sheet is laggy enough that even the background colour only semi-appears during a scroll (sorry, not great with technical talk).

    2. If the button is added off-screen, it doesn't draw correctly altogether. My workaround for this is to move the user to the cell where the button is being added & forcing a refresh by moving to another sheet and moving back (yep screen refresh and button refresh do NADA!)

    Here's a condensed version of what I have in the constructor:

    'pseudo of what I have in new
    New(..a few variables)
    'disable events, get vstoworksheet, set a few properties, get a guid
       'add the button
       vstoControls.AddControl(Me, getBtnLoc(btnRowCol,     vstoWorksheet), newGuid.ToString)
          'release my objects
          'return eventstate to original
    End Try
    End Sub

    & here's the bulk of the formatting:

        Public Sub formatBtn()
            Dim colorInt As Integer = getButtonColor()
            Dim family As FontFamily = New FontFamily("Arial")
            Dim fontStyle As FontStyle = fontStyle.Bold
            Dim format As StringFormat = StringFormat.GenericDefault
            'calls functions which set the appearance of the button
            Dim myGraphicsPath As New System.Drawing.Drawing2D.GraphicsPath()
            myGraphicsPath.AddString(getButtonString(), family, fontStyle, getButtonSize(), getButtonOrigin(), format)
            'set the button region to the graphics path we just drew
            Me.Region = New Region(myGraphicsPath)
    	Me.Size = New Size(20,20)
            Me.BackColor = System.Drawing.Color.FromArgb(colorInt, colorInt, colorInt)
            Me.FlatAppearance.BorderColor = System.Drawing.Color.FromArgb(colorInt, colorInt, colorInt)
            Me.FlatStyle = System.Windows.Forms.FlatStyle.Flat
            Me.UseVisualStyleBackColor = False
        End Sub

    I've tried...

    - double buffering (although I'm fairly convinced I'm doing this wrong as I don't know how I can access the container of the button if it's the worksheet)

    - Using WM_SETREDRAW to suspend and resume drawing

    - Suspending & resuming layout...

    - Enabling & disabling events

    Any help or advice would be really appreciated!!!


    Tuesday, August 13, 2013 4:37 AM