なぜかフォームは上書きされません。

質問

こんにちは。
下記のようなフォームにボタンを追加し、セルにデータを書き込み、最後に上書きするマクロを作ってみました。
セルのデータは上書きされますが、なぜかフォームは上書きされません。フォームの表示で追加したボタンも表示されます。
どなたかご教示願います。
--------------------------------------------
Sub testVBA()
Dim WOBJ As Object
With UserForm1
Set WOBJ = .Controls.Add("Forms.CommandButton.1", "MyCom", True)
End With
Range("A1").Value = "aaaaaaaaaaa"
ThisWorkbook.Save
UserForm1.Show
End Sub

回答No.1

kkkkkm · ベストアンサー

> セルのデータは上書きされますが、なぜかフォームは上書きされません

あとからデザインモードで見たらボタンが追加されていないという事でしたら
.Controls.Add
は実行時に動的に追加するという事ですので、セルのデータが変化したような感じで元のフォームのデザインが変わることはありません。

お礼

皆様のご教示を参考に試行錯誤して目的を達成できました。
ありがとうございました。

回答No.4

imogasi

#2です。
もし、シートのA列に順次データを入力したい、というのが目的ならば、
＃２とは、別の書き方をするとすれば、下記が易しいものかもしれない。
A1セルには見出し文言を入れておく。（End(xlUp）の初期値対策）
・ユーザーフォーム1の上にテキストボックスを１つ貼り付け。
・ユーザーフォーム1の上にコマンドボタンを１つ貼り付け。
コマンドボタンは、テキストボックスの文字列等の終了したことを知らせるため。
（他にテキストボックスのイベントの利用等あるが、使いずらいと思うのでこうする）
・下記コード作製
Private Sub commandbutton1_click()
Set cl = Worksheets("Sheet1").Range("A10000").End(xlUp)　'A列最下行捕捉
cl.Offset(1).Value = UserForm1.TextBox1.Text　’直下行のセルにテキストボックスの値設定
UserForm1.TextBox1.Text = "" '次のデータ入力のため消去
End Sub
・Sub/ユーザーフォームの実行（とりあえず、操作で）
・テキストボックスに「aaa」など、文字列入力
・データを入力後、コマンドボタンをクリック
シートのA２セルに値がセットされる。その後は、コマンドボタンをクリックごとに、順次直下行にデータがセットされる。
（数値・数字の字文字入力の場合はVAL()を被せること）
・ブックを保存（データを全て入力後）
これでエクセルブックの指定シートのA列セルにデータが保存される。
ー
手動操作で、コントロールを作成すると、ブックを閉じた後、Userform1に、そのコントロールは保存される。
でもコントロールを動的作成すると、コントロールを持ったフォームは保存されない。
次回実行時には、もし
Private Sub UserForm_Initialize()

End Sub
の中にコントロールの動的作成のコードを入れてあるのあれば、Initializeの際に、
コントロールは再作成されるので、差しさわりはない。
VBAでコントロールを動的に、フォームに貼り付け後、そのフォーム状態を保存するやり方は、現在では、小生には、調べても判っていない。

お礼

皆様のご教示を参考に試行錯誤して目的を達成できました。
ありがとうございました。

回答No.3

kkkkkm

No.1のおまけです。

動的にコントロールを追加した場合イベントプロシージャも動的に追加することになります。

たとえばクリックイベントでしたら

挿入でクラスモジュールを追加

Class1ができるのでそこに (名前は変更できます)

Private WithEvents mTarget As MSForms.CommandButton

Public Sub SetCtrl(New_Ctrl As MSForms.CommandButton)
Set mTarget = New_Ctrl
End Sub

Private Sub mTarget_Click()
'クリックイベントでしたいことを記載
MsgBox mTarget.Name & "がクリックされました"
End Sub

現在のコードのあるモジュールか標準モジュールに

Private mCtrl As New Class1 '一番上に クラスの名前を変更したらここのClass1も変更

Sub testVBA()
Dim WOBJ As Object
With UserForm1
Set WOBJ = .Controls.Add("Forms.CommandButton.1", "MyCom", True)

mCtrl.SetCtrl .Controls("MyCom") '←追加

End With
Range("A1").Value = "aaaaaaaaaaa"
ThisWorkbook.Save
UserForm1.Show
End Sub

お礼

皆様のご教示を参考に試行錯誤して目的を達成できました。
ありがとうございました。

回答No.2

imogasi

そうなるのは、当たり前でしょう。
質問の、やりたいことが明確に述べられていません。
何がしたいのですか？
普通は
（１）ユーザーフォームのコントロールのイベントをきっかけに
　　フォームのデーターー＞シートのセルへ、などの処理
（２）ユーザーフォームの（通常は）選択した内容をーーー＞フォームーー＞シートのセルへ
（３）ユーザーフォームの（通常は）入力した内容（テキストボックスの入力内容など）をーー＞シートのセルへ
（４）その他
などが多いですが、どれですか？
自分でシートにRange("A1").Value = "aaaaaaaaaaa"としてしまっているが、コントロールとどう関係する？
ーー
コマンド・ボタンの利用は、上記の（１）のタイプが多いが、そのためには、
CommandButtonのClickイベントプロシージュアーを書かないとダメでは。
下記のようなのを考えます。
内容は、小生の想像です。このままの書き方ではシートはアクチブシートのＡ１セル対象です。本当は明示的にそれを書いた方がよい。
Private Sub CommandButton1_Click()
Range("A１") = "aaa"
End Sub
ーー
小生も、日ごろＶＢＡでのコントロール作製の練習・勉強をしてます（特に動的な作製）が、（自慢ではないが）判らないことが多いです。個人的に難しいと思ってます。
ＷＥＢの記事も少なく、米国発のＷＥＢ記事などに行き着いたりします。
コントロールの作成は、フォーム上に載せるのは、手動で行い、イベントで行う、処理コードの記述は、そのオブジェクトーイベント選択で、やるようにしては。
例
Private Sub ＸＸＸ_Click()
ーー＞処理のＶＢＡコードだけをここに書く
End Sub

お礼

皆様のご教示を参考に試行錯誤して目的を達成できました。
ありがとうございました。