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

質問

エクセル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

回答No.9

taocat · ベストアンサー

こんにちは。

暫く振りに訪ねてみれば、またまたヘンテコなことにトライしておりまするねぇ。。(^^;

ヘンテコに興味あり、ということで一案。(^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^)~~

以上です。

お礼

道士さま、こんばんは。
ヘンテコですか？けっこう使うと思うんですが・・・・。
だから個人用マクロに書いてしまい、作動しなかったようです。

ご教示のコードは完璧に作動しました。ありがとうございます。

> 場合によっては重大な、ケアレスミスがあることにお気づきでせうか？

何度か試して気づいたのですが、一旦Setしたシートごとの対象範囲が、クリアされていない、またはクリアする位置が違っているため、数式が存在しないシートがあった場合、個数を正しくカウントしないということでしょうか？

回答No.12

Wendy02

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

私は、この件は、深追いするつもりはありませんが、もし、よかったら見てやってください。

お礼

ありがとうございました。
今回も大変お世話になりました。

回答No.11

taocat

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

＞Setしたシートごとの対象範囲が、クリアされていない
＞数式が存在しないシートがあった場合、個数を正しくカウントしない

Good Job!
その通りです。
式の無いシートでは、その前のシートのセル範囲（シートも含む）が
そのまま引き継がれてしまう、ということです。
On Errorを利用するときはそこら辺りのことには十分注意払いませう。
それからコードを眺めてみればお分かりになると思いますが、
Wendy02さんのではそれは表には現れませんが同じことです。

＞ヘンテコですか？けっこう使うと思うんですが・・

いつもいつも、「色んなことにトライされてますねぇ」ではちょと芸がないなと思い。。。(^^;

KenKen_SPさん、こんばんは。

今回は重箱の隅的な発言、失礼しました。
それもこれも好奇心旺盛な、merlionXXさんの為、
ということでお許しください。
何れにしろ、KenKen_SPさんのコードは非常に参考になりますので
目につけば必ず読むようにしています。
これからも宜しくお願いいたします。

お礼

ありがとうございました。
今回も大変お世話になりました。

回答No.10

KenKen_SP

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

ああ、、それなら　ThisWorkbook ではなく、ActiveWorkbook ですね。了解
です。それから、Sheets コレクションは使う意味がないので、Worksheets
コレクションに訂正します。

Dim Sh As Worksheet

と宣言もしていますのでm(__)m

> ちょっとした、場合によっては重大な、ケアレスミスがあることにお気づき
> でせうか？

わかりました。ご指摘ありがとうございました。こちらの修正は、merlionXX
さんにお任せします。
間違い等は、お気遣いなくご指摘いただけると非常に助かります。ありがとう
ございました。

お礼

ありがとうございました。
今回も大変お世話になりました。

回答No.8

KenKen_SP

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

気なしに、#4 では Sheets コレクションを使っているのですが、これが原因
かもしれません。Worksheets コレクションなら大丈夫かも...
これも確証はないのですが。

　　For Each Sh In ThisWorkbook.Sheets
　　　　　↓
　　For Each Sh In ThisWorkbook.Worksheets

興味本位で申し訳ないのですが、このように変更した場合でも Sh.Activate
が必要でしょうか？

よろしければですが、結果を教えてもらえるとうれしいです。

お礼

原因がわかりました！
わたしのミスです。
実は、このコードを個人用マクロブックのPERSONAL.xlsにコピペしていたのです。
だから、For Each Sh In ThisWorkbook.Sheetsでも
For Each Sh In ThisWorkbook.Worksheetsでもダメだったんですね？
Activateを入れてうまく行ったのは、こっちはエクセルのBOOKに書いてみたからなのだと思います。

For Each Sh In Worksheets
としたら、個人用マクロでもActiveにしなくとも大丈夫でした。
お騒がせいたしました、すみません、

回答No.7

KenKen_SP

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

あら？ Excel2002 だと動作してますけど(´・ω・`)？

> Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)を認識できてない
> ようでした。

Excel2000 とかだと、直前にシートを Activate しないと、SpecialCells が
失敗するのかも。試す環境がないので、自信なしです。

Sh.Activate '<--------追加
Set rngHasFormula = _
　　Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)

お礼

ありがとうございます。
おっしゃるとおりSh.Activate の追加でOKでした。

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

回答No.6

Wendy02

>ただ、セルの数が数えられなくなりましたが。（Areaを数えてるようですね）
そうでした。

MsgBox i & "個をFormulaHiddenしましたよん。"

セルで数えたら、結合したところを、セルの数だけ数えているのは、なんとなく不自然に感じました。実際の、その場所(Area)をロックして、数式非表示にしたことを数えています。セルは、単なる論理的な数ですね。色づけしてみて、そのほうが、良いように感じました。

まあ、それは、お好きなように!

お礼

ありがとうございます。
Areaにすると、結合してなくとも、たとえばA1:A10にそれぞれ数式が入っていても、その範囲が1個と数えられてしまいますよね？それではちょっと不都合だったものですから。

回答No.5

Wendy02

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

要点は、２点
　・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
'-----------------------------------------------------

お礼

Wendy02さん、お久しぶりで～ス！！。(^o^)/

ありがとうございました。
Area にすると結合しててもOKなんですね。
ただ、セルの数が数えられなくなりましたが。（Areaを数えてるようですね）

補足

i = i + a.Cells.Count　でセルの数も取得できました。ありがとうございました。

回答No.4

KenKen_SP

こんばんは。

結合セル判定は、 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

お礼

ありがとうございます。いつもお世話様です。
やってみましたが、作動しません。
On Error Resume Nextを削除したところ、
Sh.Cells.SpecialCells(xlCellTypeFormulas, ALL_TYPE)を認識できてないようでした。

回答No.3

zap35

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

お礼

な～るほど！
こういうやりかたもあるんですね。セルをFor next で探さなくともいいですね。
勉強になりました。
ありがとうございます。

回答No.2

imogasi

的外れかもしれないが、下記では改善されませんか。
Sub Formula_Hidden()
Dim c As Range
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
MsgBox ws.Name
MsgBox ActiveSheet.UsedRange.Address
For Each c In ActiveSheet.UsedRange
If c.HasFormula = True Then
MsgBox c.Address
c.Locked = True
c.FormulaHidden = True
i = i + 1
End If
Next c
ActiveSheet.Protect "", True, True, True
Next ws
MsgBox i & "個のセルをFormulaHiddenしましたよん。"
End Sub
Ｍｓｇｂｏｘは確認のために入れたもので、意味はありません。

お礼

ありがとうございます。
やはり結合セルでえらーになってしまいます。