2018.04.13スポンサーリンク数百人規模の中規模の住所録や顧客データをエクセルで管理している事業所って意外と多いですが、データを検索して修正するのってすごく面倒じゃないですか?面倒だなあって思っているエクセルの住所録を自分だけが使うだけならば問題はないのですが、みんなで入力や修正を行うと、全角や半角など他の人が使うとなると時々問題が発生した経験ってありませんか?入力も正確に、検索も修正も簡単にして作業効率アップしたいですよね。ユーザーフォームで入力も検索も修正も瞬間技エクセルデータでのトラブル問題は、作成者が意図しない入力方法や、書式設定や数式を入れてあるセルを消されてしまって正しく表示されなくなってしまうことなど。このようなことを防ぐ方法として、通常のワークシートのセルへの直接入力をさせないで、別枠の入力専用フォームをエクセルのワークシート上に表示させて、ここで入力をしてもらうようにします。住所録なので探すことも必要なので、同じようにオリジナルの検索フォームも作ってしまえば、とても便利で作ったエクセル住所録を壊されないように守れますね。波乗りアヒルところでユーザーフォームって何?って思った方、まずはこちらを見ていただくとユーザーフォームを使ってオリジナル入力フォーム作りたくなりますよ!ユーザーフォームでオリジナル入力画面を作るオリジナルの入力フォームを作ることができるユーザーフォームの作り方をご紹介します。今使っている住所録を確認してみよう住所録の項目をワークシートに作ろう日付の基本項目を作っておくなぜ必要?住所録入力フォーム【顧客番号確定】ボタンにVBAコードを登録しよう【郵便番号検索】ボタンにURLを登録しよう【新規登録】ボタンのVBAコードユーザーフォームを作る前に準備まず、今使っている住所録をそのまま使うのが一番いいのですが、ユーザーフォームを使うという事はEXCEL-VBAを使うという事なので、入力やデータ検索の為に実際にやってみて思ったことを書き出してみました。今使っている住所録を確認してみよう作成済み住所録を修正しよう結合セルは使わないフリガナは苗字と名前を別々のセルに分ける電話番号はハイフンを入れない誕生日などの日付は和暦を使わず西暦を使う1、結合セルは使わない見た目などを優先するとセルを結合して表示してしまえば便利で簡単なのですが、データの検索や書き換え(修正)、追記などを行う住所録などではエラーの原因になるので使わない方がいいです。データの検索や修正などをExcel-VBAで行うと、セルの番地が不明確になったり検索する基準から右に5個目のセルの値を修正なんて事が結合されているとできません。並べ替えもできなくなるので、一旦結合セルを解除してデータ修正などしてから再度結合してなんて大変なので、無駄なExcel-VBAコードは使わない方が処理も早くなるので結合セルは使わないことをおススメします。それでもどうしも使わなくてはいけない場面ってあるので、このようにVBAコードを組み合わせれば問題解決しますよ。連続でセルの結合と解除をエクセルVBAで繰り返すエクセルでリストなどの表をつくる時に同じ値はセルを結合して1つにまとめたいですが、たくさんあると大変なので自動的に見つけて連続で結合させたい時ありませんか?VBAで簡単解決します。やる必要はありません。検索する時に名前のフルネームを漢字で検索せずに、カタカナの苗字で探しませんか?そうするとフリガナで名字だけ入力すれば、該当リストが表示されて選択すれば検索完了しますので、フリガナの苗字と名前を分けておくと検索に便利になったのでおススメですよ。3、電話番号はハイフンを入れずに入力これはフリガナ検索でうまく検索できない時、例えば「中田さん」はナカタ?ナカダ?どちらで登録したか分からない時ありますよね。すると、電話番号ならば間違えないので電話番号検索を付け加えたのですが、入力するスタッフによりハイフン付ける人と付けに人がいると検索ができなくなりますので、数字だけにした方がいいですよ。和暦を使っていますか?それとも西暦を使っていますか?もちろんどちらが正しいなんてことないのですが、誕生日の計算などを数式で自動計算させるならば、西暦の方がシンプルに簡単で、今日の日付から誕生日を引けば年齢が計算できますからね。その為に入会用紙などには誕生日を西暦で書くように促しているのですが、和暦で書く人意外といますよね。西暦に直すのが結構面倒なので、エクセル関数とVBAを組み合わせてユーザーフォームに和暦を西暦に変換表示させる機能を付け加えておくと便利ですよ。波乗りアヒルもし今使っている住所録が上記に合致していたらそのまま使えそうなので、ユーザーフォームで簡単・便利に改良しましょう。違っていてもこの4つぐらいならば修正できそうじゃないですか?住所録の項目をワークシートに作ろうせっかくユーザーフォームを使って住所録を簡単・便利に作り変えるのだから、既存の住所録を改良して使おうって思いますよね。最初から作るつもりで見てもらえると、分かりやすいかな?また、修正してユーザーフォームだけ追加するならば、ここはクリアしているって思えば次へ進んでくださいね。画像クリックで拡大しますエクセルで作る住所録のワークシートは全部で15項目ありますが、人それぞれによって不必要な項目や差し替えなければならない項目などがあると思いますので、ご自由に設定して下さいね。ここまでの作業は全く難しくないですが、簡単便利に入力・検索・修正するためにユーザーフォームを使うのですが、全てをユーザーフォームで行う必要はありません。エクセル関数とExcel-VBAを上手に組み合わせた方が簡単で、作る時間も短縮できました。1、誕生月お誕生月にクーポンを郵送しているので「誕生月」という項目を使っていて、生年月日(セルAI列)から「月」を取り出して表記するエクセル関数「MONTH」を使っています。生年月日が空欄だとなぜか1と表示されてしまうので、IF関数で空欄処理を付け加えるとこのようなエクセル数式になります。住所の確認などの為に毎年1回更新手続きを行っているので、更新手続きを行っていただいたお客様と未更新のお客様を識別するために「更新状況」という項目を使っていて、今年度の基準日よりも更新日の方が新しい(数値が大きい)時は「更新済み」、古い(数値が小さい)時は「未更新」とそれぞれ表示します。更新日が空欄だと”未更新”と表示されてしまうので、IF関数で空欄処理を付け加えるとこのようなエクセル数式になります。実際のところ年齢が分かったからと言って何かに使っているわけではないのですが、一般的に年齢という項目ってあるので用意して、「日付の差」を計算するエクセル関数「DATEIF」を使っています。誕生日が空欄だと1900年を基準にされてしまうので、2019年だと119歳って表示されてしまうので、ここでもIF関数で空欄処理を付け加えるとこのようなエクセル数式になります。年齢算出で使うエクセル数式=IF(AC2=””,””,DATEDIF(AI2,$P$2,”Y”))波乗りアヒルこれ以外の12項目は全てユーザーフォームで入力した値が自動的にそれぞれの項目に自動転記されるようになっています。エクセル関数で設定するうえで、今日の日付や更新基準日などが無いと計算できないので準備しましょう。住所録データはAA列から使っていてその左側P列~S列に設定セルP2が「2019/8/27」と表示していますが、エクセル関数TODAYで現在日時を自動表示させて、セルQ2で「年」を取り出して表示、セルR2で月を取り出しています。セルS2は更新基準日を入れて更新状況を比較できるようにしています。さあ、いよいよ本番です。入力フォーム作りましょう!スポンサーリンクなぜ必要?住所録入力フォームそもそも、ワークシートに直接顧客情報を入力するのがダメなの?って思う方は、このページにたどり着いていないと思います。最初に作った住所録入力フォーム新規入力や検索など使っているスタッフからのリクエストなどを次から次へと取り入れて行ったら、こんな感じになっちゃいました。備考欄はメールアドレスに改良しますこの入力フォームで新規登録ボタンを押せば、住所録リストの最終行に新規追加登録され、修正登録ボタンを押せば、該当者を探して見つけたらそれぞれの項目を上書きするようになります。顧客番号が分からない時などは「検索」で電話番号検索やフリガナ検索ができるようになり、生年月日が和暦で記載されている時は西暦を調べることもできるようになりました。波乗りアヒル郵便番号を入れると住所が自動表示させることも可能なのですが、ものすごく知識が必要なのと、ネット接続時でないとエラーになるので、使っていません。ユーザーフォームでオリジナル入力画面を作るオリジナルの入力フォームを作ることができるユーザーフォームの作り方をご紹介します。エクセルの学校に行っていないおじさんでもできたので、仕事の作業効率化の為にぜひマネして取り入れてみてはいかがですか?tripbowl.com2019.09.09スポンサーリンク【顧客番号確定】ボタンにVBAコードを登録しようユーザーフォームの基本でもあるプログラムを実行させるためにコマンドボタンに作業を登録しなくてはいけません。顧客番号を入力するテキストボックス(TextBox1)に顧客番号を入力して顧客番号確定ボタン(CommandButton1)を押すのが当たり前なのですが、うっかりテキストボックスに何も入力しないで押してしまった時に、メッセージを表示してあげると親切ですし、誤った操作という事をお知らせできるので設定することをおススメします。If複数のテキストボックスが空欄の時にメッセージを出す方法ユーザーフォームって自分で使うとエラーにならないのに、他人が使うとエラーが出る。TextBox1に入力されている顧客番号がセルP11に転記され、VLOOKUP関数により住所録リストより探してセルP12に表示させます。登録時は「名前」未登録時は「0」を表示で判別=IFERROR(VLOOKUP(P11,AB2:AD1000,2,0),0)セルP12の検索結果により未登録番号か登録済み番号化を判別させます。Then波乗りアヒルここでは数式をセルに入力していますが、1つの検索条件で複数のセルの値を探すときは、Findメソッドを使う方法もありますので覚えておくと非常に便利ですよ。VLOOKUP関数の代わりにエクセルVBAでやる方法エクセル関数のVLOOKUP関数。データ検索するのに便利なのですが、数式だと困るのでVBAを使ってVLOOKUP関数と同じことができると便利で、仕事の作業効率化になるのでぜひマネして取り入れてみてはいかがですか?tripbowl.com2019.09.09探したいセルの値が何行目にあるか調べる関数VLOOKUP関数で右に何列目ってできるけど、左にって思って「-3」ってやっても無理ですよね。MATCH関数を追加すればできる。Then)検索結果が名前でなく”0”だった時はメッセージを出すという事ですね。新規登録します”)複数のテキストボックスが空欄の時にメッセージを出す方法ユーザーフォームって自分で使うとエラーにならないのに、他人が使うとエラーが出る。顧客番号が登録済みの場合は、名前・フリガナなどの登録済みデータリストから探し出して各テキストボックスに表示させます。メッセージで「登録済番号です。修正実行しますか?」と表示して「はい」「いいえ」で選択させて、「はい」を選べば、各項目を修正可能になり「いいえ」を選ぶと入力フォームを閉じてメインメニューフォームに表示が切り替わります。MsgBox(“登録済番号です。IfVLOOKUP関数の代わりにエクセルVBAでやる方法エクセル関数のVLOOKUP関数。データ検索するのに便利なのですが、数式だと困るのでVBAを使ってVLOOKUP関数と同じことができると便利で、仕事の作業効率化になるのでぜひマネして取り入れてみてはいかがですか?tripbowl.com2019.09.09上記1~4の作業を行う時に画面が作業中チラつくので、ちらつき防止処理をする【顧客番号確定】ボタンを押すと、ここまで記載した処理を順番に一瞬で行ってくれるのですが、その処理の度にカーソルが処理をしているセルの位置に実際は移動しています。”の前後に入れましょう。エクセルVBAでセルを選んでも画面は動かないようにする方法ユーザーフォームに配置したコマンドボタンを押すと様々な作業を自動で行ってくれるVBA。便利で仕事の作業効率化ができるのですが、押すたびに画面が切り替わっては、目障りですよね。1行加えるだけでちらつき防止できるのでぜひマネして取り入れてみてはいかがですか?tripbowl.com2019.09.02波乗りアヒルこれで【顧客番号確定】ボタンのVBAコードの設定が終わりました。MsgBox(“登録済番号です。Subスポンサーリンク【郵便番号検索】ボタンにURLを登録しよう先ほどお話しした通り、郵便番号を入力すると住所が表示されるようにするにはとても大変なので、手っ取り早く郵便番号が分からない時の為に【郵便番号検索】ボタンを押すと郵便局のホームページを開いて郵便番号が検索できるようにしましょう。開くように登録しますコマンドボタンを押すとホームページやメールを開く方法オリジナルの入力フォームを作ることができるユーザーフォームにたった1行加えると配置したコマンドボタンを押すと、指定したホームページが開いたり、メールソフトが開くことが簡単にできます。Sub【新規登録】ボタンのVBAコード住所録リストに新規登録をするのにはリストの最終行を探してから追加で入力作業を行いますので、マウスで画面スクロールさせて探す手間を省くには必須で、オリジナル入力フォームでこのような無駄な作業時間をカットできますので、ぜひ作りましょう。登録数を自動連番する最初に作ったエクセル住所録では”2番”の重複番号チェックが無かった為に、同じ番号が2つリストにできてしまって、データの修正する時に検索エラーになったことがあったので、この重複チェックは必須だと思い知らされました。”5番の”登録数の自動連番は今のところ(2年ぐらい経ちますが)使うことが無いので無くてもいいと最近は思っています。ボタンの時と同じで、入力するテキストボックス(TextBox1)に顧客番号を入力して【顧客番号確定】ボタン(CommandButton1)を押すのが当たり前なのですが、うっかりテキストボックスに何も入力しないで押してしまった時に、メッセージを表示してあげると親切ですし、誤った操作という事をお知らせできるので設定することをおススメします。(“会員番号が入力されていません”)複数のテキストボックスが空欄の時にメッセージを出す方法ユーザーフォームって自分で使うとエラーにならないのに、他人が使うとエラーが出る。先ほどもお話しした追加で作った項目で、うっかり同じ番号を追加で登録してしまうのを防ぐために【新規登録】ボタンを押したら、重複番号でないかをチェックして重複していたらエラーメッセージを表示させます。ワークシート上に上の図のように重複チェック項目を作りエクセル数式を入れておいて、重複しなければ「0」を重複したら「1」を表示させます。重複チェック用のエクセル数式=IF(IFERROR(VLOOKUP(P11,AB2:AB999,1,0),0)=0,0,1)次に【新規登録】ボタンに重複時のエラーメッセージのVBAコードを書き込みます。名前など住所録に必要な項目を各テキストボックスに入力して【新規登録】ボタンを押して所定のセルへ転記させますが、住所録リスト最初の1人目の時と2人目以降では処理が異なりますので、ここでは最初の1人目について書きます。1人目は一般的に2行目になります。(1行目は項目名が記載されています)左画像は最初に見てもらった画像と同じです住所録リストで会員番号の列を基準にすると、セルAB1が基準という事になります。最初の1人目なので入力すべきセルはセルAB2になりますが、言い方を変えると、基準のセルAB1の1行下と言い換えることができますよね。氏名はセルAC2に入らなくてはいけないのですが、これも言い方を変えると、基準セルAB1の1列右・1行下と言い換えられますね。この言い換えを繰り返して、生年月日はセルAI2なのでセルAB1の7列右・1行下ですね。いう事で、このようなVBAコードで1人目を表します。If入力済みのセルの最終行の下に新しく入力する方法エクセルでデータ入力する時にいちいちスクロールして未入力の行を探すのって手間がかかり面倒なので、自動的に入力済み最終行にデータ入力で作業効率大幅アップしましょうtripbowl.com2019.09.024、1行目にデータが入力済みの場合は入力最終行を探してデータ登録これも簡単に言うと2人目以降の事で、先ほどは基準セルに対して何列目・1行下としましたが、2日目以降は何列目かは分かりません。そこで入力済みの最終行の1行下に追加登録をするようにVBAコードもおかないと、いつまでたっても上書きされてリストは増えていきません。If入力済みのセルの最終行の下に新しく入力する方法エクセルでデータ入力する時にいちいちスクロールして未入力の行を探すのって手間がかかり面倒なので、自動的に入力済み最終行にデータ入力で作業効率大幅アップしましょうtripbowl.com2019.09.025、登録数を自動連番する実用性があるかどうかわかりませんが、使ってはいますがあまり使っていない気がするのでいらないかもしれませんね。自動連番は、会員番号が登録されると自動的に登録数をカウントアップして記載するコードです。1LoopエクセルVBAで自動的に連番をつける方法VBA自動連番付与をデータ入力をする時に欲しくなりませんか?伝票番号や顧客番号などは自動的に通し番号「連番」が付与されると仕事効率が良くなります。tripbowl.com2019.09.07波乗りアヒル次は【登録修正】ボタンですが、長くなったのでページを替えます!!続きは下記よりエクセルVBAで住所録検索してリストを修正するユーザーフォームを作る住所録リストを作成した後に、特定の人を探したり修正するのって意外と面倒ですよね。表の中で数式の入っていないセルだけ空白にする方法エクセルでデータなどを入力した表の入力データをクリアしたい事ありませんか?でも数式や関数が入っているセルまで消されては困りますよね。数式の入っていないセルだけ空白にする技が使えると、仕事の作業効率が上がるのでぜひマネして取り入れてみてはいかがですか?2018.12.03ユーザーフォームユーザーフォームでオリジナル入力画面を作るオリジナルの入力フォームを作ることができるユーザーフォームの作り方をご紹介します。エクセルの学校に行っていないおじさんでもできたので、仕事の作業効率化の為にぜひマネして取り入れてみてはいかがですか?2019.09.09データの操作VLOOKUP関数の代わりにエクセルVBAでやる方法エクセル関数のVLOOKUP関数。データ検索するのに便利なのですが、数式だと困るのでVBAを使ってVLOOKUP関数と同じことができると便利で、仕事の作業効率化になるのでぜひマネして取り入れてみてはいかがですか?2019.09.09シートの操作エクセルのシートを隠したり表示するボタンを作るVBAを使ったExcelファイルのシートはみんなで共有して使っても、触れられたくないし、商品管理の仕入れ原価は見られたくないと思いませんか?でも自分だけは操作可能にしたい時は、シート表示・非表示の切り替えボタンを作れば解決しますよ。誤ってセルの計算式が消されないようにシートの保護を掛けたら、VBAエラー表示が出た経験ありませんか?VBAコード2行追加で解決します。パスワード付きの保護・解除も可能です。2019.09.11シートの操作エクセルVBAで表以外の場所へ移動させない方法ここ以外触らせたくない!見られたくない!ってありませんんか?表・もしくは任意の指定範囲以外はセルをクリックもできないし、画面をスクロールさせることもできなくすることが簡単にできますよ!2019.09.11このサイトに掲載のVBAコードの動作確認済み環境は下記の通りです。・Excel2010パーソナルにExcel2016プロフェッショナルをインストール・Excel2007プロフェッショナルにExcel2016プロフェッショナルをインストール上記環境以外での動作確認は行っておりません。サンプルのVBAコードは自由にコピーしてご自身のエクセルに使っていただいて構いません。波乗りアヒルエクセルの勉強お疲れ様です。旅行先での移動に鉄道やバスを使う時にフリーパスや往復乗車券付きクーポンを使ってお得に楽しみたい時、意外と調べるのが面倒なんですよね。通常1人分の料金表示なのにJRのフルムーン夫婦グリーンパスは2人分の料金表示だったり、電話で聞いて分かる事も。tripbowl.net誰でもリニア試乗ができるJR東海専用応募サイト2019年第2回リニア試乗募集発表!2027年品川~名古屋間で開通予定の中央リニア新幹線。Reserved.HOMEお問い合わせプライバシーポリシーサイトマップタイトルとURLをコピーしました。