VBA: Images, TextBoxes in the worksheet flicker on every Table cell

[inddon]

Hello There

I have the attached workbook which includes the following:

1. A few TextBoxes defined and grouped them.
2. A table
3. The pane is freezed
4. Defined Private Sub Worksheet_SelectionChange(ByVal Target As Range). This sub increase the height of the current row and make the row bold. The values used and assigned to it are displayed in B2 and C2 (displayed)

Problem: On navigating within the Table, the TextBoxes keep on flickering.
To me it seems the height increase of the current row to the cells (this is needed)

Looking for:
1. To stop the flickering of the 'textboxes and images'

Could you please advise? Attached is the workbook for your reference.


Looking forward to hearing from you

Thanks & regards
Don

 

[Hui]

It is ok in Excel 2013
But yes it is slightly annoying in Excel 2010

 

[inddon]

It is ok in Excel 2013
But yes it is slightly annoying in Excel 2010

Hi Hui,

True, any suggestions how can it be resolved?

Regards
Don

 

[inddon]

It is ok in Excel 2013
But yes it is slightly annoying in Excel 2010

Hi Hui,

The below code marked in <start problem> <end Problem>, causes the textboxes to flicker. Is there a way to make that piece of code only in VBA rather than assigning it to the cells and then increase the height of the row?:

Thanks & regards
Don

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim tb As ListObject

Set tb = ListObjects("TableMain")


'On Error Resume Next
If Not (Application.Intersect(Target, Me.ListObjects("TableMain").DataBodyRange) Is Nothing) Then

  If Target.CountLarge > 1 Then Exit Sub
  
  Application.EnableEvents = False
  Application.ScreenUpdating = False

  Target.Calculate


  'Cells.RowHeight = 15
  If Not Intersect(Target, Range("TableMain")) Is Nothing Then
<start problem>
  Range(Range("C2").Value).RowHeight = Range("D2").Value
  Range("D2") = Target.RowHeight
  Range("C2") = Target.Address
  
  Target.RowHeight = 20
<end problem>
  'Make current row font size 11
  'Rows(1).Font.Size = 10.5
  End If
  
  'This makes the cursor move to the next column of the same row,
  'instead of next row same column
  Application.MoveAfterReturnDirection = xlToRight
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End If

 

[Hui]

Have a read of Ludwig's post at:
http://www.excelforum.com/excel-pro...4976-cant-stop-the-buttons-from-blinking.html

 

[inddon]

Have a read of Ludwig's post at:
http://www.excelforum.com/excel-pro...4976-cant-stop-the-buttons-from-blinking.html

Thank you Hui, for checking it out and providing me the link.
I Tried the unfreeze pane as well, but in vain.

When I comment out the below piece of code, then it does not flicker. But the increase of the row height for the current row is needed. I don't know how this can be done dynamically without assigning the values to the cells.

Could you please advise?

  Range(Range("C2").Value).RowHeight = Range("D2").Value
  Range("D2") = Target.RowHeight
  Range("C2") = Target.Address
  
  Target.RowHeight = 20

Regards
Don

 

[NARAYANK991]

Hi Don ,

I have tried out with just one shape on a blank worksheet ; change the row height of any row , using the menu options ; you will see the shape flicker.

I think this is because of the algorithm Excel uses to refresh shapes.

If you have the same text and the same colours in cells , there is absolutely no flicker.

Narayan

 

[inddon]

Hi Don ,

I have tried out with just one shape on a blank worksheet ; change the row height of any row , using the menu options ; you will see the shape flicker.

I think this is because of the algorithm Excel uses to refresh shapes.

If you have the same text and the same colours in cells , there is absolutely no flicker.

Narayan

Hello Narayan

Thank you for your reply.

Is it possible to upload the exercise file you tried.

Regards
Don