ここから本文です

いつもお世話になっております。 クライアントよりExcelデータを受領しました。...

プロフィール画像

カテゴリマスター

aid********さん

2019/8/2316:29:34

いつもお世話になっております。

クライアントよりExcelデータを受領しました。どのシートも保護がかかっています。

もろもろチェックしていると、数式が入力されているのにロックされていないセルが散見されました。
クライアントに確認したところ「数式のセルは全部ロック、それ以外はロックしないように直してくれ」とのことでした。

数が膨大な為マクロで処理しようと考え、
1.各シートのロックを一度全て解除
2.数式のセルのみ選択
3.ロックをかける
これらをLoop処理させたマクロを作成しました(一番下に記載しております)。

ところがいざ実行してみると、「配列の一部を変更できません」のメッセージがでて停止しました。
この現象自体は理解しています。
しかし、「数式のセルを選択」している(=配列のセルは全部対象になっているはず)のになぜこのメッセージが出るのかと思い再度確認すると、厄介なことに非表示になっている行がありました。

前置きが長くなりましたが、これが本題です。
非表示のセルを含めて「数式のセルを選択」するにはどのような処理をしたら良いでしょうか。

ご教示のほど、よろしくお願い申し上げます。


-------------私が作成したマクロ-------------

Set mb = ThisWorkbook
myfdr = ThisWorkbook.Path
fname = Dir(myfdr & "\*.xls*")

Do Until fname = Empty
If fname <> mb.Name Then
Set wb = Workbooks.Open(myfdr & "\" & fname)

Dim shcnt As Variant
For Each shcnt In wb.Sheets

Cells.Locked = False
Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
Next shcnt

wb.Save
wb.Close
End If
fname = Dir
Loop

閲覧数:
92
回答数:
6
お礼:
500枚

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

tai********さん

2019/8/2319:32:51

「配列の一部を変更できません」

その警告は複数のセル範囲を配列数式で確定したセル範囲があって、その中のセルをを書き換えようとしたときに出ますね。
ところがコードには値を変更するフレーズは微塵もありません。ということは別のコードが動いているか、コードがすべて公開ではない部分もあるということではないですか?

  • 質問者

    aid********さん

    2019/8/2319:41:49

    いつもお世話になります。
    ご回答ありがとうございます。

    >その警告は複数のセル範囲を配列数式で確定したセル範囲があって、その中のセルをを書き換えようとしたときに出ますね。

    私もそう思っておりました。ところが、試しに手動で配列の1部のロックを解除/設定してみたところ、件のメッセージが出ました。
    これ自体が異常な事なのでしょうか。

    書き方が悪く申し訳ありません、マクロはこれ以外の処理はしておりません。
    Screenupdating = False
    くらいのものです。

    また、他のマクロが動いている可能性も考えましたが、見つけることが出来ませんでした。
    VBAの画面を開いても発見できないということは有り得るのでしょうか。

  • その他の返信を表示

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

質問した人からのコメント

2019/8/29 18:55:41

皆様ご回答ありがとうございました。
大変勉強になりました。

ベストアンサー以外の回答

1〜5件/5件中

並び替え:回答日時の
新しい順
|古い順

Qch******さん

2019/8/2403:24:17

>厄介なことに非表示になっている行がありました。
これは、解消して実行しているのでしょうか?

For Each shcnt In wb.Sheets
Rows.Hidden = False
Columns.Hidden = False


非表示シートもあるかもですね。
取り敢えず、おいといて。

示されている問題のコードで出来るはず?なのですが、バグ?であれば

荒業提案です。
Dim window1 As Window
Set window1 = Application.Windows(fname)
window1.Activate
For Each shcnt In wb.Sheets
Rows.Hidden = False
Columns.Hidden = False
Cells.Locked = False
SendKeys "^g", True
SendKeys "%s", True
SendKeys "%f", True
SendKeys "%x", True
SendKeys "%g", True
SendKeys "%e", True
SendKeys "{ENTER}", True
Selection.Locked = True
Next shcnt
Set window1 = Nothing


’---------
Dim window1 As Window
Set window1 = Application.Windows(fname)
window1.Activate
は、意味がないかも知れませんが、
Excel2010は、MDIなので一応。

ご存知だと思いますが、SendKeysはVBE上で実行してはダメですよ。
Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = Trueが
機能しないなら、ダメかもしれませんし、少し処理、重くなるかもです。

あと、ブックの保護もないですよね?
また、制御系は追加してくださいね。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

よこまやさん

2019/8/2401:05:31

現物を見ているのがあなただけなので

ここは中身のデータだけダミーに置換した
生ブックをクラウドなどで開示するのが
多分一番早いと思います。

tok********さん

2019/8/2317:57:46

aka*さんの返答にあった内容をマクロ記録して、
for each sh in worksheet
ここにマクロ記録の内容
next sh
と全シートループする方が楽だと思うんですよねー。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

kai********さん

2019/8/2317:03:55

私のExcel2013で試したところ、

非表示セル含めて
shcnt.Cells.SpecialCells(xlCellTypeFormulas, 23).Locked = True
にて ON (/OFF) できました。


あるいは、、、

Cellsをshcnt指定していない点、とか、

あるいは、、、、

記載されていない部分にてエラーが起きているのかもです。
(シート保護の ON/OFF とか・・・)


見当違いでしたらごめんなさい。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

プロフィール画像

カテゴリマスター

aka********さん

2019/8/2316:42:55

マクロではなく CTRL+Gキーをエンター
セルの選択で 数式に チェックをいれ OK
これで 数式の入っているセルを全部選択できます。
そのまま 右クリックで セルの書式設定を クリックし
プロパティで ロックに チェックすれば いいのでは
この場合 非表示でも ロックは かかります。

最初から数式でもないところにロックがかかっていて それを 解除したいのなら
最初に全セルをロック解除して 上記実行

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる