PDA

View Full Version : VB Excel, Data Not able to auto refresh



ooitzechyi
09-14-2016, 02:33 AM
Hi,
I had my data to be show in excel as below.
I have
3 Red Shapes (Area: 2.0, 10.0, 10.0)
2 Green Shapes (Area: 6.0, 6.0)
3 Yellow Shapes (Area: 2.0, 10.0)

So, if I Count the no of shapes by filter the shape with Count only Area >5.0, I'll get
Red: 2
Green: 2
Yellow: 1

If I change my filter criteria to Count Only Area > 8.0, I will get
Red: 2
Green: 2
Yellow: 1


Private Sub Submit_Click()
Dim CountShape As Long
Dim xx As Integer

xx = 1
For Each ShapeRange In Sheet1.Shapes
ShapeWidth = ShapeRange.Width / 72
ShapeHeight = ShapeRange.Height / 72
ShapeArea = ShapeWidth * ShapeHeight


If ShapeArea >= TextBox1.Text Then

If Sheet1.Shapes(xx).Fill.ForeColor.RGB = RGB(0, 300, 0) Then
CountShape = CountShape + 1
Sheet1.Cells(2, 1) = CountShape
End If

If Sheet1.Shapes(xx).Fill.ForeColor.RGB = RGB(200, 200, 200) Then
CountShapeGrey = CountShapeGrey + 1
Sheet1.Cells(3, 1) = CountShapeGrey
End If

If Sheet1.Shapes(xx).Fill.ForeColor.RGB = RGB(300, 0, 0) Then
CountShapeRED = CountShapeRED + 1
Sheet1.Cells(4, 1) = CountShapeRED
End If


If Sheet1.Shapes(xx).Fill.ForeColor.RGB = RGB(400, 400, 0) Then
CountShapeYellow = CountShapeYellow + 1
Sheet1.Cells(5, 1) = CountShapeYellow
End If

End If
xx = xx + 1

Next ShapeRange

The data seems to be only refresh if it match with the filter criteria.
How could I make it to be refresh all.

Thanks

mana
09-14-2016, 07:45 AM
xx=1
Sheet1.Cells(2, 1).resize(4).clearcontents

ooitzechyi
09-14-2016, 06:26 PM
Thanks