Pasteで増やしたフレーム内のボタン操作（続）

質問

すみません。一旦、解決かと思ったのですが続きで不可思議な問題が発生したので質問させてください。
添付した画像の通り、UserForm1を起動させた際、Frame1が5個コピペされます。
次に各Frame内のボタンを押下するとそのFrame内のTextBox３つが消去されるようにできました。※画像は比較を容易にするために２番目と５番目のボタンを押下しています。

当初はこれで完全に解決だと思ったのですが、念の為、新規Excelファイルで.xlsm形式で保存した後、まったく同じ内容のフォームとVBAを記述したところ、そちらは一番右側のTextBox（TextBox3など）しか消去されないのです。
２枚並べて正常に動くVBAをコピペしても、ダメなVBAは正常な動作をしてくれませんでした。しかし一度全てのExcelファイルを保存して閉じた後、再度、ダメな方を開いたところ正常動作が確認できました。

話が長くなって申し訳ございませんが、話はもう少し続きます。
実は今回、正常動作したファイルのPC1とは別のPC2ではどうにもこうにもダメなままなのです。

【PC1】Office2021
・正常動作VBA
・ダメなVBA　→　保存・閉じる・再度開いたら正常動作

【PC2】Office365
・ダメなVBA　→　保存・閉じる・再度開いてもダメなまま

そこでこのような症状はExcelバージョンの違いにより発生していると思われますでしょうか？それとも私のVBAの書き方（For i 文のところ）に誤りがあるのかご教示いただけないでしょうか？
ちなみに下記のVBAはPC1であれば意図した通りに動きました。
-------------------------------------------------------------
◆Class1
-------------------------------------------------------------
Private WithEvents Target As MSForms.CommandButton
Private mBoxName As String
-------------------------------------------------------------
'新しくイベントを作成する
Public Sub SetCtrl(New_Ctrl As MSForms.CommandButton, ByVal mBoxNameX As String)
Set Target = New_Ctrl
mBoxName = mBoxNameX
End Sub
-------------------------------------------------------------
Private Sub Target_Click()
UserForm1.Controls(mBoxName).Value = ""
End Sub

-------------------------------------------------------------
◆UserForm1
-------------------------------------------------------------
Private mCmdButton() As Class1
Private Sub UserForm_Initialize()
Dim j As Long
Me.Frame1.SetFocus
Me.大外フレーム.Copy

'このFor文でTextBox1～TextBox3を消去する・・・ダメな場合はText3のみ消去される
For i = 1 To 3
Call Make_Event(1, Me.Controls("TextBox" & i).Name)
Next

For j = 1 To 5
Me.大外フレーム.Paste
Me.Controls("Frame" & j + 1).Caption = "Frame" & j + 1
Me.Controls("Frame" & j + 1).Left = Me.Frame1.Left
Me.Controls("Frame" & j + 1).Top = Me.Frame1.Top + Me.Frame1.Height * j

Me.Controls("TextBox" & j * 3 + 1).Value = Me.Controls("TextBox" & j * 3 + 1).Name
Me.Controls("TextBox" & j * 3 + 2).Value = Me.Controls("TextBox" & j * 3 + 2).Name
Me.Controls("TextBox" & j * 3 + 3).Value = Me.Controls("TextBox" & j * 3 + 3).Name

Me.Controls("CommandButton" & j + 1).Caption = Me.Controls("CommandButton" & j + 1).Name

'このFor文でTextBox4～TextBox6等を消去する・・・ダメな場合は一番右側のTextBoxのみ消去される
For i = 1 To 3
Call Make_Event(j + 1, Me.Controls("TextBox" & j * 3 + i).Name)
Next

Next
Me.大外フレーム.ScrollHeight = Me.大外フレーム.ScrollHeight + Me.Frame1.Height * j 'j=(Past回数+1)

End Sub
Function Make_Event(ByVal j As Long, ByVal mBoxNameX As String)
ReDim Preserve mCmdButton(j)
Set mCmdButton(j) = New Class1
mCmdButton(j).SetCtrl Me("CommandButton" & j), mBoxNameX
End Function

回答No.8

kkkkkm · ベストアンサー

> しかしFor文のTo以降の　(5+1)*15　となっている理由がどうしても分からないのです。90と書くのと何か違いがあるのでしょうか？

(5 + 1)の5はコピーする回数ですので、回数は可変だと思いますから、5の値は都度フレームを必要とするデータ数(シート内のデータ数などになると思ってます)から割り当ててください。そのために90としていません。

For j = 1 To 5
Me.大外フレーム.Paste

の5と同じ値になります。

ただ、 UserForm_Initialize()内の
For i = 1 To (5 + 1) * 15
Me.Controls("ComboBox" & i).Value = Me.Controls("ComboBox" & i).Name
Next
の部分はテスト時にボックス名の確認と消去テスト(消去用データが必要)のために入れているので、本番で起動時に未入力状態でいい場合は必要が無いと思います。

Me.Controls("TextBox" & j * 5 + 1).Value = Me.Controls("TextBox" & j * 5 + 1).Name
などの部分も同じです。

お礼

kkkkkm様
本日、本番ファイルへ実装して何とか期待通りの動きをするようになりましたのでご報告させていただきます。
また、For i = 1 To (5 + 1) * 15 については、最初に一時的に決めた回数である5回の意味を考えたら仰るとおり可変で対応することを前提になりますね。私が頭でっかちになっていて冷静な判断ができていませんでした。
今後もVBAで様々なものを作りながらスキルを上げて行きたいと思います。本当に有難うございました。

回答No.7

kkkkkm

TextBoxが5個、ComboBoxが15個で試してみたコードです。時間があれば試してみて下さい。

Private mCmdButton() As Class1
'↑必ず先頭に

'大外フレームの外側にコマンドボタンを作成の時
Private Sub ClearCommandButton_Click()
Dim i As Long

For i = 1 To (5 + 1) * 5
Me.Controls("TextBox" & i).Value = ""
Next
For i = 1 To (5 + 1) * 15
Me.Controls("ComboBox" & i).Value = ""
Next

End Sub

Private Sub UserForm_Initialize()
Dim j As Long
Dim i As Long
Dim mBoxNameF(5) As String
Dim mComboNameF(15) As String

Me.Frame1.SetFocus
Me.大外フレーム.Copy
For i = 1 To 5
mBoxNameF(i) = "TextBox" & i
Next
For i = 1 To 15
mComboNameF(i) = "ComboBox" & i
Next
Call Make_Event(1, mBoxNameF(), mComboNameF())

For j = 1 To 5
Me.大外フレーム.Paste
With Me.Controls("Frame" & j + 1)
.Caption = "Frame" & j + 1
.Left = Me.Frame1.Left
.Top = Me.Frame1.Top + Me.Frame1.Height * j
End With
Me.Controls("TextBox" & j * 5 + 1).Value = Me.Controls("TextBox" & j * 5 + 1).Name
Me.Controls("TextBox" & j * 5 + 2).Value = Me.Controls("TextBox" & j * 5 + 2).Name
Me.Controls("TextBox" & j * 5 + 3).Value = Me.Controls("TextBox" & j * 3 + 3).Name
Me.Controls("TextBox" & j * 5 + 4).Value = Me.Controls("TextBox" & j * 5 + 4).Name
Me.Controls("TextBox" & j * 5 + 5).Value = Me.Controls("TextBox" & j * 5 + 5).Name
Me.Controls("CommandButton" & j + 1).Caption = Me.Controls("CommandButton" & j + 1).Name
For i = 1 To 5
mBoxNameF(i) = "TextBox" & j * 5 + i
Next
For i = 1 To 15
mComboNameF(i) = "ComboBox" & j * 15 + i
Next
Call Make_Event(j + 1, mBoxNameF(), mComboNameF())
Next
Me.大外フレーム.ScrollHeight = Me.大外フレーム.ScrollHeight + Me.Frame1.Height * j 'j=(Past回数+1)

For i = 1 To (5 + 1) * 15
Me.Controls("ComboBox" & i).Value = Me.Controls("ComboBox" & i).Name
Next

End Sub

Function Make_Event(ByVal i As Long, ByRef mBoxNameX() As String, ByRef mComboNameX() As String)
ReDim Preserve mCmdButton(i)
Set mCmdButton(i) = New Class1
mCmdButton(i).SetCtrl Me("CommandButton" & i), mBoxNameX(), mComboNameX()
End Function

クラス

Private WithEvents Target As MSForms.CommandButton
Private mBoxName(5) As String
Private mComboName(15) As String

'新しくイベントを作成し、変数の値をセット
Public Sub SetCtrl(New_Ctrl As MSForms.CommandButton, ByRef mBoxNameX() As String, ByRef mComboNameX() As String)
Dim i As Long

Set Target = New_Ctrl
For i = 1 To 5
mBoxName(i) = mBoxNameX(i)
Next
For i = 1 To 15
mComboName(i) = mComboNameX(i)
Next
End Sub

Private Sub Target_Click()
Dim i As Long

For i = 1 To 5
UserForm1.Controls(mBoxName(i)).Value = ""
Next
For i = 1 To 15
UserForm1.Controls(mComboName(i)).Value = ""
Next

End Sub

お礼

kkkkkm様、お知らせいただきましたTextBox5個、ComboBox15個のパターンですが期待通りの動きをしてくれました。有難うございます。
現在はテストファイルなので明日、本番ファイルに適用してみようと思います。大外フレームリセットについても活用させていただきます。

ところで1つ（本当は1つではありませんが）疑問な箇所がございましたので宜しければご教示ください。
今回のNo7以前はComboBoxの.nameは15個分書いていた（TextBox分と合わせて20行）のですが
No7でお知らせいただいたスクリプトには下記の通り
For i = 1 To (5 + 1) * 15
Me.Controls("ComboBox" & i).Value = Me.Controls("ComboBox" & i).Name
Next
となっており、非常にスッキリしてて良いと感じました。
しかしFor文のTo以降の　(5+1)*15　となっている理由がどうしても分からないのです。90と書くのと何か違いがあるのでしょうか？またFrame数が増えますとComboBox数も比例して増え、90または(5+1)*15を超えてしまうと思うのですが、なんのために　(5+1)*15　となっているのか後学のためにお知らせいただけますと幸いです。

お礼メッセージなのに質問してしまいまして申し訳ございませんが、お手すきの際にもう少しお付き合いの程、宜しくお願い致します。

～No7以前に書いていた15行～
Me.Controls("ComboBox" & j * 15 + 1).Value = Me.Controls("ComboBox" & j * 15 + 1).Name
・・・中略・・・
Me.Controls("ComboBox" & j * 15 + 15).Value = Me.Controls("ComboBox" & j * 15 + 15).Name

回答No.6

kkkkkm

> 20個分の変数引渡は可動性も悪いですし。。。何より修正が大変でしたので。(^_^;)

数が多いとNo.2では確かにきついですね、配列渡しのNo.4の方が楽だと思います。

> Frame内の全Boxをすべて消去できるよう色々と試してみようと思います

大外フレーム内をすべて消去でしたら

大外フレームの外側にコマンドボタンを作成して
そのままだと名前が
CommandButton2
になり、コピーした時にフレーム内のボタンの名前がCommandButton3からになり合わなくなりますので
ClearCommandButton
など適当に変更して
通常のクリック時イベントで
Private Sub ClearCommandButton_Click()
Dim i As Long

For i = 1 To (5 + 1) * 5
Me.Controls("TextBox" & i).Value = ""
Next
For i = 1 To (5 + 1) * 15
Me.Controls("ComboBox" & i).Value = ""
Next

End Sub

としておけばTextBoxtとはComboBoxは全てクリアされます。

(5 + 1) の「5回コピーの5」はデータの数を
Private Sub UserForm_Initialize()
の時に設定するものに合わせておけばいけるのじゃないでしょうか。

お礼

kkkkkm様、先程からNo3とNo4の配列を使ったVBAを試しておりました。
これですと見た目もスッキリしていて良いですね。
現時点ではTextBoxとComboBoxと書き換えて別々ではあるのですが、それぞれ動作することが確認できました。
しかし、同時に動作させようとネットで調べまくりで色々試しておりますがうまく言っていない状況です。

Frame内の全消去については「大外フレーム」ではなく「Frame●」（●は数字）内だけになります。
私が最終的に作ろうとしているのはFrame●の中にTextBoxが5個、ComboBoxが15個ですのでこの合計20個の値を消去するという感じです。例としてFrame1～5まで表示させ、そのうちFrame3とFrame4の情報は不要だから各Frame内のクリアボタンで消去します。残ったFrame1、2、5はExcelシートへ詰めて書き出すということを行いたいと思います。
フォーム上の情報をExcelファイルに詰めて書き出すのは簡単にできると思いますが、それ以前のFrame増殖の部分で躓いています。とは言えこちらのサイトの情報にたどり着く前はこんなに理想的な動作ができるなんて思っても見なかった訳ですが。。。まだまだ配列も理解不足な初心者の領域を脱し得ない状況ですが、少しずつ習得していこうと思います。有難うございました。

回答No.5

kkkkkm

No.4で

For i = 1 To 3
mBoxNameF(i) = Me.Controls("TextBox" & i).Name
Next

の後の

Call Make_Event(1, mBoxNameF)

が抜けてました。

で、いまさらですが

mBoxNameF(i) = Me.Controls("TextBox" & i).Name
は
mBoxNameF(i) = "TextBox" & i
でいけると思います。
後のほうのも同じような感じで。

多分、最初に何かの都合でテスト的にそのようにしたものをそのまま流用したのだと思います。

お礼

kkkkkm様

いつも有難うございます。
こちらNo2で試したところ期待した動きになりました。

またご指摘いただきました昨晩の【PC1】で正常動作した「For i 文」のVBAをそのままPC2へ持っていきVBA実行したところダメな動きしかしませんでした。
元々「For i 文」のままですとご指摘いただきました通り、For i = 1 to 3の最後の3番目の値によって1、2が上書きされているようですので当然と言えば当然の動きですね。
Office2021ですと何故か私が期待した通りに動いたので本当に謎です。

さて、すみません。ここからまた長いです。。。
これでようやく完成できるぞ！と意気揚々と本番ファイルに書き込もうとしたのですが・・・ここで問題が発生しました。その本番ファイルのFrame内にはTextBoxが5個、ComboBoxが15個の合計オブジェクト数が20個なのです。そこでお教えいただいたTextBox3つ分のスクリプトを少しずつ修正していきました。
最初からいきなり本番ファイルの20個全部でやってみたのですがComboBoxの場合はどうなんだ？という感じで迷いと不安で頭の中がぐちゃぐちゃになってしまったため結局テストファイルを新たに作って動作確認と修正作業を行いました。オブジェクト数が12個（それぞれ6個ずつ）までは正常動作を確認できましたが、そこから欲張って24個に増やしたところ途端に動かなくなりました。私の記述のどこかが間違っているのかもしれませんが、そこから数を15まで減らしたりしましたが結局エラーが出るようになってしまったので本日は諦めました。

先程、もう少し良い方法がないか考えたところFrameに基づく消去ボタンを押下した際の動作なので、Frame内の全Boxを消去すれば良いのでは？という結論に達しました。
20個分の変数引渡は可動性も悪いですし。。。何より修正が大変でしたので。(^_^;)
明日からFrame内の全Boxをすべて消去できるよう色々と試してみようと思います。有難うございました！

回答No.4

kkkkkm

TextBox名の引数を配列にして渡していくという手も。

mBoxNameF(2)
で
For i = 0 To 2
mBoxNameF(i) = Me.Controls("TextBox" & i + 1).Name
Next

みたいに全て0 To 2にしてもいいと思いますが、TextBoxの番号とiが違うのでわかりにくくなると思って 1 to 3にしています。

Call Make_Eventのところだけ

Dim mBoxNameF(3) As String

For i = 1 To 3
mBoxNameF(i) = Me.Controls("TextBox" & i).Name
Next

For i = 1 To 3
mBoxNameF(i) = Me.Controls("TextBox" & j * 3 + i).Name
Next
Call Make_Event(j + 1, mBoxNameF)

Make_Eventは

Function Make_Event(ByVal i As Long, ByRef mBoxNameX() As String)
ReDim Preserve mCmdButton(i)
Set mCmdButton(i) = New Class1
mCmdButton(i).SetCtrl Me("CommandButton" & i), mBoxNameX()
End Function

クラスを

Private WithEvents Target As MSForms.CommandButton
Private mBoxName(3) As String

'新しくイベントを作成する
Public Sub SetCtrl(New_Ctrl As MSForms.CommandButton, ByRef mBoxNameX() As String)
Dim i As Long

Set Target = New_Ctrl
For i = 1 To 3
mBoxName(i) = mBoxNameX(i)
Next
End Sub

Private Sub Target_Click()
Dim i As Long

For i = 1 To 3
UserForm1.Controls(mBoxName(i)).Value = ""
Next
End Sub

回答No.3

kkkkkm

No.2で
ループで上書きされている
と記載しましたが、開放せずに再度セットしている感じなのでその動作が保証されているかどうかはわかりません。

回答No.2

kkkkkm

For i = 1 To 3
Call Make_Event(1, Me.Controls("TextBox" & i).Name)
Next

の場合Class1でセットされるのは最後のTextBox3だけ(ループで上書きされている)なので最後のText3のみ消去されるのが正常な動作だと思います。2013では最後だけ消去されました。

ですので、ベタな感じですが

Call Make_Event(1, Me.Controls("TextBox1").Name, Me.Controls("TextBox2").Name, Me.Controls("TextBox3").Name)

後の方は

Call Make_Event(j + 1, Me.Controls("TextBox" & j * 3 + 1).Name, Me.Controls("TextBox" & j * 3 + 2).Name, Me.Controls("TextBox" & j * 3 + 3).Name)

Make_Eventを

Function Make_Event(ByVal i As Long, ByVal mBoxNameX1 As String, ByVal mBoxNameX2 As String, ByVal mBoxNameX3 As String)
ReDim Preserve mCmdButton(i)
Set mCmdButton(i) = New Class1
mCmdButton(i).SetCtrl Me("CommandButton" & i), mBoxNameX1, mBoxNameX2, mBoxNameX3
End Function

Class1の方は

Private WithEvents Target As MSForms.CommandButton
Private mBoxName1 As String
Private mBoxName2 As String
Private mBoxName3 As String

'新しくイベントを作成する
Public Sub SetCtrl(New_Ctrl As MSForms.CommandButton, ByVal mBoxNameX1 As String, ByVal mBoxNameX2 As String, ByVal mBoxNameX3 As String)
Set Target = New_Ctrl
mBoxName1 = mBoxNameX1
mBoxName2 = mBoxNameX2
mBoxName3 = mBoxNameX3
End Sub

Private Sub Target_Click()
UserForm1.Controls(mBoxName1).Value = ""
UserForm1.Controls(mBoxName2).Value = ""
UserForm1.Controls(mBoxName3).Value = ""
End Sub

で試してみてください。

回答No.1

chie65535

フォームのコピペを行った場合、オブジェクトの名前が、TextBox1、TextBox2、TextBox3のように、番号順に付く保証はありません。

状況により、１、４、５のように間が飛んだりします。

なので
Me.Controls("TextBox" & i).Name
のように「１、２、３の順」などと、オブジェクト名を決め打ちしてはいけません。

TextBox○やFrame△の○や△部分の数字が何になっていても同じ動作をするように記述しないとなりません。コピペを行うと○や△部分の数字が何になるか判らない、と考えて下さい。

補足

chie65535様

ご回答有難うございます。
>フォームのコピペを行った場合、オブジェクトの名前が、TextBox1、TextBox2、TextBox3のように
>番号順に付く保証はありません。
>状況により、１、４、５のように間が飛んだりします。
はい。そうですね。ただし今回はきちんと番号順にペーストされていることは確認済みです。
ちなみにダメなときは３、６、９だけがそれぞれのボタンで消去されてしまいます。
これはFor文で「1 to 3」の「3」がセットされるためと考えます。1と2は最後の3で上書きされてしまっているのだと思ってはいるのですが、同じVBAでも正常動作するものもあるので悩んでおります。

Frame1：１、２，３　ボタン1
---------------------------
Frame2：４，５，６　ボタン2
---------------------------
Frame3：７，８，９　ボタン3