ユーザーフォームの入力をシートに反映

質問

かなりの初心者(始めて3日)ですが宜しくお願いします。
しかも2点あります。。。

(1)ユーザーフォームにある大量のテキストboxないし、optionboxをエクセルのsheet2に反映させたいです。
で、地道に
With Sheets(2)
.Range("A22").Value = OptionButton184.Value
.Range("A23").Value = OptionButton185.Value
.Range("A24").Value = OptionButton186.Value
.Range("A25").Value = OptionButton187.Value
とAの行を手で直していたのですが、途中で間違えて、手直しは断念しました。そこで、思いついたのが、「ひとつ下のセルに記述する」ということでした。
で、本を見ながら考えたのが、
With Sheets(2)
Range("A1").Select
ActiveCell.Offset(1, 0).Value = TextBox1.Text
ActiveCell.Offset(1, 0).Value = TextBox2.Text
ActiveCell.Offset(1, 0).Value = TextBox3.Text
まずA1を指定させて、下にずらしていけるかと思ったのですが、全然上手くいきません。しかもsheet1のA1を選択してるようです。どうしてでしょうか？？？

(2)フォームOKボタンを押して、上記の処理をしても、フォームに記入したものが次にフォームを開いた時に消えていないように、
Me.Hide
End Sub
で終わらせたのですが、次に開いても残っていません。

無知でお恥ずかしいですが、宜しく御願い致します。

回答No.3

nishi6 · ベストアンサー

＞まずA1を指定させて、下にずらしていけるかと思ったのですが、全然上手くいきません。しかもsheet1のA1を選択してるようです。どうしてでしょうか？？？

　　With Sheets(2)
　　　　Range("A1").Select
　　　　ActiveCell.Offset(1, 0).Value = TextBox1.Text

多分、Sheet1からこのユーザーフォームが開かれているのでは？
『Range("A1").Select』があり『ActiveCell』を基準にしているので、『With Sheets(2)』が意味をなしていません。
書くなら、
　　Sheets(2).Activate
　　With Sheets(2).Range("A1")
　　　　.Offset(1, 0).Value = TextBox1.Text
これでSheet2のA2から書き出すはずです。（未確認です）

＞で終わらせたのですが、次に開いても残っていません。
『残っていない』のではなく、最初にデザインした状態が再度表示されているわけです。

残すためには、プロパティ『ControlSource』にセルを指定する必要があります。
または、下の例示のように『Initializeイベント』で値を取得する必要があるでしょう。

質問では、オプションボタンやテキストボックスが沢山あるみたいですが、一括で処理する例です。

オプションボタン1～80の値をA1～A80に、
テキストボックス1～80の値をB1～B80に　書き込んでいます。

説明を簡略化するために、オプションボタン等の番号と書き出すセルの行番号を一致させています。

終了後、最後フォームを開いた時に書き込んだ値を取得するために、『Unload』で終わり、
『UserForm_Initialize』で取り込んでいます。コード表現は書き込みと逆ですね。

'Sheet2へ書き込む
Private Sub CommandButton1_Click()
　　Dim i As Integer

　　With Worksheets(2)
　　　　'オプションボタンの値を書き出し
　　　　For i = 1 To 80
　　　　　　.Range("A" & i) = UserForm1.Controls("OptionButton" & i).Value
　　　　Next
　　　　'テキストボックスの値を書き出し
　　　　For i = 1 To 80
　　　　　　.Range("B" & i) = UserForm1.Controls("TextBox" & i).Text
　　　　Next
　　End With
End Sub

'Formを呼び出したとき、Sheet2から読み込む
Private Sub UserForm_Initialize()
　　Dim i As Integer

　　With Worksheets(2)
　　　　'オプションボタンの値を読み込み
　　　　For i = 1 To 80
　　　　　　UserForm1.Controls("OptionButton" & i).Value = .Range("A" & i)
　　　　Next
　　　　'テキストボックスの値を読み込み
　　　　For i = 1 To 80
　　　　　　UserForm1.Controls("TextBox" & i).Text = .Range("B" & i)
　　　　Next
　　End With
End Sub

'終了ボタン
Private Sub CommandButton2_Click()
　　Unload Me
End Sub

お礼

ばっちり出来ました！
ありがとうございます！！
本当に本当に助かりました！

補足

長い回答ありがとうございます。
ご指摘の通り、sheet1にフォームを開くボタンがあって、そのフォームに記入した内容をsheet2に記述させたいのが全体の流れです。

つまり、長々と500行近くコードにA1、A2...と記述するのではなく、変数を使って変化させると言う認識で良いのでしょうか？
と言うことは、全部Aの列に入れたい私はtextboxとoptionbuttonも数字をならびにしないといけないと言うことですか？(今はぐちゃぐちゃ状態です。1~766まで。。。)

でも、記述がその分、短くて良いと言うことですよね。今の番号ぐちゃぐちゃ状態でやるには、Do...Loopとかいうのを使って可能ですか？(本を見ました。)

回答No.2

imogasi

ＶＢＡプログラムを組みたいのかもしれませんが、
この件では、エクセルＶＢＡならプログラムにしないで処理できます。
テキストボックス、オプションボタンともデザインモード
でマウス右クリックすると「プロパティ」が出てきて、
その中の項目に「ＬｉｎｋｅｄＣｅｌｌ」があります。
これにＡ２２やＡ２３を入れていけば、連動します。
この辺を操作ですませ、プログラムにしないこと（コントロールも手で貼りつけたでしょう）こそＶＢなどの目指しているところだと思います。
ＶＢならコントロール配列がつかえること、コントロールを配列にすることを思い出してください。
また私はＯＦＦＳＥＴはCells(i+1,j+1)とかすれば良いので、使いません。

補足

お返事有り難うございます。
それも考えたのですが、社内のエクセル苦手な人々に記入してもらう為、いじられないように＆全角とかで記入されないようにユーザーフォームにしてみたのですが・・・逆に大変な事始めちゃったかしら。。。

回答No.1

postpapa

初めて3日ですでここまで考えられたら大したものです。
さて
>With Sheets(2)
>Range("A1").Select
>ActiveCell.Offset(1, 0).Value = TextBox1.Text
>ActiveCell.Offset(1, 0).Value = TextBox2.Text
>ActiveCell.Offset(1, 0).Value = TextBox3.Text
With Sheets(2)
Range("A1").Select
Selection.offset(1).Select
Selection.Cells.Value = TextBox1.Value
Selection.Offset(1).Select
Selection.Cells.Value = TextBox2.Value
.
.
.
Ene With

offsetは行方向、列方向に移動出来ます。
offset(1).Selectは、行方向に一つ移動します。
offset(，-1).Selectは、列方向に一つ移動します。

今アクティブなセルを基準に考えれば
上に一つ移動は　offset(－1).Select
下に一つ移動は　offset(1).Select
右に一つ移動は　offset(，1).Select
左に一つ移動は　offset(，－1).Select

上に移動し右に移動する場合はoffset(－1,1).Selectとなります。
あと個人的には、TextBoxの値を取る場合、Valueで収得した方が良いですよ。特にセルの値を取る場合は必ずValueで設定して下さい。

ユーザーフォームを非表示は Unload命令で、オブジェクトをメモリーから削除します。
Hideメソッドはメモリー上から削除されませんから、マクロで操作可能ですので少し工夫して下さい。
プログラミングは、100人いれば100通りのやり方がありますのでこれが正解だとは言い難いです。
工夫次第では素人のアルゴリズムの方が優れている場合もありますよ。
どんどん自分で工夫して頑張って下さい。

補足

お返事有り難うございます。
activecellじゃなくて、selectionなのですね。
勉強になります。