Hi gang,
I had a frustrating problem with a shape on my worksheet. It is a Text Box Shape (not a userform type) and it is supposed to display text from a specific cell. The cell contains an INDEX formula, so the contents change based on a scrollbar control elsewhere.
My problem was, the Text Box would not always "refresh" its contents when the cell contents changed. It seemed the workbook had to be open for a few minutes before it would respond properly. I watched the cell while using the scrollbar control and it was updating just fine; the problem was definitely between the cell and the Shape. I have read that Shapes have "slow code" in Excel 2007.
I thought of attaching a macro to the scrollbar that would force a refresh of the shape along with performing the scrollbar's normal action. I failed to find any "refresh" or "repaint" methods for Shapes in VBA, so I resorted to using two horizontal flips--just activating something on the Shape seems to be enough to force the refresh, and I don't notice a delay. I named my Shape based on its purpose, to show explanatory notes next to a dynamic chart.
So this appears to have solved my problem. If someone is having the same problem, this may help. Also, if anyone has a better solution, please share. I may have overthought this a bit.
I had a frustrating problem with a shape on my worksheet. It is a Text Box Shape (not a userform type) and it is supposed to display text from a specific cell. The cell contains an INDEX formula, so the contents change based on a scrollbar control elsewhere.
My problem was, the Text Box would not always "refresh" its contents when the cell contents changed. It seemed the workbook had to be open for a few minutes before it would respond properly. I watched the cell while using the scrollbar control and it was updating just fine; the problem was definitely between the cell and the Shape. I have read that Shapes have "slow code" in Excel 2007.
I thought of attaching a macro to the scrollbar that would force a refresh of the shape along with performing the scrollbar's normal action. I failed to find any "refresh" or "repaint" methods for Shapes in VBA, so I resorted to using two horizontal flips--just activating something on the Shape seems to be enough to force the refresh, and I don't notice a delay. I named my Shape based on its purpose, to show explanatory notes next to a dynamic chart.
Code:
Sub RefreshEvent()
Dim db As Worksheet
Set db = Sheets("Dashboard")
db.Shapes("PieScrollShowEvent").Flip msoFlipHorizontal
db.Shapes("PieScrollShowEvent").Flip msoFlipHorizontal
End SubSo this appears to have solved my problem. If someone is having the same problem, this may help. Also, if anyone has a better solution, please share. I may have overthought this a bit.
