Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
複数の登録期間の入力チェックについて
投稿日時: 18/06/29 09:15:09
投稿者: 河童TKO

いつも大変お世話になっております。
 
複数の登録期間の入力チェックについて
教えてください。
 
ユーザーフォームに
年月を表示するためのラベルと
開始日と終了日を選択するコンボボックスを
1セットとして15セット設置しています。
コンボボックスのソースは1から31までセットしています。
 
ユーザーフォームを開いたときに
ラベルに年月を表示させています。
 
やりたいことは、
開始日と終了日の期間のチェックです。
各コンボボックスで重複がないこと
期間に誤り(開始日>終了日)がないことです。
 
開始日と終了日が同日になることはありません。
 
現状は、
セットごとの開始日と終了日のチェックはできていると思います。
・開始日が入力されているか
・終了日が入力されているか
・開始日と終了日の比較
 
しかし、セット同士のチェックができていません。
分からないことは、
例えば
開始日1:5
終了日1:8
開始日2:2
終了日2:10
 
上記の場合、期間が重複しているのでチェックをかけたいです。
 
すべてのコンボボックスで重複が出ないように
チェックをかけるにはどのようにすればよいですか?
 
 
Private Sub cmdTouroku_Click()
              
Dim i As Integer
Dim j As Integer
 
'
With Frm_Touroku.Controls
 
    '期間入力チェック
    For j = 1 To 15
         
        '終了日の有無
        If .Item("cboStrDay" & j) <> "" Then
            If .Item("cboEndDay" & j) = "" Then
                .Item("cboEndDay" & j).SetFocus
                MsgBox ("終了日を入力してください")
                Exit Sub
            End If
        End If
        '開始日の有無
        If .Item("cboEndDay" & j) <> "" Then
            If .Item("cboStrDay" & j) = "" Then
                .Item("cboStrDay" & j).SetFocus
                MsgBox ("開始日を入力してください")
                Exit Sub
            End If
        End If
         
        '開始日>終了日
        If .Item("cboStrDay" & j) <> "" Then
             
            If Format(lbl年月 & .Item("cboStrDay" & j) & "日", "yyyy/mm/dd") > _
               Format(lbl年月 & .Item("cboEndDay" & j) & "日", "yyyy/mm/dd") Then
                .Item("cboStrDay" & j).SetFocus
                MsgBox ("登録期間に誤りがあります")
                Exit Sub
            End If
                             
        End If
         
    Next
 
End With

回答
投稿日時: 18/06/29 11:21:53
投稿者: mattuwan44

シート上に表作ったら一目瞭然だと思うのですが、
わざわざユーザーフォーム上にテキストだけで表示するのですか?
 
あと、コンボボックスで1〜31までを選択させられるのも、
個人的には結構苦痛なのですがどんなもんなんですかねー
 
で、期間がかぶってるかどうかなら、
 
2つ目開始日が一つ目の開始日以上で、終了日以下
か、
2つ目の終了日が、一つ目の開始日以上で、終了日以下
 
のどちらかが満たされたら重複と判定できますよね?
 
後は各コントロールの名前を工夫して、
どれの期間というのを上手く指定しやすいようにしてやればいいかと思います。
 
参考URL>>
https://plaza.rakuten.co.jp/puppypc/diary/200905210000/

回答
投稿日時: 18/07/01 10:41:27
投稿者: WinArrow
投稿者のウェブサイトに移動

余計な心配かもしれませんが
 
>Format(lbl年月 & .Item("cboStrDay" & j) & "日", "yyyy/mm/dd")
lbl年月:2018年6月
cboStrDay1:31
 
とした場合
正しい日付になりますか?
 

投稿日時: 18/07/01 10:55:42
投稿者: 河童TKO

おはようございます。
mattuwan44様、お返事ありがとうございます。
 
ユーザーフォームには
開始日と終了日を入力のコンボボックスのほかに
何種類かのテキストボックスとチェックボックスもあります。
 
90個程のコントロールを配置したユーザーフォームを
表示させるだけなのに、表示の処理速度が遅いです。
 
シード上でデータを入力するにしても
上記のコントロール分のセルにデータを入力することになります。
 
日付の入力は
コンボボックスにしているのですが、
選択ぜずにそのまま数字を入力できるからいいかなと思っています。
 
利用者の予定表を作成するときに
利用期間の登録で1か月に複数回あるとか
月をまたぐときはどのようにするかなど
悩んでいます。

回答
投稿日時: 18/07/01 12:29:47
投稿者: WinArrow
投稿者のウェブサイトに移動

>月をまたぐときはどのようにするかなど
>悩んでいます。
 
年月(ラベル)が1つしかないのですから、
無理ですね・・・・
 
どのように困っているかわかりませんが、
最初から設計しなおしでしょうね・・・
 

回答
投稿日時: 18/07/01 12:32:49
投稿者: simple

こんにちは。
 
その予定表というのは、どのくらいの利用者がいるのでしょうか。
一人だけが利用するのか、4,5人程度が使うのか、
それによっても仕様が変わってきますよね。
 
また、なんらかのものが既にあって、改良を加えようとしているのでしたら、
現行のものの説明をされた方がよいと思います。
これから新たに作成するというのであれば、
関係者(利用者)に内容を説明して感触を聞かれたほうがよさそうです。
 
さらにいえば、ネット上を調べると色々な形式の予定表があると思いますが、
そうしたもののうちで使えそうなものはないのですか?

回答
投稿日時: 18/07/01 12:51:15
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

すべてのコンボボックスで重複が出ないように
チェックをかけるにはどのようにすればよいですか?

 
1つのアイデア
 
あらかじめ、設定する範囲はかのうならば、
 
その範囲の日付を配列に格納し、
1組目の選択が終了したら、配列に使用済フラグを立てます。
2組目の選択が終了したとき、配列の使用済フラグをチェックする
と、いうような方法はいかがでしょうか?
 
修正時は、最初からやり直す。
 
 

回答
投稿日時: 18/07/01 16:13:24
投稿者: mattuwan44

 >表示させるだけなのに、表示の処理速度が遅いです。
僕の感覚だと「90個も!」なんですけど、どうなんでしょう。
重くて使い物にならないなら、僕ならさっさと見切り付けますが。。。
結構古いパソコンなんですかね?
 
そもそも90個ものコントロール毎に機能や制御するコードを書くだけでもぞっとします。
ExcelVBAを使うならシート上のセルがすでに高機能ですし、
それをオブジェクトとして定義することなく使えますので、
僕は、シート上にユーザーフォーム的なものを作っちゃいますね。
もちろんそういうユーザーフォームを使ってアプリを作っている人の方が大半かも知れませんが、
よくわからないものに、立ち向かうよりは、多少なりとも手動での操作がイメージできる、
シート上の方が開発が楽ではないかなと思います。
 

>利用者の予定表を作成するときに
>利用期間の登録で1か月に複数回あるとか
>月をまたぐときはどのようにするかなど
>悩んでいます。

シート上で数式で数えたら数えられませんか?
 
というか、
どんなアプリを作ろうとしているのですか?
そして、その中で、どんな入力をする場面の話しなのでしょうか?
そういうことを説明されると、も少し具体的なアドバイスが貰えると思います。
コードもそうですが、今一度、画面やアプリ全体の設計を見つめ直した方がいいと思います。

回答
投稿日時: 18/07/01 21:29:46
投稿者: simple

続きを書きます。
 
私もワークシートを中心にすべきだと思います。
予定表の内容をすべて、ユーザーフォームに表示するなどは非現実的でしょう。
ユーザーフォームのまま保存できるわけではないですし、
基本はワークシートでしょうから、ワークシートを機能充実させたほうが得策です。
 
(1)
ある程度の数の予定件数がありそうですから、
閲覧機能としてユーザーフォームを利用するのは適当でないと思います。
ワークシートでは簡単にできる色や図形を利用も、
ユーザーフォームでは簡単にできません。
また言語仕様としてもVBAのユーザーフォームは、
コーディングに負荷が掛かりすぎると思われます。(VBなどに比して)
 
仮に、見やすいユーザーフォームを作成するとしても、
相当の熟練が必要でしょう。
 
(2)
ユーザーフォームをデータの変更に使用することはありえます。
変更には@追加、A修正、B削除がありますが、
@追加に使うとしても、1つのデータを対象にして使う程度にすべきです。
 
こうした予定表などというものは全体を一覧しながら追加していくことが多いので、
シートを表示しつつ、ユーザーフォームを使って一件だけ追加する、
というのはあるかもしれません。
 
論点は、入力負荷を軽減するためのヘルプ機能をどれだけ必要としているか、
充実させうるか、という点でしょう。
今考えていることを提示してもらえば、皆さんから意見が出されるでしょう。

回答
投稿日時: 18/07/01 22:37:33
投稿者: WinArrow
投稿者のウェブサイトに移動

根本的なことを質問します。
 
15セットの開始〜終了の組合せがある
その各々は重複しないこと
がシステム仕様ですよね?
 
実際に隙間がない状態で登録されていたとして、
変更はどのようにするのでしょうか?
 
1組目の終了日を1〜2日後にずらしたい
といったとき、2組目の開始日に重なってしまいますよね?
 
最後の組から変更しなくてはいけなくなりませんか?
 
 

回答
投稿日時: 18/07/02 08:29:02
投稿者: WinArrow
投稿者のウェブサイトに移動

最初から考え直すならば、「ジョブのネットワーク」という考え方を提案します。
 
今回の「開始」と「終了」の単位が仕事なのかわかりませんが、
「ジョブ」と呼びます。
一般的には、「ジョブ」は、「直列」の場合と「並列」の場合がある。
「ジョブのネットワーク」は、どちらにも対応可能です。
今回の場合は、説明からすると「直列」と推測します。
例えば
「A」→「B」→「C」・・・という具合
しかし、「A」と「B」の期間が重複していると、「直列」にはなりません。
そこで、考え方としては、
「B」は、「A」の終了を以て開始とするが
「A」の終了と「B」の開始に「空き」がある場合は、「B]の開始を採用する
つまり、「前のジョブの終了」と「自ジョブの開始」の大きい方を「自ジョブ」の開始とする
という考え方です。
 
この概念を採用するには、
@「ジョブ」単位に「開始」と「長さ」と「前ジョブ」を登録する
 「前ジョブ」が存在しなければ、そのジョブがネットワークの先頭ジョブになります。
A「ジョブ」をスケジューリングする
と2段階になります。
@はユーザーフォームで入力し
Aはガントチャート方式で表示
 
この方式は、私が、昔、大型コンピュータのバッチ処理のオペレーションを自動化した時に
考えだした手法です。
 
この考え方は、@とAを独立させることができ、メンテナンスが楽になることもちろんのこと
プログラム開発もず〜と楽になります。
 
 
 
 
 
 
 
 

投稿日時: 18/07/03 06:04:07
投稿者: 河童TKO

おはようございます。皆様お返事ありがとうございます。
 
この計画表でもう少し具体的に説明させていただきます。
 
介護施設で利用表を作成しています。
今はエクセルでフォーマットを印刷して
そこに手書きで横線を引いて計画表を作っています。
一日の定員は13名ですが、利用者は30名ぐらいいます。
 
利用者によって一カ月で12回くらい入退を繰り返す方もいれば、
1、2回の方もいます。
 
横線を手書きするのをやめたいと思っています。
 
一カ月の予定表を作成するうえで、
各利用者の予定を入力します。
今は紙ベースの予定を確認しながら、
表に手書きで線を引くところを、利用者ごとで
ユーザーフォームに期間を入力しようと思っています。
入力後、実行ボタンを押せば自動的に指定された期間の
セルの色が塗られるように考えています。
期間の追加、編集、削除などの処理は
 
利用者ごと利用期間ごとに1回1回登録しようと思ったのですが、
利用期間が何回もあるとき大変だったので、
決まっている期間を1度に登録できるようにしたいです。
編集、削除する場合も考慮したい。
編集する場合もあります。
1日から10日の予定から
1日から12日までとか、すべてキャンセルとか。
編集後に重複があればチェックをかけたいです。
12日が次の入所の予定だった場合など。
 
シート上で作成する場合、
データベースのテーブルみたいに作成するのかな。
利用者名、年月、期間1、期間2・・・・

回答
投稿日時: 18/07/03 06:39:16
投稿者: WinArrow
投稿者のウェブサイトに移動

なんとなくですが、
手書レベルの予定表をユーザーフォームで登録
というのは、無理があるように思います。
 
目視でセルに色を設定する方法で入/退を区別できるようしたらと考えます。
入所している間を「赤」にする。退所したら、色を消す。
所謂ガントチャートです。
日付を横に展開し、列幅を小さくすれば、1ヶ月位をA4横で表現可能です。
 
まずは、手作業をそのままExcelシートに置き換えたらいかがでしょう?
 

投稿日時: 18/07/03 15:51:51
投稿者: 河童TKO

こんにちは。
WinArrow様、お返事ありがとうございます。
 
今修正中のフォーマットはA3用紙横で1ヶ月単位で出力できます。
 
手作業(出力した用紙に手書きで横線を引く)のかわり
エクセルシート上でセルの色を塗る補助ツールを作成していましたが、
使い心地や評判が悪くて他の方法を考えている最中です。
 
色塗り補助ツール(マクロで登録)
・入所から退所までの期間は「赤」
・キャンセルは「青」
・削除は「色なし」
・入所と退所の区分の表示は「入」「退」

回答
投稿日時: 18/07/03 16:17:42
投稿者: Suzu

ガンチャート をユーザーフォームは かなり厄介だと思います。
 
きちんと作り込んだサブルーチンが沢山必要でしょう。。
クラス化した方が楽かな。。。
 
でも、私にはその技量がありません。
自前でなら、まだワークシートの方がコード作成は楽ちんと思います。。
 
ただ。。各タスクの拘束条件(このタスクとこのタスクは連続とかの条件)があり、
編集を考えると一気に難易度はあがりますよね。。
 
 
そのあたりのガンチャートの拘束条件を含め、設定できるソフト
 
Microsoft Project というソフトがあります。
https://www.microsoft.com/ja-jp/project/
 
無料トライアルもあります。

回答
投稿日時: 18/07/03 16:22:49
投稿者: WinArrow
投稿者のウェブサイトに移動

>利用者によって一カ月で12回くらい入退を繰り返す方もいれば
 
これっておかしくない?
 
1ヶ月,MAX31日として
入/退が同じ日はあり得ないとしたら、
※今までの説明の中に「開始日」=「終了日」はない・・と書いてあったような気がします。
入所中期間が、最低2日となります。
間1日おいて入所するというパターンだとすると
1サイクルが3日ですから、10サイクルとなるから、12回にはならないでしょう?
 
>利用者によって一カ月で12回くらい入退を繰り返す方もいれば
は計画段階でもあるのかしら?・・・つまり、実績=結果として、こうなったのではなkのかな?
 
まあ、それはよいとして
 
手書イメージのシートデザインを提案します。
 
A列に氏名
仮に、B列より右に日付をレイアウトします。
「値」を日付、「表示形式」を"dd"として2桁表示する
これをA4横に印刷すると仮定すると、1ヶ月+αが一目で目視可能になります。
 
もう一つの設定
B2〜右に「条件付き書式」を設定しておきます。
条件は、セルの値が「1」と等しい
書式:塗りつぶしを「赤」(任意)とします。
 
入所中のセルに「1」をいれていくと「赤い」バー状態が表示されます。
退所した時はセルを[Del]すれば色は消えます
 
更に、30名ほど合計行に「=SUM」関数を入れておけば、日別の入所者数が自動的に把握できます。
私見としてはが、合計行は、下よりも上の方がよいと思います。
最下行に合計があると、追加の利用者/削除の利用者で行が増えたり、減ったりするので関数の引数を変更することになる可能性があるかも?
 
>1日の定員13名
は制約なのかわかりませんが、日別合計の「条件付き書式」を設定しておけば、定員超過も容易に把握できます。
 
過去となって、不要部分を列単位で削除すれば、簡単にシフトもできます。
 
ユーザーフォーム(=VBA)でこれだけの機能を自作するとなると、
しかも、気が遠くなるような、15組の総当たりチェックを考えているより
手っ取り早く、実現しそうな気がします。
 
 
 
 

投稿日時: 18/07/12 11:39:32
投稿者: 河童TKO

こんにちは。
いつも大変お世話になっております。
 
皆様、お返事ありがとうございました。
 
ユーザーフォームを使用して計画表を作成したかったのですが、
なかなか難しくて、とりあえず保留することにしました。
 
とりあえず、シート上にガンチャートの表を作成することにしました。
 
プログラミングは素人ですが、
何かを作り上げていくことは面白いです。
 
いろいろなアドバイス、ありがとうございました。