エクセルのVBAについて教えてください。

質問

以前に下記のような内容のプログラムを作成したいと投稿致しましたら

dim row as Integer
for row = 1 to 65535
if (selectSheet.Cells(1, row) = "") then
selectSheet.Cells(1, row) = "#####"
EndIf

をアレンジしたいのです。
上記のプログラムが構築されているコマンドボタンと同じuserformにオプションボタンを５個、コンボボックスを一つ作りました。
オプションボタン１を選択するとコンボボックスにはあ行が。
オプションボタン２を選択するとコンボボックスにはか行が。
オプションボタン３を選択するとコンボボックスにはさ行が。
オプションボタン４を選択するとコンボボックスにはた行が。
オプションボタン５を選択するとコンボボックスにはな行が。
選択できるようにしたいのです。
次に選んだオプションボタンと同名前のシートに上記の#####が入力されるようにしたいのですが、どのようにすればいいのですか？

このように教えて頂きました。

Public myop As Integer 'オプション選択保持用

Private Sub CommandButton1_Click()
Dim row As Integer
Dim mykey As String '比較キー
'選択したオプションボタンにより
'比較キーと選択保持用変数に各値を代入
Select Case True
Case OptionButton1: mykey = "[あ-お]*": myop = 1
Case OptionButton2: mykey = "[か-こ]*": myop = 2
Case OptionButton3: mykey = "[さ-そ]*": myop = 3
Case OptionButton4: mykey = "[た-と]*": myop = 4
Case OptionButton5: mykey = "[な-ほ]*": myop = 5
Case Else: myop = 0
End Select
If myop = 0 Then Exit Sub
For row = 1 To 65535
If ActiveSheet.Cells(1, row).Value Like mykey Then
ActiveSheet.Cells(1, row) = "#####"
End If
Next row
End Sub

これを流用して自分でいじりたいのですが、私が未熟ですので、コードの意味、役割がさっぱりわかりません。
わがままな質問ではございますが、どなたか上記のコードの意味を教えて頂けませんか？
よろしくお願い致します。

回答No.12

myRange · ベストアンサー

これが２回目分です。

■次に、UserForm２～５それぞれに次のコードをコピペする

'----●UserForm２ ----------
Private Sub CommandButton1_Click()
　　On Error Resume Next
　　Unload UserForm2
　　Unload UserForm3
　　Unload UserForm4
　　Unload UserForm5
End Sub

Private Sub CommandButton2_Click()
　　UserForm2.Hide
　　UserForm3.Show
End Sub

'----●UserForm３ --------
Private Sub CommandButton1_Click()
　　UserForm3.Hide
　　UserForm2.Show
End Sub

Private Sub CommandButton2_Click()
　　UserForm3.Hide
　　UserForm4.Show
End Sub

'----●UserForm４ --------
Private Sub CommandButton1_Click()
　　UserForm4.Hide
　　UserForm3.Show
End Sub

Private Sub CommandButton2_Click()
　　UserForm4.Hide
　　UserForm5.Show
End Sub

'----●UserForm５----------
Private Sub CommandButton1_Click()
　　UserForm5.Hide
　　UserForm4.Show
End Sub

Private Sub CommandButton2_Click()
　　Dim Lastrow As Long

　　With Sheets(KirokuSheetName)
　　　　Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　.Cells(Lastrow + 1, "A").Value = GetCaption(2)
　　　　.Cells(Lastrow + 2, "A").Value = GetCaption(3)
　　　　.Cells(Lastrow + 3, "A").Value = GetCaption(4)
　　　　.Cells(Lastrow + 4, "A").Value = GetCaption(5)
　　End With

　　Unload UserForm2
　　Unload UserForm3
　　Unload UserForm4
　　Unload UserForm5
End Sub

Private Function GetCaption(N) As String
　　Dim i As Integer
　　GetCaption = ""
　　For i = 1 To 4
　　　　If UserForms(N - 2).Controls("OptionButton" & i).Value Then
　　　　　　GetCaption = UserForms(N - 2).Controls("OptionButton" & i).Caption
　　　　　　Exit For
　　　　End If
　　Next i
End Function
'------------------------------

質問者が先にテストしたブックで動作を確認すること！
以上です。

回答No.13

myRange

おや、まあ、２回に分けたら投稿できた。
原因不明ですが、投稿できたので、よしとしませう。

これが最後の回答になるように、祈って。。。
以上です。

補足

今帰宅しました。
昨晩はそのような障害があったんですか・・・。
何度も投稿を試みて頂いてありがとうございました。
頂いたサンプルコードですが、後日確認させて頂きます。
ありがとうございました。
また３回目の投稿にコメントさせて頂きます。

回答No.11

myRange

myRangeです。
昨夜から何回も投稿を試みるが、アクセス集中、で拒否され、今朝も同様。
恐らく、OKWAVEのシステムがバグっているのだろうと推測。
OKWAVE,しっかりせんかぃっ！　と叱咤激励したい気分。

と、ま、それは置いといて、今回の投稿もちょと長いので２回に分けてみる。

これは、１回目。

第一段階、上手くいって何よりです。
第二段階ですが、これは、Userform２～５のOptionのCaptionを
【第一】段階のUserForm1で選択したシートのＡ列に転記、ですね？

'-----------------------------------------
■先ずUserForm１の宣言セクションの変数KirokuSheetNameを削除して
標準モジュールの最初（宣言セクション）に

　　Public KirokuSheetName As String

とグローバル変数として宣言する
'-----------------------------------------

■印刷シートへ次のコードを追加

Private Sub CommandButton2_Click()
　　If KirokuSheetName = "" Then
　　　　MsgBox "シート名が選択されてない"
　　Else
　　　　UserForm2.Show
　　End If
End Sub
'-------------------------------------------

ここまでが１回目送信分。

補足

コード試しました。
コマンドボタン２をクリックすると、シート名が洗濯されてない。
とメッセージがでてきます。
ちゃんと最初にコマンドボタン１で転記先シートを決めた上でやりましたが、どうしてなのでしょうか？

回答No.10

myRange

暇なのでまたまた登場、myRangeです。

＞言われた通りにやっているのですが・・・。
>９．印刷シートのコードを開いて、教えていただいたサンプルコードをコピペする

これを見て、おい、おい、ということで、前回の回答を見たところ、
あろうことか当方が、コードを印刷シートのモジュールへと言ってますね。
それは間違いで、
回答のコードをコピペする場所は、印刷シートではなくて、●UserForm1●です。

印刷シートには下記のようにUserForm1を表示させるコードのみを書いておく。

Private Sub CommandButton1_Click()
　　UserForm1.Show
End Sub

これで上手くいくでしょう。
以上です。

お礼

私のフォームでもうまくいきました。

次なんですが、印刷シートにコマンドボタン２があります。
それには以前説明したようなオプションボタンで選択していって記録するマクロがあるのですが、コマンドボタン１で選択した転記先シートと同じところに転記するのはどのようにするのでしょうか？

補足

できました！！！

あせっていた気持ちがすごく楽になりました。
今から自分のに試します！
未熟な私に丁寧に付き合ってくれてありがとうございます＞＜。
自分ので試したらまた報告いたします！

回答No.9

myRange

＞If ComboBox1.ListIndex = -1 Then
＞で実行エラー４２４が発生します。

４２４の他に、エラーメッセージは表示されませんでしたか？
表示されてたらそれも提示しないと分かりません。

コントロールの種類、数、シート名など前回の回答の条件を満たしているんですよね。

ま、何れにしろ、アップしたコードは●テスト済み●なので
回答のとおりすればエラーは出ないはずです、というより、
エラーの出るような部分はありません。

で、試しに、新しいブックでシート名など回答の条件でアップしたコードを試してみてください。
UserForm１だけにすること。

その後、現在使用してエラーが出るというコードをアップしてください。
UserForm1のモジュールに書いてあるコードを全部アップすること。
文字制限（２０００文字）をオーバーするようだったら、２回に分けて投稿のこと。

以上です。

補足

言われた通りにやっているのですが・・・。

私がしていることを順を追っていいますね。
１．エクセル起動
２．シートを６個用意する
３．シート１を印刷　シート２をリスト　シート３～６を記録１～４に名前を書き換える
４．印刷シートにコマンボボタン１を作成
５．VBA起動
６．userform1を作成
７．userform1にオプションボタンを４個　コンボボックスを１個　テキストボックスを５個　コマンボボタンを１個作成
８．オプションボタン１のCaptionを記録１　オプションボタン２のCaptionに記録２とその後３，４を同様にする
９．印刷シートのコードを開いて、教えていただいたサンプルコードをコピペする
１０．リストのA2からA9まで適当な文字を入力　B列C列D列も同様に適当に打ち込む
１１．印刷シートにあるコマンドボタンをクリックし、マクロを起動させる
１２．すると実行時エラー４２４　オブジェクトが必要です　となりコードにいき、先ほどの場所が黄色で囲まれている

このようになってしまいます・・・。
試験の動作確認でこうなるので、自分のものではまだためしていません。

回答No.8

myRange

myRange、連続投稿。サンプルコードです。

以下のコードを　印刷シートモジュールにセットして実行する。
先に示したシート名などの条件は必ず遵守すること。

'-------------------------------------------------
　Dim ListAdrs
　Dim KirokuSheetName As String

Private Sub UserForm_Initialize()
　　OptionButton1.Caption = "記録１"
　　OptionButton2.Caption = "記録２"
　　OptionButton3.Caption = "記録３"
　　OptionButton4.Caption = "記録４"

　　ListAdrs = Split("A2:A9,B2:B9,C2:C9,D2:D9", ",")
End Sub
'---------------------------------------------

Private Sub ComboBox1_Enter()
　　Dim i As Integer

　　KirokuSheetName = ""

　　For i = 1 To 4
　　　　If Controls("OptionButton" & i).Value Then
　　　　　　ComboBox1.List = Sheets("リスト").Range(ListAdrs(i - 1)).Value
　　　　　　KirokuSheetName = Sheets(Controls("OptionButton" & i).Caption).Name
　　　　　　Exit For
　　　　End If
　　Next i

　　If KirokuSheetName = "" Then
　　　　MsgBox "OptionButtonにチェックが入ってない！"
　　End If
End Sub
'-----------------------------------------------

Private Sub CommandButton1_Click()
　　Dim i As Integer
　　Dim Lastrow As Long

　　If ComboBox1.ListIndex = -1 Then
　　　　MsgBox "ComboBoxが選択されてない"
　　　　Exit Sub
　　End If

　　With Sheets("印刷")
　　　　.Range("A1").Value = ComboBox1.Value
　　　　.Range("A4").Value = TextBox1.Value
　　　　.Range("A5").Value = TextBox2.Value
　　　　.Range("B3").Value = TextBox3.Value
　　　　.Range("D3").Value = TextBox4.Value
　　　　.Range("D4").Value = TextBox5.Value
　　End With

　　With Sheets(KirokuSheetName)
　　　　Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　.Cells(Lastrow + 1, "A").Value = ComboBox1.Value
　　　　.Cells(Lastrow + 1, "B").Value = TextBox1.Value
　　　　.Cells(Lastrow + 1, "C").Value = TextBox2.Value
　　　　.Cells(Lastrow + 1, "D").Value = TextBox3.Value
　　　　.Cells(Lastrow + 1, "E").Value = TextBox4.Value
　　　　.Cells(Lastrow + 1, "F").Value = TextBox5.Value
　　End With

　　Unload UserForm1
End Sub
'----------------------------------------------------

以上です。

補足

サンプルコードありがとうございました。
また、補足説明に不備があり、申し訳ありませんでした。
ご説明頂いたとおりしましたが、

Private Sub CommandButton1_Click()
　　Dim i As Integer
　　Dim Lastrow As Long

　　If ComboBox1.ListIndex = -1 Then
　　　　MsgBox "ComboBoxが選択されてない"
　　　　Exit Sub
　　End If

のIf ComboBox1.ListIndex = -1 Then
で実行エラー４２４が発生します。
どうすればいいのでしょうか？

回答No.7

myRange

補足要求の重要事項が補足されてません。
なぜ無視するでしょうか？
質問者のための補足要求ですよ！

なかなか意思の疎通が図れないようなので
手始めに、UserForm１の分だけのコードをアップしてみましょう。
よって、新しい補足は不要です。

●シートの名前は必ず次のようにすること
　　印刷用シート、、、印刷
　　リストシート、、、リスト
　　記録用シート、、、記録１～記録４（数字は全角）

●記録シート名の取得
　　UserFom1のOptionButtonのCaptionと同じとする
　　（Captionが、記録１とか記録４とかになってるということ）

●リストシートの一覧は次のようになってること
　　OptionButton１用：　Ａ２～Ａ９
　　OptionButton２用：　Ｂ２～Ｂ９
　　OptionButton３用：　Ｃ２～Ｃ９
　　OptionButton４用：　Ｄ２～Ｄ９

上記のような条件で、サンプルコードは続けて投稿します。

回答No.6

myRange

またまたまた登場、myRangeです。

＞＝＝＝＝＝
印刷シートに別のコマンドボタンをもうひとつ用意しています。
そのマクロとは、userformにオプションボタンを４個程度配置しています。
そのオプションボタンを選択形式でまた次の同じようなオプションボタンが４個あるようなuserformへと進んでいきます。
userformはhideで一旦隠して、最後のuserformで今まで隠していて、選択されているオプションボタンのcaptionを転記していくといった内容になっています。テキストボックスもいくつかありますが。。。
参考までに全部ではありませんがコードをのせておきます。

If UserForm1.OptionButton1 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton1.Caption
ElseIf UserForm1.OptionButton2 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton2.Caption
＜＝＝＝＝＝

●更に補足が必要です。
●簡単のために、UserFormが１～３の３つあり、
　UserForm１がメイン、２～３がサブと仮定して、、
(以下、UserFormは、Formと書く）
---------------------------------------------------
Form１のボタンでForm１の内容を転記したあと
このForm１は、Unloadするのか、Hideするのか？
--------------------------------------------------
印刷シートのボタン２で、Form２を表示するのは分かったが
そこから次のForm３を表示するタイミングが不明。
例えば、Form２にもボタンがありそれでForm３を表示するとか。。
----------------------------------------------------
提示のコードでは、Form１のOptionしか書いてないが
実際は、Form１～３全てのチェックされたOptionのCaptionを転記したいということか
また、提示のコードでは、記録シートの【Ａ列】転記するようになってるが
前回の補足には

○リストから選択したものが印刷用シートA列１行と記録用シートのA列の空白セルに転送。

このように、記録用シートのＡ列には、Form１のComboで選択した項目を転記とあるが、
そこら辺りをもっと詳しく補足のこと。
-----------------------------------------------------

■■■注意点■
するしないは別にして、実際の流れを実際に即して提示しないと
（要するに今までのように小出しにしないということ）
コードをアップする前に何回も遣り取りをしないといけなくなるので
そこら辺りを十分に考慮して質問補足すること

尚、コード自体は簡単なものですので（但し、慣れた人にとっては）、
あとは質問者がやりたいことをちゃんと説明できるかどうかです。
以上です。

補足

おはようございます。

私の説明不足で混乱させてしまっていますね・・・。
申し訳ありません。
印刷用シートの最初に補足しましたコマンドボタンを１とします。
今回のもうひとつのコマンドボタンを２とします。
コマンドボタン１と２の関連性は、コマンドボタン１で決定した「どのシートに転記するか」だけです。
前回の補足でコードを表示しましたが、userform1とか書いてあるからややこしかったのですね・・・。
コマンドボタン２のみの機能を説明致します。
コマンドボタン２はuserformが４個で成り立っております。
仮に各々のuseroformをA～Dとします。
formA～Dは同じような構成です。

オプションボタンG 　　　オプションボタンH
オプションボタンI 　　　 オプションボタンJ
コマンドボタンL　　　　 　　　コマンドボタンY
↑前のformに戻る　　　　　　　↑次のformへ進む機能を
機能を持たせている　　　　　　持たせている

このようになっております。
ただし、formAのコマンボドタンLはformAをunload。
formDのコマンドボタンYはformA～Dまでのオプションボタンの内容を転記するきっかけとします。
formAでオプションボタンンを選び、コマンドボタンLを選択して一旦formし、formBへ。formBでも同様にオプションボタンを選び、コマンドボタンでhideにしてformCへと続いていくようにしています。
で最後にformDで一斉に転記としています。

回答No.5

myRange

またまた登場、myRangeです。

初めからそのように書けば分かりやすかったですね。
それにしても最初とは随分と違う質問になりましたねぇ。。(^^;;;

ただ、も少し補足が必要です。

＞４個のオプションボタンがあり
＞オプションボタン毎にコンボボックスのリストが変化する。

コンボボックスにリストを設定するタイミングは？
オプションボタンをクリックした瞬間にセットする？

＞５個テキストボックスの内容を印刷用シートのあるセルと
＞記録用シートの、先ほどと同じ行に転送。

記録シートは転記するたびに行方向に増えていくが
印刷用シートは転記するたびに増えていかないで、
常に決まったセルに転記するのか？
また、印刷用シートの【ある】セル、とはどのセル？

それから、転記が終了した後の処理の流れはどうなっているか？
例えば、１件転記したあと、次はどんな処理をするのかということです。

上記が補足されたらサンプルコードをアップします。
以上です。

補足

返答ありがとうございます。
ネット上のマクロ講座で勉強はしたのですが、求めているものを簡潔に考えようとしても難しいです・・・。
未熟な私の手助けをしていただいて本当にありがとうございます。

コンボボックスにリストを設定するタイミングは？
オプションボタンをクリックした瞬間にセットする？

そうです。オプションボタン４個のすぐ↓にコンボボックスを設けているので、オプションボタンを選択後、コンボボックスでリスト内から選択といった形をとりたいです。
自分でも色々調べたのですが、オプションボタンを色々変更してクリックするとリストがおかしくなっちゃったりするのですよね・・・？

記録シートは転記するたびに行方向に増えていくが
印刷用シートは転記するたびに増えていかないで、
常に決まったセルに転記するのか？

おっしゃるとおりです。
印刷用はフォーマットを作っているので決まった箇所に転記されたいのです。
記録用は、今後データを行ごとに蓄積し、管理したいので、最初のコンボボックスの選択したリスト内容を転記した同じ行にしたいのです。ご理解はいただいていると思いますが、データは蓄積したいのでリスト転記は空白セルを検索し、転記させたいのです。

また、印刷用シートの【ある】セル、とはどのセル？

テキストボックス１はA4
テキストボックス２はA5
テキストボックス３はB3
テキストボックス４はD3
テキストボックス５はD4
です。

一件転記した後ですが、
印刷シートに別のコマンドボタンをもうひとつ用意しています。
そのマクロとは、userformにオプションボタンを４個程度配置しています。
そのオプションボタンを選択形式でまた次の同じようなオプションボタンが４個あるようなuserformへと進んでいきます。
userformはhideで一旦隠して、最後のuserformで今まで隠していて、選択されているオプションボタンのcaptionを転記していくといった内容になっています。テキストボックスもいくつかありますが。。。
参考までに全部ではありませんがコードをのせておきます。

If UserForm1.OptionButton1 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton1.Caption
ElseIf UserForm1.OptionButton2 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton2.Caption
ElseIf UserForm1.OptionButton3 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton3.Caption
ElseIf UserForm1.OptionButton4 = True Then
.Range("a" & m_row).Value = UserForm1.OptionButton4.Caption
End If

これらの作業が一通りとして、また同じ作業をすると違う行にデータが蓄積されていくようにしたいのです。

説明に不足な点だらけとは思いますがよろしくお願い致します。

回答No.4

phoenix343

#2です

| With Worksheets("sheet2")を
| Worksheets("OptionButton1.Caption")
| みたいになるわけですか？

""はいりません
""で囲むとただの文字列と扱われます
Worksheets(OptionButton1.Caption)

※変数と聞いてワカラナイのであれば、
　まずはVBAでの基本を抑えないと、
　この先もっとワカラナイと思いますよ　笑

回答No.3

myRange

期限が迫っていて焦ってらしいのは分かりますが、
最初の質問と、回答１の補足はほとんど別物になってますし
何をどうしたいのかが明確には読み取れません。
回答１の補足の質問者のたコードも説明とは全然異なっていますし。

特に、以下は意味不明です。

＞また、userform1で決めた、記録先シート（オプションボタン１～５のいずれを選択したか）に他のuserfromでの内容を記録していきたいのです。
＞他のuserfromtとは下記のようになります。
＞オプションボタンを選択してuserformを進んでいき、最後のuserformにコマンドボタンがあり、オプションボタンに名前を記録していくマクロです。

UserForm１以外のUserFormがいくつあるのか書いてない、
また、UserFormを進んできき、とはどういうことか
さらに、最後のUserformとは？
そしてまた、Userform1ではtextBoxの内容を記録するとあるが
他のUserFormではオプションボタンに名前を記録する、になってる
名前を記録するとは？
ここでいうオプションボタンはどのUserFormにあるのか
などなど疑問点を挙げれば限がありませんね。。(^^;;;

UserFormの数、それぞれのコントロール、それらの役目、転記するセル。。。など
やりたいことを実際に即して詳細に再度補足した方がいいでしょう。

補足

説明が下手で申し訳ありません。

順を追って説明したいのですが・・・。
どんなVBAが完成してほしいか書き出したほうがよろしいでしょうか？

書き出してみます。
シートは全部で６シート。
うち４シートは記録用です。
もう１シートはコンボボックスのリストデータ用です。
残り１シートは印刷用です。
印刷用シートにコマンドボタンが３個あります。
印刷用シートのコマンボボタン１をクリックすると、userform1が呼び出される。

userform1に持たせたい機能
１．　４個のオプションボタンがあり、オプションボタン毎にコンボボックスのリストが変化する。
２．　テキストボックスが５個ある。
３．　コマンドボタンがあり、そのコマンドボタンをクリックすると以下の用に動いてほしい。
○　リストから選択したものが印刷用シートA列１行と記録用シートのA列の空白セルに転送。
○　オプションボタン１を選択した場合は記録用のシート１に、オプションボタン２はシート２、オプションボタン３はシート３、オプションボタン４はシート４としたいです。
○　５個テキストボックスの内容を印刷用シートのあるセルと、記録用シートの、先ほどと同じ行に転送。
この場合テキストボックス１をB列、テキストボックス２をC列・・・・。

このようにしたいです。
わかります・・・か？
説明が下手で申し訳ありません。