ユーザーフォームがエラーになってしまって困っています。

質問

VBA初心者なので、テキスト本を参考にユーザーフォームを作ったのですが、エラーになってしまって困っています。
フォーム上のコンボボックス、テキストボックスに入力した値をワークシート上に転記したいのですがエラーが出てしまって先に進めずに困っています。
どなたかお力をお貸し頂けませんでしょうか？

Option Explicit
Dim TBL(1 To 8) As Control
Dim データ範囲 As Range
----------------------------
Private Sub UserForm_Initialize()
With Combo会社名
.MatchEntry = fmMatchEntryFirstLetter
.ColumnCount = 2
.TextColumn = 1
End With

With Combo住所1
.AddItem "東京都"
.AddItem "埼玉県"
.AddItem "神奈川県"
End With

Set TBL(1) = Textコード番号
Set TBL(2) = Text登録年月日
Set TBL(3) = Combo住所1
Set TBL(4) = Combo会社名
Set TBL(5) = Text郵便番号
Set TBL(6) =Text住所2
Set TBL(7) = Text 住所3
Set TBL(8) = Text電話番号

Set データ範囲 = Range("A1").CurrentRegion
Spin移動.Max = レコード数取得 + 1
If データ範囲.Rows.Count = 1 Then
Else
データ表示 2
End If
End Sub
----------------------------------------
Public Function レコード数取得() As Integer

レコード数取得 = Range("A1").CurrentRegion.Rows.Count - 1

End Function
----------------------------------------
Public Sub データ表示(行数 As Integer)
Dim Cnt As Integer
For Cnt = 1 To 19

TBL(Cnt).Value = データ範囲.Cells(行数, Cnt).Value　　---この部分でエラー
Next
Textレコード.Value = Spin移動.Value - 1 & "/" & レコード数取得

End Sub
-------------------------------------
Private Sub Spin移動_Change()

If データ範囲.Rows.Count <> 1 Then
データ表示 (Spin移動.Value)
End If

End Sub
------------------------------------
Private Sub Button追加_Click()

Dim AddRow As Integer

AddRow = データ範囲.Rows.Count + 1
データ書き込み (AddRow)

Textレコード.Text = Spin移動.Value - 1 & "/" & レコード数取得
Set データ範囲 = Range("A1").CurrentRegion
Spin移動.Max = データ範囲.Rows.Count
Spin移動.Value = データ範囲.Rows.Count

データ表示 (AddRow)

End Sub
-------------------------------------
Private Sub Button更新_Click()

データ書き込み (Spin移動.Value)

End Sub
-------------------------------------
Public Sub データ書き込み(行数 As Integer)
Dim Cnt As Integer
For Cnt = 1 To 19

データ範囲.celles(行数, Cnt).Value = TBL(Cnt).Value
Next
End Sub
--------------------------------------
Private Sub Button終了_Click()

ActiveWorkbook.Save
Application.DisplayAlerts = False
Unload Me
ActiveWorkbook.Close

Application.Quit
End Sub

回答No.3

nag0720 · ベストアンサー

行数に問題がないとしたら、
TBL(Cnt).Value = データ範囲.Cells(行数, Cnt).Value
を次のようにして

Dim S
For Cnt = 1 to 19
S = データ範囲.Cells(行数, Cnt).Value
TBL(Cnt).Value = S
Next

どこでエラーになるか、またSの型と値はどうなのか、TBL(Cnt)の型は本当にComboBoxなのかを調べてみてください。

変数の型はTypeName関数で調べられます。

お礼

ありがとうございました。

TypeName関数で変数の型を調べている時に
「どこでエラーになるか、またSの型と値はどうなのか、TBL(Cnt)の型は本当にComboBoxなのかを調べてみてください。」
をもう一度考え直したところ、
TBL(Cnt)の型は本当にComboBoxなのですが、
Combo会社名のリストはCombo会社名のプロパティのROWSourceで範囲を指定していたのですが、
Combo会社名のリストに無い値がセルに入力されたままになっており
それがエラーの原因と判明しました。

色々とアドバイスをして頂き、本当にありがとうございました。
おかげさまで解決致しました。

回答No.4

imogasi

初心者と言う割には、難しく複雑なコードを作成している。
何か解説書からの引き写しか？
VBでなく、エクセルVBAなどなら
ユーザーフォームの作成
コンボボックスをユーザーフォーム上に作成
は操作で行う、が標準（コードでも出来るがむつかしいだけ）。
そしてコンボのアイテムの設定は、前もってセル範囲例えばF1:F19などのセルに入力する。
そしてROWSourceにF1:F19と（文字列で）入力する。
ユーザーフォームを表示するとアイテムにF１：F19の項目がアイテムとして表示される。
ーー
この設定はコードからでも出来る。
Private Sub UserForm_Initialize()
UserForm1.ComboBox1.RowSource = "G1:G3"
End Sub
ーー
セルと選択したアイテムとの連動は
Private Sub ComboBox1_Change()
Cells(3, "B") = UserForm1.ComboBox1.Value
End Sub
＝＝＝
質問の仕方について、良く考えること。
どこでどういうエラーがどこで起こったかも書いてない。
コードだけ書いて、何をしたいか（特にコードの後半）を文章で説明するもんだ。
初心者の書いたコードなど、独特のやり方だったり、まわりくど勝ったりで、主旨（やりたいこと）がわかりにくい。

お礼

アドバイスありがとうございました。

参考書を見ながらエクセルでユーザーフォームを作成しコンボやテキストに入れた値をシート上のセルに配列どうりに転記していくようにしたのですが・・・
コンボのアイテムの設定はComboプロパティのROWSourceで範囲を指定していました。

色々と勉強不足かつ質問の仕方の不備などありましてすみませんでした。

もっと勉強したいと思います。
アドバイスありがとうございました。

回答No.2

nag0720

TBL(Cnt).Value = データ範囲.Cells(行数, Cnt).Value - --この部分でエラー

エラーになったときの「行数」の値を調べてみてはどうですか。

補足

ありがとうございます。

エラーになったときの「行数」の値は４でした。
その時のCntの値も４でした。
それが意味することと対処の仕方がわからなくて・・・

回答No.1

nag0720

For Cnt = 1 To 19
TBL(Cnt).Value = データ範囲.Cells(行数, Cnt).Value - --この部分でエラー
Next

TBL配列を８個しか用意してないのになんで
For Cnt = 1 To 19
なの？

補足

すみません！
本当はTBL配列は１９個あるのですが、質問用に８個にしてしまったのに、
For Cnt = 1 To 19をFor Cnt = 1 To 8と直すのを忘れていました。
質問も初心者ですみません。

それと、OSはWindows XP でExcel 2003です。