マクロのコマンドボタン《ActiveXコントロール》のエラーの原因と解決方法について

1つのファイル（ブック）の中に２枚のシート（aとb）があり、「シートaのセル（a5）の値をコピーし、シートbのセル（b7）に貼り付ける」 という簡単なマクロを作り、シートb上にマクロボタンを設定しました。
シートを跨いで値をコピペするというものです。

《フォームコントロールの ボタン》にコードを設定し実行すると想定通りに動くのですが、《ActiveXコントロールのコマンドボタン》にコードを記述して設定した場合はエラーとなってしまいます。
エラー表示は下記の通りです。

実行時エラー’1004’:
Range クラスの Select メソッドが失敗しました。

マクロ用ボタンとしては、色を変えられる等、自由度が高い《ActiveXコントロールのコマンドボタン》の方を使いたいのですが、上記エラーになる原因と解決方法をご教示いただけませんでしょうか。

尚、図形にマクロを登録した場合は、想定通りに動きます。
また、シートを跨がず、同じシート上でのセル値のコピペの場合は、問題なく動きます。

OS Win10
Excel Microsoft 365 (2016)

よろしくお願いいたします。

No.4 ベストアンサー 回答者： Qchan1962 回答日時： 2021/09/21 13:42

こんにちは

すでに解決されていると思いますが、
ActiveXコントロールのコマンドボタンのコードははシートモジュールに記されます
＞Range("A5").Select ← この部分が黄色に色付けになっています。
からSheets("シートb")にボタンを設置した事が想像できます。

シートモジュールでRangeはシートオブジェクトが省略されている場合
書かれているシートオブジェクトが省略されています。
（標準モジュールの場合ActiveSheet）
なので
Sheets("シートa").Select
Range("A5").Select
シートaからシートbのA5セルを参照する事になり見つけられないので
1004となります。

なので
Sheets("シートa").Select
Sheets("シートa").Range("A5").Select

又は
Sheets("シートa").Select
ActiveSheet.Range("A5").Select
とすればコードは実行されると思います。

Sheets("シートa")にActiveXコントロールのコマンドボタンを設置した場合は、
最後の　Range("A1").Select　で同様の原因でエラーが返されると思います

すでに回答にあるように値参照（代入）で良ければ、
同範囲.Value=同範囲.Valueとするのが良いと思います。また、自動記録で作成したマクロコードは初めにSelectとSelectionなどを判り易く加工するのが良いと思います。慣れると簡単です。さらにオブジェクトでまとめるなども加工のテクニックですね。

独学なので間違った情報が含まれているかもしれません。

この回答へのお礼

Qchan1962さん
ご丁寧にご説明いただきありがとうございます。

>シートaからシートbのA5セルを参照する事になり見つけられないので
1004となります。

ご教示いただいたように、改めてシート名を明示すると、想定通りに実行できました。
Sheets("シートa").Select
Sheets("シートa").Range("A5").Select

大変勉強になりました。
ありがとうございました。

お礼日時：2021/09/21 18:01

No.3 回答者： fujillin 回答日時： 2021/09/21 12:00

こんにちは

ActivateやSelectメソッドはいろいろと面倒なので、省くようにするのが良いでしょう。
例えば、以下のように変えたら動作しませんか？

Private Sub CommandButton1_Click()
Worksheets("シートb").Cells(Rows.Count, 5).End(xlUp).Offset(1).Value = _
Worksheets("シートa").Range("A5").Value
End Sub

この回答へのお礼

fujillinさん
ご回答ありがとうございます。
ご教示いただきましたコードで、思い通りの動作ができました！
コピー → 貼り付けするのでなく、直接 値を代入するということでしょうか。
簡潔に記述できるのですね。
ありがとうございました。

お礼日時：2021/09/21 12:44

No.2 回答者： mygoonickname 回答日時： 2021/09/21 03:29

こんばんは。

推測ですが、ボタンがSelectされている状態で、セル関係のプログラムを
　実行しようとして、エラーになっているとか？

Msgbox Selection.Name　で何が表示されるでしょうか？

この回答へのお礼

mygoonicknameさん
早速のご連絡ありがとうございます。

>ボタンがSelectされている状態で、セル関係のプログラムを実行

開発タブの「デザインモード」はOFF状態で、ボタンはSelectされていないと思います。


>Msgbox Selection.Name　で何が表示されるでしょうか？

すみません。初心者なもので、仰っている意味がよくわかりません。
マクロボタンを押下直後に表示されるエラー表示は、質問に書きましたように下記です。そのことでしょうか？

実行時エラー’1004’:
Range クラスの Select メソッドが失敗しました。

上記エラー表示ウィンド？にある、「デバッグ」ボタンを押すと、実際のコードには、
Range("A5").Select ← この部分が黄色に色付けになっています。

お礼日時：2021/09/21 10:50

No.1 回答者： bonaron 回答日時： 2021/09/21 01:02

どんなコードを書いたんですか？

この回答へのお礼

bonaronさん

早速のご連絡ありがとうございます。
コードは下記の通りです。
関数（合計）のセルをコピーし、別シートに 値だけ貼り付けるものです。

Private Sub CommandButton1_Click()

Sheets("シートa").Select
Range("A5").Select
Selection.Copy

Sheets("シートb").Select
ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A1").Select

End Sub

お礼日時：2021/09/21 10:29