EXCEL VBAユーザフォームでTextとComboBoxの値をOKボタンで該当セルに書込みたい

連日のエクセルについての質問ですいません。

TextBox3つとComboBox３つとOKボタン１つのEXCEL VBAユーザフォームを設置しました。

ComboBox1はシート名を選択するためにシート名を読みに行きます。
ComboBox2は業者名（特定の１縦列（実際にはC3:C740））を選択するためにシートへ読みに行きます。
ComboBox3は物件名（特定の１横行（実際にはE2:ML2））を選択するためにシートを読みに行きます。
TextBox1は金額を入力できます。
TextBox2はComboBox1を選択と同時にComboBox1の値を元に請求書番号の前側が自動的に確定します。（たとえばComboBox1で「2015-04」のシートを選ぶとTextBox2にそのまま自動入力されます。
TextBox3は請求書番号の末尾数字（４桁まで）です。
ユーザーフォーム右下にOKボタンを設置しました。

以上までのことについては自分でなんとか作成できました。

さて、ここからです。※わかりやすく句読点を多く書きます。ご了承下さい。

OKボタンを押します。
そうするとComboBox1で選んだシートの、ComboBox2で選んだ業者名と、ComboBox3で選んだ物件名の交差するセルの位置に、TextBox1の値を入力したいのです。またTextBox2とTextBox3を半角ハイフンで繋いだ値を「請求書参照番号（実際にはB3:B740）」に同時に入力。そして最後に「最終入力日時（BN列（実際にはのBN3:BN740））」の該当箇所にそのOKを押した年月日時分秒をYYYY/MM/DD/HH:mm:ssのフォーマットで自動入力したいのです。
そして最後にユーザーフォーム内のComboBox1の値は保持し、それ以外はクリアし、その次の手入力へとつなげたい。

このような一連のVBAを構築しようと考えておりますが、頭が着いてこず、諸先輩方のお力を何卒お借りしたいと考えております。
何卒ご教示の程宜しくお願いいたします。

質問者からの補足コメント

• 2015/03/26 15時書き込み
  
  tom04さん補足させて下さい。
  Set myArea1 = wS.Range("C3:C740")
  Set myArea2 = wS.Range("E2:ML2")
  のレンジの値（C3:C740とE2:ML2）を入れ替えると書き込みができました。
  そう思ったのは、myArea1の参照が業者列であるのに対し、ComboBox2は物件名であったためです。
  同様にmyArea2の参照は物件名行であるのに対し、ComboBox3は業者名であったためです。
  
  ただし、変更し実行してみると、とんでもない所に値が書き込まれる…。
  
  物件名ではF2の物件を選び、業者名ではC8の業者を選んだところ
  E2に請求書参照番号が書き込まれ、C2の業者名ラベル自体に金額が。
  BN2の最終更新日時ラベル自体に日時が書き込まれます。
  うーん。どこかもしかして噛み合ってないのかも？

    補足日時：2015/03/26 15:51

• ２０１５０３２６　16時書き込み。
  
  解消です＼(^O^)／
  
  .Cells(c.Row, r.Column)を.Cells(r.Row, c.Column)に。
  c.Offsetをr.Offsetに。
  With .Cells(c.Row, "BN")をWith .Cells(r.Row, "BN")に。
  
  ただし…。
  金額がなぜか1000000と入力しても該当箇所は1に20000000と入力すると20に…。
  
  Private Sub TextBox1_Change()
  TextBox1 = Format(TextBox1, "#,###")
  If Len(TextBox1.Text) > 10 Then
  TextBox1.Text = Left(TextBox1.Text, 10)
  End If
  End Sub
  これが邪魔なのでしょうか？

    補足日時：2015/03/26 16:16

• tom04さん度々の度々の補足となります。
  
  TextBox1 = Format(TextBox1, "#,###")
  が原因でした。これを削除したところうまく行きました。
  ただし、これを消すとTextBox1内の入力に桁数カンマが表示されず困ってしまいます。
  なんとかなりませんでしょうか？
  最終質問となりそうです。
  何卒お力添えを宜しくお願いいたします。川田

    補足日時：2015/03/26 16:28

No.5 ベストアンサー 回答者： tom04 回答日時： 2015/03/26 20:46

何度もごめんなさい。

投稿後気づきました。

コード内のコメント
＞'//▼　各Sheetの表示形式を設定しておけばこの次の1行は不要 //
は下の行ではなく、コメントの１行上でした。
入力時、１行間違っていました。

どうも失礼しました。m(_ _)m

この回答へのお礼

お礼が遅くなり大変申し訳ありませんでした。
おかげで快適に運用できるようになり、そして私自身大変勉強になりました。すべてtom04さんのおかげでございます。
ありがとうございました。

お礼日時：2015/04/06 18:49

No.4 回答者： tom04 回答日時： 2015/03/26 20:32

続けてお邪魔します。

悪戦苦闘されていらっしゃるようですね。

最初の質問文を読むと「業者名」「物件名」の範囲が全く逆だったのですね？
それでは当然とんでもない表示になってしまいます。

次にTextBoxの数値表示に関して
どうしても3桁区切りにしたいのですね？
ここで気を付けなければならない点として「テキストボックス」は読んで字のごとく
「文字列」のデータです。それを数値化しなければならないので
＞Val（TextBox1）
のようにしていました。
今回は問題ないのですが、日付などをTextBoxで扱う場合は要注意です。

さて、さらなるご希望としてテキストボックスは3桁で区切り、セルへの表示形式も3桁で区切りたい！
というコトなので、
もう一度コードに手を加えてみました。

↓のコードにしてみてください。

Private Sub CommandButton1_Click()
Dim c As Range, r As Range, str As String, wS As Worksheet
Dim myArea1 As Range, myArea2 As Range
If ComboBox1 = "" Then
MsgBox "Sheetが選択されていません" & vbCrLf & "Sheetを選択してください"
ComboBox1.SetFocus
Exit Sub
End If
If ComboBox2 = "" Then
MsgBox "物件名が選択されていません" & vbCrLf & "物件名を選択してください"
ComboBox2.SetFocus
Exit Sub
End If
If ComboBox3 = "" Then
MsgBox "業者名が選択されていません" & vbCrLf & "業者名を選択してください"
ComboBox3.SetFocus
Exit Sub
End If
If TextBox1 = "" Then
MsgBox "金額が未入力です" & vbCrLf & "金額を入力してください"
TextBox1.SetFocus
Exit Sub
End If
If TextBox3 = "" Then
MsgBox "末尾番号が未入力です" & vbCrLf & "末尾番号を入力してください"
TextBox3.SetFocus
Exit Sub
End If
str = ComboBox1
Set wS = Worksheets(str)
Set myArea1 = wS.Range("C3:C740")
Set myArea2 = wS.Range("E2:ML2")
Set c = myArea1.Find(what:=ComboBox2, LookIn:=xlValues, lookat:=xlWhole) '//物件名の列を取得
Set r = myArea2.Find(what:=ComboBox3, LookIn:=xlValues, lookat:=xlWhole) '//業者名の行を取得
With wS
.Cells(c.Row, r.Column) = Val(Replace(TextBox1.Text, ",", "")) '//←文字列を数値に！
.Cells(c.Row, r.Column).NumberFormatLocal = "#,##0"
'//▼　各Sheetの表示形式を設定しておけばこの次の1行は不要 //
c.Offset(, -1) = TextBox2 & "-" & Format(TextBox3, "0000") '//☆
With .Cells(c.Row, "BN")
.Value = Now()
.NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
End With
End With
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
ComboBox2 = ""
ComboBox3 = ""
TextBox1.SetFocus
End Sub
'//　▼以下3行がTextBoxの表示処理
Private Sub TextBox1_Change()
TextBox1 = Format(TextBox1, "#,##0")
End Sub

※　TextBox1の表示形式のコードも入っています。

※　一番簡単なのはコードで表示形式に手を加えるのではなく、
各Sheetとも同じ配置のようですので、すべてのSheetを作業グループ化し、
最初からセルの表示形式を設定しておく方法だと思います。
（そうすればコード内にもコメントを入れていますが、あの1行は不要です）

※　細かいエラーを考えればTextBox1・TextBox3　に数値以外のデータが入力された場合も必要かと・・・

もっと欲を言えば手入力するTextBox1　とTextBox3　に関しては
プロパティのIMEモードをOFF（２）にしておけば、言うコトなし！
かもしれませんね。m(_ _)m

No.3 回答者： tom04 回答日時： 2015/03/25 18:57

No.2です。

＞.Cells(c.Row, r.Column) = Val(TextBox1)
で「実行時エラー91 オブジェクト変数またはwithブロック変数が設定されていません」と出てしまいます

コンボボックスで選択しているはずですので、可能性とは低いと思いますが、
「業者名」もしくは「物件名」が選択したSheetにない可能性があります。
（「コンボボックス1」で選択したSheet名は必ず存在するとします）

確認の意味でコードに手を加えてみました。
↓のコードに変更してコマンドボタンをクリックしてみてください。

Private Sub CommandButton1_Click()
Dim c As Range, r As Range, str As String, wS As Worksheet
Dim myArea1 As Range, myArea2 As Range
str = ComboBox1
Set wS = Worksheets(str)
Set myArea1 = wS.Range("C3:C740")
Set myArea2 = wS.Range("E2:ML2")
Set c = myArea1.Find(what:=ComboBox2, LookIn:=xlValues, lookat:=xlWhole)
Set r = myArea2.Find(what:=ComboBox3, LookIn:=xlValues, lookat:=xlWhole)
'//▼ココから追加
If c Is Nothing Then
MsgBox "業者名が存在しません" & vbCrLf & "再確認してください"
ComboBox2.SetFocus
Exit Sub
End If
If r Is Nothing Then
MsgBox "物件名が存在しません" & vbCrLf & "再確認してください"
ComboBox3.SetFocus
Exit Sub
End If
If TextBox1 = "" Then
MsgBox "金額が未入力です" & vbCrLf & "再入力してください"
TextBox1.SetFocus
Exit Sub
End If
If TextBox3 = "" Then
MsgBox "末尾番号が未入力です" & vbCrLf & "再入力してください"
TextBox3.SetFocus
Exit Sub
End If
'//▲ココまで追加
With wS
.Cells(c.Row, r.Column) = Val(TextBox1)
c.Offset(, -1) = TextBox2 & "-" & Format(TextBox3, "0000")
With .Cells(c.Row, "BN")
.Value = Now()
.NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
End With
End With
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
ComboBox2 = ""
ComboBox3 = ""
TextBox1.SetFocus
End Sub

※　万一「コンボボックス2」または「コンボボックス3」で選択したデータがない場合は
メッセージボックスが表示されます。

※　ついでに　TextBox1・TextBox3 が未入力の場合も再入力するメッセージが表示されるようにしてみました。

欲を言えばもう一つコマンドボタン（閉じるためのボタン）を配置し、
編集途中の場合のメッセージなどを表示させる方が良いかもしれませんね。

これでもダメならごめんなさいね。m(_ _)m

この回答へのお礼

度々ありがとうございます。
なるほど。とても勉強になります。読み解くっていうのは非常に難しい反面ためになってありがたい限りです。

早速コピーさせていただきました。
「物件名が存在しません」というメッセージが出てしまいます。
業者名のIfを物件名の先に持ってくると、「業者名が存在しません」とでます。
認識できてないんでしょうか？

ちなみに金額や末尾番号の項を前に持ってきても、そのメッセージは出てこないので数字またテキストボックスの内容は認識しているようです。

また
If wS Is Nothing Then
MsgBox "該当期が存在しません" & vbCrLf & "再確認してください"
ComboBox1.SetFocus
Exit Sub
End If
というのを入れてみたのですが、該当期は認識できているものと思われます。

2バイト文字のせいということもありうるのでしょうか？

お礼日時：2015/03/26 14:09

No.2 回答者： tom04 回答日時： 2015/03/24 22:02

こんばんは！

ちょっとやってみました。

ユーザーフォームのコマンドボタンのコードを↓のようにしてみてください。

Private Sub CommandButton1_Click()
Dim c As Range, r As Range, str As String, wS As Worksheet
Dim myArea1 As Range, myArea2 As Range
str = ComboBox1
Set wS = Worksheets(str)
Set myArea1 = wS.Range("C3:C740")
Set myArea2 = wS.Range("E2:ML2")
Set c = myArea1.Find(what:=ComboBox2, LookIn:=xlValues, lookat:=xlWhole)
Set r = myArea2.Find(what:=ComboBox3, LookIn:=xlValues, lookat:=xlWhole)
With wS
.Cells(c.Row, r.Column) = Val(TextBox1)
c.Offset(, -1) = TextBox2 & "-" & Format(TextBox3, "0000")
With .Cells(c.Row, "BN")
.Value = Now()
.NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"
End With
End With
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
ComboBox2 = ""
ComboBox3 = ""
TextBox1.SetFocus
End Sub

※　詳しい検証はしていませんので
ご希望通りにならなかったらごめんなさいね。m(_ _)m

この回答へのお礼

こちらもご協力有り難うございます。
ちなみに
.Cells(c.Row, r.Column) = Val(TextBox1)
で「実行時エラー91 オブジェクト変数またはwithブロック変数が設定されていません」と出てしまいます。
いくら試行錯誤しても、…。うーん。何がダメなのか…。

お礼日時：2015/03/25 15:50

No.1 回答者： hallo-2007 回答日時： 2015/03/21 14:35

＞ComboBox1で選んだシートの、ComboBox2で選んだ業者名と、ComboBox3で選んだ物件名の交差するセルの位置に、TextBox1の値を入力したいのです

一例ですが
Sheets(ComboBox1.Value).cells(ComboBox２.ListIndex + ?,ComboBox３.ListIndex + ?).Value=Textbox1.Value
といった具合で、ListIndexを使って、行と列をしてできないでしょうか。
＞またTextBox2とTextBox3を半角ハイフンで繋いだ値を「請求書参照番号（実際にはB3:B740）」に同時に入力
Sheets(？).Range("?").Value=TextBox2.Value & "-" & Textbox3.Value
>にそのOKを押した年月日時分秒をYYYY/MM/DD/HH:mm:ssのフォーマットで自動入力したい
Sheets(？).Range("?").Value=Now
TextBox1.Value=""
TextBox2.Value=""
・・・・

この回答へのお礼

回答有り難うございます。
できればもう少し優しく教えて頂ければ幸いです。

お礼日時：2015/03/23 19:07