ActiveX Text Box not updating

[filido]

Hi, I have grouped an ActiveX text box with Excel's box and whiskers chart. I want the text box to update as the data table is filtered. First, I used the Linked Cell -function but it doesn't update the text box when I run my macro. It sorta "reacts" slowly, in a sense that the value of text box SHOULD be 'Chart 1', 'Chart 2', 'Chart 3' etc. But now it goes 'Chart 1', 'Chart 1', Chart 2'...

Then I removed the Linked Cell and added this to my code:

wsChart.OLEObjects("TextBox1").Object.Value = "Chart name"

I have to add this line after every filtering. However, this too only works when I run my macro step by step with F8. When I run my macro as a whole, the text box only applies the first filter value and it does not update as the data table is filtered.

The box and whiskers chart is really annoying because I also have a scatter chart and I have no problem with it but with box&whiskers there seems to always be some issue with it!

 

[Yongle]

I have not tested this with a "box and whiskers" chart (whatever one of those may be ) but here is something to try
- link the textbox to a cell (in my example below it is B2)
- in VBA write value to a cell whenever chart name changes (in my test Chart 1 , Chart 2...Chart 50 )
- on next line after name change insert this line

  Application.Calculate

In my test
- B2 updates regardles of Application.Calculate but the TextBox behaves differently
- without Application.Calculate the textbox updates ONCE (at the end)
- with Application.Calculate the textbox updates every time x changes

Sub Test()
    Dim shp As Shape, aCount As Long, x As Long, y As Long
    Set shp = Me.Shapes("TextBox 1")

    For x = 1 To 50
        For y = 1 To 1000
            Range("B2") = "Chart " & x
        Next y
        Application.Calculate
    Next
MsgBox "done"
End Sub

 

[Yongle]

oops
- above test worked with a standard textbox but when I tested with active-x textbox it does not update

Would a standard textbox do everything you require ?
- if so give suggestion in post#2 a try

 

[filido]

Hi, I will try to add this Application.Calculate but now that I changed my textbox to standard textbox, I don't know how to assign a value to it...
Sheets("Sales").TextBox1.Value = "___" Doesn't work.

 

[Yongle]

The shape is usually auto-created where it name has a space between TextBox and its number

This works for me

Sheets("Sales").Shapes("TextBox 1").Characters.Text = "???"
Application.Calculate

Or
- assign a cell value to the shape by clicking on shape to select it and then in formula bar ...
=B2

AND update the cell during the macro

Range("B2") = "???"
Application.Calculate

 

[filido]

Hi! I figured it out. I added a standard textbox and then added to my code:

Sheets("Sales").Shapes("TextBox 1").TextEffect.Text = "???"
Application.Calculate

Now the textbox updates at the same time with the boxplot. Thank you for telling me that the shape is usually auto-created when there's a space between TextBox and its number. A very small detail but important to me that helped me a lot.

 

[Yongle]

thanks for the feedback