ユーザーフォームからワークシートへの転記について

質問

●質問の主旨

エクセルVBAの初心者です。

添付画像のユーザーフォームからデータベースへの転記の
コードを記述しましたが、実行しようとすると15行目の「CelData」のところが
選択され、コンパイルエラー（変数の定義がされていません）
が返されます。

１．「CelData」のコンパイルエラーはどのように修正すれば
　　よいでしょうか？

２．またこのコンパイルエラーを修正すれば、ユーザーフォームから
　　ワークシートへの転記ができるでしょうか？

●質問の補足

１．ユーザーフォームにある「入力No.」と
「入力日」（赤色で囲った部分）をそれぞれ
A2セル、B2セル（青色で囲った部分）に転記しようと
考えています。

２．添付画像のワークシートには手入力で「柴田8月分」
というシート名が予めつけられています。

３．ユーザーフォームやワークシートには
他に入力、転記する個所がありますが質問を
簡単にするため省略しています。

４．この質問に関するオブジェクト名は以下の
通りです。

・「入力No.」＝TextBox3
・「入力日」＝txtdate
・「データベース入力」＝CommandButton3

エクセルVBAにお詳しい方ご教示願います。
使用機種はWindows Vista Excel2007です。
なお、コードは以下の通りです。

1 Option Explicit

2 'ユーザーフォームの初期化
3 Private Sub UserForm_Initialize()
4 TextBox3.Value = 1
5 txtdate = Date

6 End Sub

7 'ユーザーフォームからデータベースへの転記
8 Private Sub データベース入力_Click()

9 Dim RowPos As Integer
10 Dim ColPos As Integer

11 RowPos = 2
12 ColPos = 1

13 Do

14 RowPos = RowPos + 1
15 CelData = Worksheets("柴田8月分").Cells(RowPos, _
16 ColPos)
17 Loop While CelData <> ""

18 With Worksheets("柴田8月分")
19 .Cells(RowPos, ColPos) = TextBox3.Value
20 .Cells(RowPos, ColPos + 1) = txtdate.Value

21 End With

22 End Sub

回答No.2

imogasi · ベストアンサー

#1です。
ーー
私が言っているのわかってないようだ。
例えば
ユーザーフォームに
テキストボックス１つ
コマンドボタン１つ（クリックイベントでも良いがこちらにする）
（これら部品２つはコントロールといわれる。）
を設ける。
ーー
Private Sub CommandButton1_Click()
rowpos = rowpos + 1
MsgBox rowpos
Worksheets("Sheet1").Cells(rowpos, "A") = TextBox1.Value
End Sub
をつくり実行すると
テキストボックスにaと入れてボタンクリックーー＞Ａ１がaになる。
テキストボックスにbと入れてボタンクリックーー＞Ａ１がbになる。
こン名ことの繰り返しで良いのか・
ーー
私が
rowpos = Worksheets("Ｓｈｅｅｔ１").Range("Ａ10000").End(xlUp).Row
と入れた意味を無視している。
rowpos という変数の有効性の場面を考える必要がある。

お礼

たびたびのご回答ありがとうございます。

imogasiがANo.1のコードの通り、改めてユーザーフォーム及び
コードを作成するとデータベース形式で入力ができ、
常に最下行にテキストが入力できるようになりました。

rowpos = Worksheets("Ｓｈｅｅｔ１").Range("Ａ10000").End(xlUp).Row
というコードの意味が良く分かりました。

回答No.1

imogasi

質問文は長いが
（１）「フォームのコントロールの構成　　が不明
ｔｘｔｄａｔe はテキストボックスなのか、外のものか。
（２）フォームにはデータ入力後表示するのか
＞ユーザーフォームからデータベースへの転記の・・
とあるが、フォーム入力ーー＞シート転記なら、
クリックごとにシートに書き出す行が１行下をさす（か最終行の次の行）を指さないとならないが
それらしいコードが無い。
根本的に欠陥は無いか？クリック１回するごとにＲｏｗＰｏｓなどは前回の値とは無関係になるが。
この間の変数地の受け渡しは、初心者には難しい。
本件ＶＢＡの学習として、背伸びしすぎだと思う。
（３）フォームにデータを入力後、シートにそれらデータを転記するのか
根本的なことが文章で説明されて無い。
そうでないなら毎回繰返して、CelDataという１つの変数に何回も値を代入して、セルにセットはしないのか。
フォームに表示だけか？
ーー
一例
フォーム
テキストボックス1　　番号入力用
テキストボックス2　　日付用
テキストボックス3　　その他データ用
入力用のボタンを１つ設けボタンのクリックにしたほうが良いのでは？
ーー
Private Sub UserForm_Click()
'Private Sub データベース入力_Click()
rowpos = Worksheets("Sheet1").Range("a10000").End(xlUp).Row 'データ最終行の取得
ColPos = 1 'A,B,C・・で指定したほうがわかりやすいのでは
rowpos = rowpos + 1
With Worksheets("Sheet1")
.Cells(rowpos, ColPos) = TextBox1.Value 'A列
.Cells(rowpos, ColPos + 1) = TextBox2.Value 'Ｂ列
.Cells(rowpos, ColPos + 2) = TextBox3.Value 'Ｃ列
End With
End Sub

お礼

ご回答ありがとうございます。

補足質問についてもご確認いただければ幸いです。
よろしくお願い申し上げます。

補足

いつもご指摘ありがとうございます。

また拙い説明ながらお付き合いいただきありがとうございます。
imogasi様のご確認について以下の通りお答え申し上げます。

>（１）「フォームのコントロールの構成　　が不明
>txtdateはテキストボックスなのか、外のものか。
txtdateはユーザーフォームの一部を構成する
テキストボックスで、オブジェクト名をtxtdateにしています。

>（２）フォームにはデータ入力後表示するのか
原則は開発タブ→マクロ→マクロ実行後に
ユーザーフォームを表示させリストボックス、テキストボックス等に
入力してていきます。
ただし「入力No.」と「入力日」は初期値を
表示させておいて、他の項目の入力が完了したときに同時に転記します

>（３）フォームにデータを入力後、シートにそれらデータを転記するのか
ご推察の通り、フォームにデータを入力後、シートにそれらデータを転記します。
転記の完了後は新しい番号で、「入力No.」と「入力日」以外は空白のユーザーフォームを
表示させて、再度、入力→転記の作業の繰り返しを行うつもりです。

初心者には難しいことなのは、重々承知ですが、
ひとつひとつ問題をクリアしていこうと思っています。

それで今回はフォームからワークシートへの転記作業の
コードを記述しようと思い、質問しました。

imogasi様から頂いた例に基づきコードを
以下の通り、書き換えをしてみました。
エラーやデバックはなかったものの、
「入力No.」と「入力日」のワークシートへの
転記ができませんでした。

先のご回答でヒントが含まれているのかも知れませんが、
何分初心者でimogasi様の意図が読み切れていないと
思います。たびたびの質問で恐縮ですが、
再度ご教示いただければ幸いです。

以上よろしくお願い申し上げます。

Private Sub UserForm_Initialize()

TextBox3.Value = 1
txtdate = Date

End Sub

Private Sub データベース入力_Click()

rowpos = Worksheets("柴田8月分").Range("a10000").End(xlUp).Row
ColPos = 1

rowpos = rowpos + 1

With Worksheets("柴田8月分")

.Cells(rowpos, ColPos) = TextBox3.Value
.Cells(rowpos, ColPos + 1) = txtdate.Value

End With

End Sub