エクセルVBAでセルのFormulaHiddenが失敗！？

Question

エクセル2000です。ブック内の全シートの計算式を非表示にするため以下のマクロを作成しました。
ところが実行時エラー1004「RangeクラスのLokedプロパティを設定できません」とエラーになります。
どうも結合セルがひっかかるようですが、理由がわかりません。
ご教示願えませんでしょうか？

Sub Formula_Hidden()
 For Each ws In Worksheets
 ws.Activate
    For Each C In ActiveSheet.UsedRange
    If C.HasFormula = True Then
    C.Locked = True
    C.FormulaHidden = True
    i = i + 1
    End If
    Next C
 Next ws
 MsgBox i & "個のセルをFormulaHiddenしましたよん。"
 End Sub

taocat · Accepted Answer

こんにちは。

暫く振りに訪ねてみれば、またまたヘンテコなことにトライしておりまするねぇ。。(^^;

ヘンテコに興味あり、ということで一案。(^o^)

-----------------------------------------------
Sub MerlionXX()

　Dim Sht As Worksheet
　Dim Cnt As Long
    
　On Error Resume Next
    
　For Each Sht In Worksheets
　　　With Sht.Cells.SpecialCells(xlCellTypeFormulas)
　　　　　.Locked = True
　　　　　.FormulaHidden = True
　　　　　Cnt = Cnt + .Cells.Count
　　　End With
　Next Sht
    
　　　MsgBox Cnt & " 個のセル云々"
    
End Sub
-----------------------------------------------

それから、
＞でも変ですねえ、WendyさんのもActivateしてないのに同じSpecialCellsで認識してるのに・・・。
これ、Merlionさんの勘違いではありませぬか？
で、お訊きします。
「動作しない」とはどんな”状態”のことを仰っているのでせう。
少々興味ありです。


ところで、Merlionさん、
当方の尊敬する回答者のお二人、Wendy02さん、KenKen_SPさんのコードに
ちょっとした、場合によっては重大な、ケアレスミスがあることにお気づきでせうか？

コードを眺めただけでそれがお分かりになりましたら
次からは「お師匠さま」とお呼びせねばなりますまいねぇ。。(^o^)~~

以上です。

Wendy02 · Answer

taocat 様、Wendy02です。

どうも、ご指摘ありがとうございます。

>式の無いシートでは、その前のシートのセル範囲（シートも含む）が
>そのまま引き継がれてしまう、ということです。

今、試してみました。
Sheet1, Sheet2, Sheet3 

で、Sheet2 は、まったくの空の状態にし、SpecialCellsで、エラーが発生するように作り、Sheet3 は、２箇所の結合セルと一般の数式セルを置きました。ws に、Sheet2　が入ったときに、ステップモードで見てみると、前のSheet1のシートを繰り返していました。SpecialCells の内容は、Sheet 情報から入っているようですから、クリアしなければ、同じものを、再び、チェックしています。

私の試した方法では、問題はでなかったのですが、ループを繰り返していますから、無駄ですね。

（まあ、私のすることですから、何が抜け落ちでもあるかもしれませんが、ダメならダメでしょうがないとします。)

ループの最後の手前で、

　Set rng = Nothing　'ここを加筆
　Next ws

とすればよいのかな？

なお、カウンターは以下のようにしてみました。

'カウンター
　　　　If .FormulaHidden = False Then
　　　　　If .MergeCells Then
　　　　　　i = i + 1
　　　　　Else
　　　　　　 i = i + .Cells.Count
　　　　　End If
　　　　End If
　　　　

私は、この件は、深追いするつもりはありませんが、もし、よかったら見てやってください。

taocat · Answer

こんばんは、お師匠さま　←　お約束、(^o^)~~
 
＞Setしたシートごとの対象範囲が、クリアされていない
＞数式が存在しないシートがあった場合、個数を正しくカウントしない

Good Job!
その通りです。
式の無いシートでは、その前のシートのセル範囲（シートも含む）が
そのまま引き継がれてしまう、ということです。
On Errorを利用するときはそこら辺りのことには十分注意払いませう。
それからコードを眺めてみればお分かりになると思いますが、
Wendy02さんのではそれは表には現れませんが同じことです。
　
＞ヘンテコですか？けっこう使うと思うんですが・・

いつもいつも、「色んなことにトライされてますねぇ」ではちょと芸がないなと思い。。。(^^;
　
　
KenKen_SPさん、こんばんは。

今回は重箱の隅的な発言、失礼しました。
それもこれも好奇心旺盛な、merlionXXさんの為、
ということでお許しください。
何れにしろ、KenKen_SPさんのコードは非常に参考になりますので
目につけば必ず読むようにしています。
これからも宜しくお願いいたします。

KenKen_SP · Answer

> 個人用マクロブックのPERSONAL.xlsにコピペしていたのです。

ああ、、それなら　ThisWorkbook ではなく、ActiveWorkbook ですね。了解
です。それから、Sheets コレクションは使う意味がないので、Worksheets 
コレクションに訂正します。

Dim Sh As Worksheet

と宣言もしていますのでm(__)m

> ちょっとした、場合によっては重大な、ケアレスミスがあることにお気づき
> でせうか？

わかりました。ご指摘ありがとうございました。こちらの修正は、merlionXX
さんにお任せします。
間違い等は、お気遣いなくご指摘いただけると非常に助かります。ありがとう
ございました。

KenKen_SP · Answer

> でも変ですねえ、WendyさんのもActivateしてないのに同じSpecialCellsで認識してるのに・・・。

気なしに、#4 では Sheets コレクションを使っているのですが、これが原因
かもしれません。Worksheets コレクションなら大丈夫かも...
これも確証はないのですが。

　　For Each Sh In ThisWorkbook.Sheets
　　　　　↓
　　For Each Sh In ThisWorkbook.Worksheets

興味本位で申し訳ないのですが、このように変更した場合でも Sh.Activate 
が必要でしょうか？

よろしければですが、結果を教えてもらえるとうれしいです。

KenKen_SP · Answer

>　やってみましたが、作動しません。

あら？ Excel2002 だと動作してますけど(´・ω・`)？

> Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)を認識できてない
> ようでした。

Excel2000 とかだと、直前にシートを Activate しないと、SpecialCells が
失敗するのかも。試す環境がないので、自信なしです。

Sh.Activate '<--------追加
Set rngHasFormula = _
　　Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)

Wendy02 · Answer

>ただ、セルの数が数えられなくなりましたが。（Areaを数えてるようですね）
そうでした。

MsgBox i & "個をFormulaHiddenしましたよん。"

セルで数えたら、結合したところを、セルの数だけ数えているのは、なんとなく不自然に感じました。実際の、その場所(Area)をロックして、数式非表示にしたことを数えています。セルは、単なる論理的な数ですね。色づけしてみて、そのほうが、良いように感じました。

まあ、それは、お好きなように!

Wendy02 · Answer

元のコードをそのまま加筆してみました。

要点は、２点
　・SpecialCellsで取った場所はセルではなくて、Area にする
　・カウンターの取り方

'-----------------------------------------------------
Sub Formula_Hidden_Wendy()
　Dim a As Range
　Dim ws As Worksheet
　Dim rng As Range
　Dim i As Long
　For Each ws In Worksheets
　　On Error Resume Next
　　Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
　　'Formula Types include All = 23
　　On Error GoTo 0
　　If Not rng Is Nothing Then
　　　For Each a In rng.Areas
　　　With a
　　　'カウンター
　　　　If .FormulaHidden = False Then
　　　　　　i = i + 1
　　　　End If
　　　　.Locked = True
　　　　.FormulaHidden = True
　　　　'.Interior.ColorIndex = 3 '色づけ確認用
　　　End With
　　　Next a
　　End If
　Next ws

　MsgBox i & "個のセルをFormulaHiddenしましたよん。"
End Sub
'-----------------------------------------------------

KenKen_SP · Answer

こんばんは。

結合セル判定は、 IF RangeObject.MergeCells Then でできます。

が、単独セルで MergeArea プロパティーを使っても単独セルを返すだけなので、
結合セル判定は必要ないかも。

Sub Formula_Hidden()
　　
　　Dim rngHasFormula As Range
　　Dim Sh　　　　　　As Worksheet
　　Dim i　　　　　　 As Long
　　Dim C　　　　　　 As Range
　　
　　Const ALL_TYPE = xlErrors Or xlLogical Or xlNumbers Or xlTextValues

　　For Each Sh In ThisWorkbook.Sheets
　　　　On Error Resume Next
　　　　Set rngHasFormula = _
　　　　　　Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)
　　　　On Error GoTo 0
　　　　If Not rngHasFormula Is Nothing Then
　　　　　　For Each C In rngHasFormula
　　　　　　　　With C.MergeArea
　　　　　　　　　　.Locked = True
　　　　　　　　　　.FormulaHidden = True
　　　　　　　　End With
　　　　　　　　i = i + 1
　　　　　　Next C
　　　　End If
　　Next Sh
　　Set rngHasFormula = Nothing
　　' 結合セルの数が含まれる
　　MsgBox CStr(i) & "個のセルをFormulaHiddenしましたよん。"

End Sub

zap35 · Answer

SpecialCellsで数式のセルを選択したらいけそうでした。コードは整理していません。悪しからずです。

Sub Formula_Hidden()
For Each ws In Worksheets
　ws.Unprotect
　ws.Activate
　ws.Cells(1, 1).Select
　Selection.SpecialCells(xlCellTypeFormulas, 23).Select
　Selection.Locked = True
　Selection.FormulaHidden = True
　i = i + Selection.Cells.Count
　ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws
MsgBox i & "個のセルをFormulaHiddenしましたよん。"
End Sub

エクセルVBAでセルのFormulaHiddenが失敗！？

こんにちは。

taocat 様、Wendy02です。

こんばんは、お師匠さま ← お約束、(^o^)~~

> 個人用マクロブックのPERSONAL.xlsにコピペしていたのです。

> でも変ですねえ、WendyさんのもActivateしてないのに同じSpecialCellsで認識してるのに・・・。

> やってみましたが、作動しません。

>ただ、セルの数が数えられなくなりましたが。

元のコードをそのまま加筆してみました。

この回答への補足

こんばんは。

SpecialCellsで数式のセルを選択したらいけそうでした。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

こんばんは、お師匠さま　←　お約束、(^o^)~~

>　やってみましたが、作動しません。