EXCELのデータベース

質問

EXCEL2000を使用しています。
VBAを使ってデータベースを作ろうとしているのですが、ちょっとわからないのでどなたか教えてもらえませんか。
データベースへの入力はUSERFORMを使います。

USERFORMの内容は、
コンボボックス、テキストボックス1,2,3、コマンドボタン”登録”、”終了”です。

まず大項目は"SHEET DB"のA1:A120に入力されており、”発注者”、”請負者”、”病院”・・・・というのがあります。
この項目はUSERFORMのコンボボックスにセットされます。

次にテキストボックスの入力項目が３個あり、
TEXTBOX1　・・・名前
TEXTBOX2　・・・電話番号
TEXTBOX1　・・・FAX
を入力します。

発注者のデータベース入力範囲はA2:C100で、次の大項目、請負者のデータベース入力範囲は、D2:F100と言う風に延々と続きます。

USERFORMのコンボボックスで大項目を選択し、テキストボックス1,2,3の入力を入力し、コマンドボタン"登録"を押すと、大項目のデータベース範囲にテキストボックスのデータの空白行にデータが登録されます。
大項目が1個しかない場合は出来るのですが、大項目が複数になった場合の条件の設定がよくわかりません。
大項目の下図は数十個あり、１個１こセルに入力するのは大変で苦労しています。
VBAのプロの方なら初歩的すぎる事かもしれませんが、ビギナーな私にわかりやすくどうかご教示願います。

回答No.2

ngsvx · ベストアンサー

要件は理解しました。

ただ、このような保存形式には疑問があります。
前提条件など、詳細がわからないので一概にはいえませんが、
普通はこのようにはせず、「発注者」などもレコードの１項目として
扱います。

つまり、レコードの項目は、
・相手区分
・名前
・電話番号
・ＦＡＸ
という４つになります。

そういないと、相手（コンボボックスに表示する項目）が増える度に
ＶＢＡのコードを修正する必要がありますし、いろいろ面倒です。

まあ、事情がわからないので、とりあえず今の状態でアドバイスをします。

私がやっても、同じようなコードになりますよ。
あえていうなら、

If ComboBox1.ListIndex = 0 Then
CL = 1
ElseIf ComboBox1.ListIndex = 1 Then
CL = 4
ElseIf ComboBox1.ListIndex = 2 Then
CL = 7

の部分ですね。

直す余地があるとすれば、

１．ComboBox1.ListIndexを使っているため、コンボボックスで表示する順番を
　　変えると、コードの修正が発生する。

２．保存先の最左のカラムを指定しているため、保存項目が増えた場合
　　この部分のコードを全て修正する必要がある。（バグの元）

３．コードを読みやすくするため、コントロールには適切な名前を付ける。

ことだと思います。

解決策として、

１．保存先の位置と、表示の順番は切り離す。

　・コンボボックスのプロパティ
　　・ColumnCountを２に設定
　　・RowSourceには２列のデータをセット
　　　（Sheet1!A1.B10という具合。セルの範囲に名前を付けておき、
　　　　その名前を設定する方がもっと管理しやすい）
　　・ColumnWidthsは１列目が表示されないように調整
　　　（0pt;30ptのようにする）

　・RowSourceで指定した場所の、１列目にはシートへ保存するときの位置。
　　セルの列番号ではなく、何番目に保存するものかを設定。
　　一番左は１、次は２・・・。

２．ソースコードの先頭でレコードの項目数をコンスタント値を宣言。

　　Option Explicit
　　const RECORD_ITEM_COUNT As Integer = 3

＜訂正後コード例＞

'ＣＢで選択されているインデックス値
Dim cbIndex As Integer

'シートに保存する項目位置（１から始まる）
Dim slotNo As Integer

'保存先の列位置（０から始まる）
Dim CL as Integer

cbIndex = ComboBox1.ListIndex
slotNo = ComboBox1.List(cbIndex, 0)
CL = (slotNo - 1) * RECORD_ITEM_COUNT

こんなかんじです。

わからない箇所は質問して結構です。

頑張ってください。

お礼

ご多忙な中懇切丁寧に対応して頂きありがとうございました。初歩的な質問で躊躇したのですが、親切な対応でほっとしました。
内容の方は理解できました、早速組み替えて見ましたがうまくいきました。
また何かあったらご教示願います。

回答No.1

ngsvx

何回も読んで、やっと意味がわかった気がします。

１．コンボボックスは、、”発注者”、”請負者”、”病院”などを選択できる。
２．登録ボタンが押されたときに、コンボボックスの内容によって、書き込み先の列を変える。

ということでしょうか？

補足

僕の説明下手ですいません。
ngsvxさんの仰るとおりです。
少しくどくなりますが、以下に説明します。

まず大項目”発注者”の書き込み先は
A3:C30であり、
A3・・・名前　　(TEXTBOX1の内容)
B3・・・電話番号(TEXTBOX2の内容)
C3・・・FAX (TEXTBOX3の内容)
となります。
つづいて大項目”請負者”の書き込み先は
D3:F30であり、A3,B3,C3がD3,E3,F3に変わります。
このように大項目毎に延々とつづいて行きます。
基本的には登録先の最下端にデータを追加していきます。

現在はこんな感じでコードを作っています。

'登録ボタンのコード
Private Sub Commandbutton1_click
Dim CL As Long 'コンボ大項目登録先の列番号
Dim RW As Long '最下端の行番号
If ComboBox1.ListIndex = 0 Then
CL = 1
ElseIf ComboBox1.ListIndex = 1 Then
CL = 4
ElseIf ComboBox1.ListIndex = 2 Then
CL = 7

-途中省略-
End if

RW = Worksheets("DB").Cells(1, CL).End(xlDown).Row
'大項目登録先最下端の検出

With Worksheets("DB")
.Cells(RW + 1, CL).Value = TextBox1.Value
.Cells(RW + 1, CL + 1).Value = textbox2.value
.Cells(RW + 1, CL + 2).Value = TextBox3.Value
End With
End Sub

'コンボボックスの値セット
Private Sub UserForm_Initialize()
ComboBox1.RowSource = "DB!AW1:AW16"
ComboBox1.ListIndex = -1
End Sub

申し訳ないのですが、最初の質問の時とは若干登録先の配置が換わっています。
表記の"CL"は大項目登録先の列番号です。
"RW"は大項目登録先の最下端の行番号です。

ここでよくわからないのが、コンボの値によって変化する入力先をどう与えるかです。
考えている内にこんがらがって、表記の様な不細工なものになりました。一応問題なく動作しましたが、めちゃくちゃめんどくさいです。今後の為にご教示願います。