エクセルVBAにてテキストボックスの参照値を再計算したいです。

Question

エクセル2007において、オートシェイプと参照式の入ったテキストボックスをグループ化したものを多数配置しており、そのシートを別のブックにコピーしたところテキストボックスの参照値が同期されず、調べたところ2007でのバグのようでした。

対策として、テキストボックスの数式欄に入りそのままENTERすると再度参照値が同期されるようになるのですが、すべてのテキストボックスにこの作業をコピーの度に行うのは現実的ではないのでVBAを用いて数式欄を更新できる状態にしようとしています。

グループ化されていないテキストボックスを全て一つずつ認識することができるところまではできたのですが、肝心な数式に入りそのまま更新する方法がわかりません。
下記コードの※の部分に入れるコードをお教えいただけると幸いです。
下記コードでは削除を行い存在するグループ化されていないテキストボックスが全て削除されることを確認しました。

また、このコードをチェックしている環境はエクセル2003であることが原因だと考えているのですが、グループ内のテキストボックスは下記コードでは削除されませんでした。
この部分に関してもエクセル2007でも明らかに不可能な場合はご享受いただきたいと思います。

エクセル2007は職場での環境で、2003は自宅の環境です。
テキストボックスの参照先は同じシート内です。
シートのコピー後にリンクの設定でコピー元のリンクとなっているのをコピー先のリンクに変更もしています。

よろしくお願いします。

<追記1>

再計算ボタンは試しましたがシートのコピーで同期されなくなったテキストボックスは変わらず同期されないままでした。
また、設定で自動計算になっていることも確認しました。

Sub test()
    Dim i As Long
    Dim s As Long
    With ActiveSheet.Shapes
        For i = .Count To 1 Step -1
            If .Item(i).Type = msoTextBox Then
                .Item(i).Delete※※※※※※※※※※※※
            ElseIf .Item(i).Type = msoGroup Then
                For s = .Item(i).GroupItems.Count To 1 Step -1
                    If .Item(i).GroupItems(s).Type = msoTextBox Then
                        .Item(i).GroupItems(s).Delete※※※※※※※※※※※※
                    End If
                Next s
            End If
        Next i
    End With
End Sub

<追記2>

下記コードで解決できるかもしれません。手元にエクセル2007がないので確認できないのですが、確認出来次第こちらに報告させていただきます。
また、どなたか2007をお持ちの方がいらっしゃいましたらご確認していただけると幸いです。

Sub test()
    Dim i As Long
    Dim s As Long
    With ActiveSheet.Shapes
        For i = 1 To .Count Step 1
            If .Item(i).Type = msoTextBox Then
                .Item(i).Select
                Selection.Formula = Selection.Formula
            ElseIf .Item(i).Type = msoGroup Then
                For s = .Item(i).GroupItems.Count To 1 Step -1
                    If .Item(i).GroupItems(s).Type = msoTextBox Then
                        .Item(i).GroupItems(s).Select
                        Selection.Formula = Selection.Formula
                    End If
                Next s
            End If
        Next i
    End With
End Sub

Answer 1

本文の最後に追記させていただいたコードで無事に再同期することができました。
ありがとうございます。

Answer 2

課題がいまいち掴めておらず、具体的なコードの提示でなく申し訳ないのですが、もしこれが解決に繋がればと思い回答致します。
解決に繋がらなければ申し訳ありません。

Excelには数式の再計算を実行するボタンがあります。
参考リンク→http://incloop.com/excelで計算結果が自動再計算されないとき/

都度再計算を実行する必要はないのであれば、上記で解決には繋がりませんでしょうか。

Comments

[masa246]

ご回答頂きありがとうございます。
再計算ボタンは試しましたがシートのコピーで同期されなくなったテキストボックスは変わらず同期されないままでした。
また、設定で自動計算になっていることも確認しました。
コピーしたシートのテキストボックスの参照を再度同期させるには今のところ、数式欄に入って数式をもう一度決定するといった方法しか見つかっておりません。
よろしくお願いします。

[akabee]

解決に至りませんでしたか・・。残念ながらこれ以上は私では力になれそうにありません。

ポイントはこの作業の頻度だと思うのですが、１オペレーション毎に数式の更新を実施する必要があるのですかね。私なら、初回だけやればいいのであれば最悪の選択肢として全部手作業で数式を選択するということも視野に入れます。
次にこの事象がＶＢＡで回避できるのかどうかですが、「肝心な数式に入りそのまま更新する方法がわかりません」とおっしゃっているように、私自身も回避可能かどうかが分かりません。同様の問題に悩んでいる方も多いようですね・・・。

[masa246]

ご回答ありがとうございました。

ちなみになのですが、
ActiveSheet.Shapes.Item(i).Formula
ができれば解決するかもしれないのですが、この「ActiveSheet.Shapes.Item(i)」には.Formulaが使えないようなのです。
「ActiveSheet.Shapes.Item(i)」から数式を参照/更新する方法はご存じありませんか？

[masa246]

申し訳ありません。自己解決できたかもしれません。

エクセル2007で試してみないとわからないのですが、シートをコピーした後にリンクをコピー先のブックに変更して、本文追記のコードを実行すれば解決かもしれないです。
現在手元に2007がない為確認できないのですが確認できたらまたお返事させていただきます。
ありがとうございます。

[akabee]

（おそらく参照されたかと思いますが）Shapes.item(i)の戻り値はShapeで、そのメンバにFomulaは無いので無理でしょうね。https://msdn.microsoft.com/ja-jp/library/office/ff835842.aspx
通常の使い方としてはオートシェイプから数式を参照する、しかもＶＢＡからというのは想定されていないと思われます。

ここからは課題解決に直結はしないのですが、実はそこが良く分かっていないところです。
オートシェイプということは図形ですよね？「オートシェイプ」「参照式の入ったテキストボックス」「参照元？の数式」「リンク」「グループ化」がどう関係しているのか。。。
事象として「数式を再度ＥＮＴＥＲしないと反映されない」ということには出くわしたことがあるため回答はしてみたのですが。

解決かもしれないようですね。何もできていませんが、もしお時間あれば結果お聞かせください。