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

質問

エクセル2003です。
Sｈeet1　
　　　A　　　　B　　 　C　　　　　D
1　種類1　商品1 商品A　　商品あ
2　種類2　商品2　商品B　　商品い
3　種類3　商品3　商品C　　商品う　
4　　　　 　商品4　商品D　　商品え
5　 商品5　商品E　　商品お

Sｈeet2
　　AB CDF　　　　　G　　　　H　　　　　I
1　　　　　　　　　種類表示　　　　　商品表示　
2　　　　　　　　　種類表示　　　　　商品表示　
3　　　　　　　　　種類表示　　　　　商品表示　
4　　　　　　　　　種類表示　　　　　商品表示　
5　　　　　　　　　種類表示　　　　　商品表示　

*Sheet2のG1をダブルクリックでSheet1のA列をユーザーフォームのコンボボックス1にてセルに表示
　の上挿入
*コンボ1の選択によりコンボ2（Ｓｈｅｅｔ2のＩ1をダブルクリック）の表示を変更する
*コンボ1　種類1　→　コンボ2　Ｓｈｅｅｔ1のＢ列を表示の上セルに挿入
*コンボ1　種類2　→　コンボ2　Ｓｈｅｅｔ1のＣ列を表示の上セルに挿入
*コンボ1　種類3　→　コンボ2　Ｓｈｅｅｔ1のＤ列を表示の上セルに挿入
までは、出来きたのですが、Ｓｈｅｅｔ2の2行目以降も同作業をしたいのですが、、、
Offset等を使用するのでしょうか？
あまりわかっていないので詳しく教えて頂ければ幸いです

コード

Ｓｈｅｅｔ2のコード
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
With Target
If .Address = "$F$1" Then
UserForm1.Show
ElseIf .Address = "$H$1" Then

Dim k As Long, myFlg As Boolean, myArray
myArray = Array("種類1", "種類2", "種類3")
myFlg = False
For k = 0 To UBound(myArray)
If Range("F1") = myArray(k) Then
myFlg = True
End If
Next k
If myFlg = False Or Range("F1") = "" Then
MsgBox "種類を選択して", vbOKOnly
Range("F1").Select
Exit Sub
End If
UserForm2.Show
End If
End With
End Sub

ユーザーフォーム1のコード
Private Sub UserForm_Initialize()
ComboBox1.RowSource = "sHEET1!A1:A3"
End Sub

コンボ1のコード
Private Sub ComboBox1_Change()
Worksheets("Sheet2").Range("F1") = UserForm1.ComboBox1.Text
Worksheets("Sheet2").Range("H1") = ""
Unload Me
End Sub

ユーザーフォーム2のコード
Private Sub UserForm_Initialize()
With UserForm2.ComboBox1
Select Case Worksheets("Sheet2").Range("F1")
Case "種類1"
.RowSource = "Sheet1!B1:B5"
Case "種類2"
.RowSource = "Sheet1!C1:C5"
Case "種類3"
.RowSource = "Sheet1!D2:D5"
End Select
End With
End Sub

コンボ2のコード
Private Sub ComboBox1_Change()
Worksheets("Sheet2").Range("H1") = UserForm2.ComboBox1.Text
Unload Me
End Suｂ

このコードをいじくってＳｈｅｅｔ2以降も同作業できるようにお願いします

回答No.1

tsubuyuki · ベストアンサー

> このコードをいじくってＳｈｅｅｔ2以降も同作業できるようにお願いします

との事ですが、「以降」のシートはSheet1・2と同じフォーマットですか？
だとしたら、同様に書いて、シートの名前を変えればＯＫです。

さて、意地悪はさておき。

> Ｓｈｅｅｔ2の2行目以降も同作業をしたいのですが、、、

こちらが本題ですね。

・・本題に入る前に、もう一つ意地悪を。

> Sheet2のG1をダブルクリックで

とありますが、ご提示のコード

> With Target
> If .Address = "$F$1" Then
> UserForm1.Show
> ElseIf .Address = "$H$1" Then
> 省略

これしか「起動するセルの条件」にＧ１セルが無いように見えるのですが、
どうしてもＧ列で起動したいのであれば、以下の回答は無意味ですのでスルーなさってください。

さて、ご提示のコードから察するに
「Sheet2のＦ列またはＨ列の任意行のセルをダブルクリックしたらマクロを起動」と言うことですね。

だとすると、
　　With Target
　　　　If .Column = 6 Then
　　　　　　UserForm1.Show
　　　　ElseIf .Column = 8 Then
と書いてやります。
これで、ダブルクリックされたセルが６列目＝Ｆ列だったら、と言う意味に使えます。

同様に、Target.Rowで行を取れますので、以降の処理にはコレを使います。
つまり、一部を抜粋して、

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim TRow As Long, TCol As Long
Dim k As Long, myFlg As Boolean, myArray

　　Cancel = True

　　TRow = Target.Row: TCol = Target.column
　　If TCol = 6 Then
　　　　UserForm1.Show
　　ElseIf TCol = 8 Then
　　　　myArray = Array("種類1", "種類2", "種類3")
　　　　myFlg = False
　　　　For k = 0 To UBound(myArray)
　　　　　　If Range("F" & TRow) = myArray(k) Then
　　　　　　　　myFlg = True
　　　　　　End If
　　～以下、処理続行

と言うくらいの感じでしょうか。
（Ｗｉｔｈは却って邪魔な気がしたので取っ払いました。）

その他の部分にも応用します。
例えば、コンボ2のコードはこんな感じ。

Private Sub ComboBox1_Change()
　　Worksheets("Sheet2").Range("H" & ActiveCell.Row) = UserForm2.ComboBox1.Text
　　Unload Me
End Sub

ただし、
コンボ2の処理が動く前に「アクティブセルがダブルクリックしたセルから動かないように」
しておかないといけません。
おそらく、ご提示の処理であれば問題なかろうと思います。

とりあえず、参考までに。

お礼

> このコードをいじくってＳｈｅｅｔ2以降も同作業できるようにお願いします
すみません　Sheet2の2行目以降が正解です　

> Sheet2のG1をダブルクリックで
すみません　F1です

間違えがたくさんありますね。今後見直しを強化します

本題ですが、大変参考になりました
勉強しながら成功しました
ありがとうございます