Q&A
最終系ばかりを意識して迷走している気がします。とりあえずＶＢＡで
・セルへの文字入力のしかた。
・単一セルの保護の掛け方
・複数セルの保護の掛け方
・最終行の求め方
・シート構成の保護のしかた
あたりを『順に』調べていってみてはどうでしょうか。
VBA初心者です。すみませんが教えてください。
このファイルは「管理簿」「ログインID認識」の2つのシートがあり、「ログインID認識」でログインIDを取得します。
それをもとに「ログイン認識」シートにVLOOK関数で管理者の名前を抽出させ、
「ログイン認識」VLOOK関数で抽出させたユーザーネームを「管理簿」シートの黄色の列に、入力規則によりプルダウンで選択させ、管理者がログインした時だけ管理者のユーザーネームがプルダウンで表示されるようになっています。
Excel2013で作成
１. B列を基準にして、B列の最後の値（書式ではなく）から上の行を保護したいのですが、
書式は関係なく最終の値を認識してその上の行を指定する方法が分からずに困っております。
（イメージ図で言えば「E12」が最後の値なので11行目～15行目までです）
下方向に最終行を検索するべきなのかと思うのですが、検索方法は
Dim maxrow
maxrow=Range("E65536").End(xlUp).Row
で合っていますか？これだと最終セルだけが認識されているような気がして間違っているような気がします。
その上の行を全部指定する方法なのですが、どのように行全体を指定したらいいのかが分からず困っております。
保護していない行の空白セルは自由に記述したいため（イメージ図では17行目以降の空白セルです）
UsedRange.SpecialCells(Type:=xlCellTypeBlanks)で空白セルを認識させています。
これだと、空白セルだけの認識なので、「行の保護」と相反していてどのように指定したらいいのか分からない
のですがどのように指定したらいいのか、教えていただけないでしょうか。
2.「保護」マクロを実行したら黄色の列も保護したい。（N列、P列）
単一列を指定するのでN列を指定するため
Columns(14).Interior.Locked = True
と書いて実行してみました。そうすると、保護されるセルと保護されないセルがあり、
（「N11」「N21」セルは保護されたのに「N18」は保護されない）
どうして同じ列なのに保護されるセルとされないセルがあるのか分からず悩んでいます。
3. ログインID認識シートをプロパティで「Vislible/0-xsheetHidden」で非表示に
して、更にパスワードかけたいのですが、
ActiveSheet.Protect Password:="●●●●●"
のようにパスワードを指定する方法などあるものでしょうか？
基本的な考え方も勉強中で、おかしいコードなどが混ざっているかもしれません。
すみませんが、ご教授いただけませんでしょうか。
Option
1Sub 保護() '管理簿シートに記述 2 If ActiveSheet.ProtectContents = False Then 3 4 Cells.Locked = True 5 ActiveSheet.UsedRange.SpecialCells(Type:=xlCellTypeBlanks).Locked = False 6 7 ActiveSheet.Protect Password:="●●●●●●" 8 End If 9End Sub 10コード
Option
1Private Sub Workbook_Open() 'ThisWorkbookに記述 2 Application.ScreenUpdating = False 3 4 Dim environmentstring As String 5 Dim username As String 6 Dim i 7 8 i = 1 9 10 Do 11 environmentstring = Environ(i) 12 13 If Left(UCase(environmentstring), 9) = "USERNAME=" Then 14 username = Mid(environmentstring, 10, Len(environmentstring)) 15 Sheets("ログインID認識").Cells(3, 1).Value = username 16 17 Exit Do 18 End If 19 20 i = i + 1 21 22 Loop Until Environ(i) = "" 23 Application.ScreenUpdating = True 24 25End Sub 26コード
提示いただいたコードですが、これは実際に動かそうとしているコードのコピペではありませんよね？
変数名のないDim宣言や、Cellsメソッドのsがない、findrang変数が使用時にfindrngになっている、行＝loked=trueは行.Lockedかな？等々、誤記が多すぎてちょっと読み取る気になりません。
UCASEの比較相手が小文字だったりもしてますし、原文を手で書き写したのかもしれませんが、これではあちこちエラーだらけでエラー原因の特定もできません。
というかOption Explicitも超えられませんよね(^-^;
コード提示はできるだけ正確にお願いします。
あと、せっかく最初の関数をマークアップで記載してくれているのですが、中身のネストがぐちゃぐちゃなので結局整っていません。
ここらへんも整えてくれるとうれしいです。
マークアップは複数書くこともできますので、後半のワークブックに記載するコードもマークアップで記載してもらえると助かります。
コード内容に関しても指摘したいこと(意味不明なOnErrorや、Trueに戻されていないScreenUpdatingなど)がありますが、まずはしっかり質問文を整えてください。
行、列の認識が誤っているため、質問文も正しい表現に修正してください。行は横方向の塊(1行目、2行目...)、列は縦方向の塊(A列、B列...)です。
1)
表の中のNo.の列って何が入るのでしょう？特に途中行が飛んで、急に99と100が出てきますが、99と100は特別な意味があるのですか？
また、そのほかの番号は99以上にはならないということですか？
2)保護を掛ける意味はなんでしょうか？
特に管理者以外がその表を見ることがあるのでしょうか？
ファイルを開くときにパスワードを入力させて、
読み取り専用か編集も出来るようにするかを場合分け出来ないでしょうか？
どんな人が利用して、だれなら編集可能にするのか、そして管理者とは何の管理をする人か、編集権限の範囲を明確にしましょう。
（アプリ製作者も含めて）
それから、プルダウンで管理者の名前を選択させる仕様のようですが、
誰が今操作しているかを操作しているパソコンで判断するということはできないでしょうか？
できれば、その辺も自動で出来た方が良いような気がします。
また、管理者以外でも、セルに記入が出来そうな気がしますが、
入力規則のリストで、一覧以外は入力できないよう設定しているということですか？
ぼくなら、プルダウンめにゅうから選ぶくらいならキーボードで入力させてもらった方が楽かも。
手がマウスに行ったりキーボードに行ったりするのは煩雑です。
3)
基本的に入力中の行以外のセルはロックして触らせたくないということですか？
4）
提示の表で、入力が必須な列を教えてください。
それが全部埋まらないと次の行に入力できないようにしたらいいと思うのですが。
とにかく事細かく想定している仕様を説明しないと、
期待した結果は得られないかなと思います。
1)Noは通し番号が入ります。1から下にデータが500件以上の年間データが入っていきます。
今は、データが入っていくものと仮定して作成した為分かりづらくなってしまいました。すみません。
2)保護をかける意味ですが、基本的に表を編集するのは管理者ではない方々です。管理者がはデータを見てサインとする時に見ます。管理者も5～7人ぐらいおります。共有サーバーの中で誰でもファイルを開くことのできる環境にあります。管理者ではなく実際にデータを入力する担当者は20人前後です。この方々が主に編集を行いますが、サインをする管理者もサインするために入力できなければなりません。頻繁に使うファイルなのであまりに開くのが遅かったり、重かったりすると不便で多少面倒でも軽くしようと考えています。管理者がサインした後に担当者がそのサインを消去してしまったりしては困るのでロックしようとしています。誰が今操作しているかは「Excel登録ネーム（●●さん）が編集中です。読み取りで開きますか」と表示され誰かが開いていると他の人は読み取り専用でしか開けません。管理者以外がセルに記入できないように入力規則を設定したので、なりすましできないと思ったのですが、「山田」さん（管理者）の名前を「田中」さんがコピーして貼り付けすると入力規則に反しないため簡単になりすましできることが判明し「管理者サイン」列をロックしなければと考えています。プルダウンメニューはなりすまし防止の為に設定しましたがその意味がないので直接入力したほうがいいかもしれないと思っております。確かにマウスとキーボード行き来するのは無駄かもしれません。
3)入力中の行以外は触らせたくないわけではなく、管理者がサインしたあとに改ざんされるのが困ります。管理者間でなりすましして、他人の名前を入力されても困ります。責任の所在が分からなくなるからです。
4)担当者が入力の必要な列は「管理者サイン」（黄色の列）以外の列です。
管理者が基本的に入力するのは「管理者サイン」N列、P列です。そして、その他は担当者が入力します。イメージとしては部下が作成したデータを管理者が許可するイメージです。
回答3件
0
んと、なんとなくイメージがつかめましたが、
長文が多すぎて何が要点か解りません。
まずはご自分が、今何をすべきかを把握するため、
パソコンを離れ手書きで作業の流れを整理してみてはいかがでしょうか？
それで、ご自分の意図が回答者に伝わるように説明できるようになれば、
その説明をVBA語に翻訳すればエクセル君にも伝わる(＝意図通り動いてくれる）と思います。
で、、、、
まず、このアプリを作る目的は、
「誤入力を出来る限り防ぎたい。（特に入力済みのデータの上書きを防ぎたい。）」
ですかね？
こういうアプリでは、「データをユーザーに直接触らせない。」が基本になります。
つまり、本来のデータベースとして使うシートに直接ユーザーに書き込ませないように作ります。
よくあるのは、一件一件のデータを入力させていく方法がとられます。
参考URL>>
ユーザーフォームの作り方
ただし、このやり方だと、複数行を一度に編集できるエクセルのいい点を消すことになりますので、
個人的に好みではありませんが、方法として一案ではあります。
そこで僕がお勧めしているのは、「シートをユーザーフォームのように使う」という方法です。
使い慣れたエクセルのシートを設定していくだけですからそれほど苦にはならないかと思います。
ただし、VBAで制御するのは少し難しくなるかもしれません。
でも、シート上で入力フォームを作ると、エクセルの機能がふんだんに使えますので
（もしかしてテーブル機能で入力をより楽にできるかも？）、
そういう面では開発が楽になると思います。
いま、やりたいのは、
「データ入力者によって編集できるセル範囲を変えたい。」
という事だと思いますので、
データ入力者により別の（同じに見えるが実質は違う）シートを使ってもらうようにすれば、
シート毎に設定を変えればいい（背景色を変えれば、どのシートを使っているかわかりやすいかも？）ので、プログラムでちょこまかセルの保護範囲を制御するより、
シートの表示・非表示を切り替えるだけなので、開発が楽になるのではないでしょうか？
と、いうことで、まずは、入力者のレベル分けと、
それぞれが編集できるセル範囲を整理するところから始めてはいかがでしょうか？
例えば、
業務のデータを入力する人（作業者）→B列からM列
データのチェックをする人（管理者？上司？）→B列からN列
最終にデータチェックチェックする人（部長？）→P列
（A列とO列はVBAで自動で入力。他の日付列も自動で入っちゃえばいいのかな？）
アプリ管理者（データベースの管理者）→すべてのデータ
こんな感じで制御したいのかなと思いますがいかがでしょうか？
これに、操作者の一覧を作り選択してもらい、
操作者の氏名を保持しておけば、
他の名前を使えなくなります。とは言っても手入力は出来ちゃうので、
その辺は別途制御を考えないといけないですが、それは後回しでいいのではないでしょうか？
まずは、運用してもらえるアプリを作り、
機能は後から少しづつ足していく感じでいいでしょう。
（ただし、全体の設計が上手くできてないと、途中で全改修することにはなると思いますが、
開発の全てを今から勉強するものに任せたつけでしょう。）
あまり凝ったものに最初から挑戦しても、完成せずにプロジェクトがとん挫することになるだけです。
とりあえず、
0）ファイルを開いたときに名前一覧シートを表示する
1）一覧シートから名前を選択
2）名前によりアクティブなシートを切り替える
3）アクティブなシート（それ以外のシートは非表示）にデータを入力してもらう
4）ブックを閉じるときに追記されたデータでデータベースを更新
という流れをまずは作ってみてはいかがでしょうか？
ファイルを開くときに重いとかどうとかよりは、
まずはこのアプリが稼働することの方が重要かと思います。
エクセルを開くとき2回目からは初回より起動が速くなりますし、
閉じるときも、ブックだけ閉じてアプリはそのまま起動した状態を保てば、
エクセルを起動する時間を省けます。ファイルを開くマクロをブックに用意して、
開くファイルと別にすると、エクセルを終了しないでブックを閉じる制御が簡単になるかもです。
データ数をみる限りそんな作業に支障が出るほど、重くなる可能性はないと思いますが。。。。
どうしても素早く起動したいなら、プログラムの改修や、ファイルの容量を抑えることに
時間をかけるより、ハイスペックなPCを導入したほうが対費用効果が出ると思います。
（案外違いがないかもですが、うちにあるパソコンは電源入れて5秒くらいで使える状態になりますが、
それからエクセルを起動しても30秒かからないんじゃないかなぁ。。。2003だから速いのかな＾＾；
会社で支給されたパソコンは1分以上待たないと使える状態にならない＞＜エクセルも合わせると2分以上待っているのかなぁ。。。アドインソフトを不要だけど入れてるからめっちゃ遅い。）
（ついでに家のパソコンはエクセル2003＾＾；でも500行しか使わないなら2003でまったく不便がないのですが・・・・＾＾；；）
そんなこんなで、
作りかけてみて、躓いたらそのテーマで質問しながら開発していってはどうでしょうか？
いまの質問では、あれもこれもそれも思いつくことを羅列されていて、非常に答え難いです。
でも、質問しながら作っていては、1年掛かっても完成しないかもしれませんが。。。＾＾；
それくらい他人に使ってもらえるアプリを作るのは難しいです。
ここのサイトに上げるサンプルコードでもモノによっては数時間かけて書いているものもあるし、
本当に他人に使ってもらうように作るのなら、エラー回避などを含めた、動作確認や不具合の修正など、
そして、1年後に読んでも自分でわかるよう精査したりコメントを入れたりするのに、
1週間ぐらいかけたいのが本音です。
（実際、1年後に一部修正しようとして読んだら、自分で書いたコードが意味解らなくて、
2週間かけて全部新たに作り直したことがあります＾＾；；）
参考になれば幸いです。
P.S.
他人には簡潔にと言っておきながら、
とりとめもない長文すみません。
投稿2019/03/29 12:32
総合スコア2136
回答していただきありがとうございます。
なるほど！ユーザーフォームで入力させると便利ですね。
入力者により表示するシートを切り替える方法、思いつきませんでした。
名前一覧で表示するシートを分けていけば確かに簡単で便利ですね。
人によって表示するシート分ける方法を試してみます。
とても参考になりました。教えていただきありがとうございます。
0
ベストアンサー
とりあえずやりたいことをごちゃっと全部並べあげてしまうと、収集がつかなくなってしまいます。
まずは機能単位に整理して考えましょう。
大きく２つの機能（ブックオープン時の機能/保護ボタン押下時の機能）がありますよね？
まずこの２つで、わけられる部分ははわけて考えましょう。
それぞれについて、具体的な機能をざっと洗い出すと
【ブックオープン時の処理】
・パスワードのチェック処理
・シートの表示/非表示
・ブックの保護（非表示シートを再表示させないため）
【保護ボタン押下時の処理】
・ロック対象範囲の取得（E列最終行の取得を含む）
・対象セル範囲のロック
・特定セル（空欄/サイン欄）のロック解除
・シートの保護
・保護状態の表示(B4セル)
※保護解除時に「解除」を表示する処理は【保護ボタン押下時の処理】とはタイミングが異なりますので別途処理を用意する必要があります。
といった具合でしょうか。
ただでさえ初心者さんがチョイチョイと作れる物量ではなさそうなのに、これをごちゃっとひとくくりに考えてしまっては、手の付けどころもわからなくなってしまいませんか？
細かな機能単位に絞って、ひとつひとつ実装を積み重ねていきましょう。
1について
検索方法は
Dim maxrow
maxrow=Range("E65536").End(xlUp).Row
で合っていますか？
検索方法はあっていますが、このコードの意味は理解できていますか？
これはE列最終セルRange("E65536")から[Ctrl+↑]で移動.End(xlUp)したセルの行番号.Rowを取得し、maxrow変数に格納しています。
あるセルから[Ctrl+↑]で移動すると、データが入力されているセルの端まで移動します。
つまりE列最終行までデータが詰まっていない状況であれば、このセルから[Ctrl+↑]操作を行うことで、E列のデータが入力されている最終セルまで移動できるというわけです。
上記コードでは、見つけたセルそのものではなく、セルの行番号を取得しています。
今回、E列の最終データセルそのものに何かをしたいわけではありませんので、セルを単体で持っていても意味はありません。
データ範囲の最終行を知るための素材として利用しているわけです。
今回ロックしたい範囲は、11行目から最終データ行の１行手前ですよね？
これを取得したmaxrowを使用して表すとRange(Rows(11), Rows(maxrow -1))が対象の行範囲ということになります。
2について
私の手元の環境ではColumns(14).Interior.Locked = Trueはエラーとなります。
Lockedプロパティは対象セル範囲(Range型)に対するプロパティですので、Columns(14).Locked = Trueのような記述になると思います。(Columnsは指定列を指すRange型を返す。)
この記述で処理してもロックの状態がばらけるのであれば、ロックに失敗しているけどエラーを無視して処理を進めている可能性くらいしか原因が思いつきません。
3について
シートを非表示にしてブックを保護すれば、保護を解除しない限りシートを再表示することはできなくなります。
ブックを保護する際にパスワードをかけることもできますが、やりたいことはそういったことでしょうか？
投稿2019/03/28 07:03
総合スコア3013
回答いただきありがとうございます。
ろくに見直しもせず不快な失礼な質問をあげてしまい、すみませんでした。反省しています。
「maxrow=Range("E65536").End(xlUp).Row」の意味自分では理解したつだったのですが、大きく間違っていました。
3のシートの再表示ですが、
共有サーバーの中に保存したファイルを数人が交互に開いて処理を行います。「ログイン認識」シートはログインIDを表示させているため、他人のログインIDをコピーして貼り付けてしまえば「山田」さんがログインしているのに「田中」さんになりすましてプルダウンで名前が表示されるようになります。それを防止しようと思い、、「ログインID認識」シートを非表示にしてパスワードをかけようと思いました。しかし、他人の氏名をコピーして貼り付けれることが分かり行を保護しなければいけないと思い質問2の行の保護をしなければならないと考えました。管理者サインの「N列」「P列」です。管理者のみがパスワードを知り、再表示できるようにする。基本的には常に非表示にしておこうと思っております。
（丸投げではなく）自分なりに思慮しているのも見て取れますし、特に不快とは思っていませんよ。
ただ少しやることが多すぎて、整理が追い付いていないのかな？と思っています。
「木を見て森を見ず」という言葉がありますが、時には敢えて森を見ないことも必要です。
それでも林くらいは見すえた方がいいんですけどね。
木を１本ずつ処理していくとそのうち森を見るのが苦しくなくなってきますので、がんばってひとつずつ片づけていってください。
---
３について
>管理者のみがパスワードを知り、再表示できるようにする。
>基本的には常に非表示にしておこうと思っております。
N列/P列はロックして保護、列の非表示まではしない。
「ログインID認識」シートは常に非表示。パスワードを知る人のみが再表示可能にしたい。
という認識でいますがあっていますか？
シート非表示についてはたぶん下記サイトの記事のようなことをやりたいのだと思います。
http://office-qa.com/Excel/ex133.htm
これをVBAで実装することもできます。
```
Workbook.Protect Password:="xxx", Structure:=True, Windows:=False
```
こうしておくとブックの保護を解除できる人でなければ「ログインID認識」シートを再表示できなくなると思います。
ありがとうございます。やりとげたい！という気持ちばかりが前に進み、実力を伴わないまま無理矢理進んでいるような状態ですが、Jawaさんや皆さんに教えていただき少しずつでも前進したいと思っております。
シートを非表示にする方法、こんな方法があったのですね。新たな発見です！
教えていただきありがとうございます。
N列/P列は管理者のみがパスワードを入力してシート保護解除することで編集可能にし、ロックして保護、列の非表示まではしないようにしたいです。
>「ログインID認識」シートは常に非表示。パスワードを知る人のみが再表示可能にしたい。
>という認識でいますがあっていますか？
はい。あっております。
教えていただいた最終行から上の範囲選択ですが、自分なりに考えてみたのですが、
見ていただけますか？
「最終行から1行上の範囲を取得」
Dim maxrow
Dim 対象範囲
「E列最大セル「E65536」から↑で行番号を取得」
maxrow = Range("E65536").End(xlUp).Row
「11行目以降「maxrow - 1」最終行から1個手前を取得し「対象範囲」に格納」
対象範囲= Range(Rows(11), Rows(maxrow - 1))
「N列とP列をロックする」
Columns(14).Locked = True
Columns(16).Locked = True
やはりうまくいきません。対象範囲が格納されていないようです。
Dim maxrow
Dim 対象範囲
maxrow = Range("E65536").End(xlUp).Row
対象範囲= Range(Rows(11), Rows(maxrow - 1)).Locked = True
「Set」「Slect」を使うべきでしょうか？
離れた列を指定したいときは、どのように書いたらいいでしょうか？
Columns(14).Locked = True
Columns(16).Locked = True
と続けて書いてもエラーになってしまいます。
Columns(14,16).Locked = True
Range（"N:N","P:P"）.Locked = True も駄目でした。
Jawaさん、詳しく分かりやすく教えていただきありがとうございました。
もっと勉強します。もしよければ、また教えてください。
回答していただきありがとうございました。
0
直接な回答ではなく、テクニック面の話です。
保護プロシージャ内の処理ですが、プロシージャが記述されている「管理簿シート」に対して操作をしたいのだと思われます。
そのように、「プロシージャが記述されている自分自身」に対して処理をしたい場合はMeキーワードを使うと良いです。
vba
1Sub 保護() '管理簿シートに記述 2 If Me.ProtectContents = False Then 3 Me.Cells.Locked = True 4 Me.UsedRange.SpecialCells(Type:=xlCellTypeBlanks).Locked = False 5 Me.Protect Password:="●●●●●●" 6 End If 7End Sub
Meを使うメリットとして、(今回の例では)「どのシート」を操作しているのか、がわかりやすくなります。
また、ActiveSheetのあとに.を打っても何も出てきませんが、Meの後ろなら、使える処理の一覧が出てきます(ProtectContentsなど)。
そのため、処理を記述する際の打ち間違えなどが減り、素早く正確に記述できるようになります。
Workbook_Openの中でUSERNAME環境変数の値を取得しようとしていますが、予め名前が分かっている場合はEnviron(環境変数名)で取得できます。
このことを踏まえると以下のように書けます。
vba
1Private Sub Workbook_Open() 'ThisWorkbookに記述 2 Dim wsログインID認識 As Excel.Worksheet 3 Set wsログインID認識 = ThisWorkbook.Worksheets.Item("ログインID認識") 4 5 wsログインID認識.Cells.Item(3, 1).Value = VBA.Environ$("USERNAME") 6End Sub
wsログインID認識の変数は無くてもよく、以下のように１行で書いてしまっても良いのですが、Worksheetsの後から入力補完が切れてしまうため、それを嫌って変数に入れています。
vba
1' 以下のどの書き方でも動作は同じ。 2Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME") 3Me.Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME") 4ThisWorkbook.Worksheets("ログインID認識").Cells(3, 1).Value = Environ("USERNAME")
投稿2019/03/31 07:44
総合スコア2166
回答していただきありがとうございます。
「Me」の使い方、大変勉強になりました。
「Me」を活用して、打ち間違いを減らしていこうと思います。
「Environ(環境変数名)」で取得するとこんなに簡潔にすっきりと書けるのですね。
驚きました！ありがとうございます。
あなたの回答
tips
プレビュー
