エクセルVBAで質問です

質問

あるブックのシートに入力するようにしています。そこには関数や、
リストなどさまざまな設定がされています。そのブックにコードを書いてマクロ実行ボタンもそのシートに付けたいと思っています。
このシートをデスクトップにコピーして別ブックとして作成したいのですが、コードを教えていただけないでしょうか。できるかわかりませんが、関数が設定されてある箇所は関数としてではなく、値としてコピーされると都合がいいです。
また、作成されたブック(コピーしたシート)には、マクロのボタンなんかが残らない形ではできないものでしょうか？

回答No.2

Wendy02 · ベストアンサー

こんにちは。

ご指摘のものは、フォームツールのボタンですね。
フォームツールのボタンでしたら、簡単に変更するには、標準モジュールに貼り付けたコードから、　"Private" を取ればよいのです。本来は、そのプロシージャの名称がヘンですが、「Sub CommandButton1_Click()」　とすれば、選択できますし、「Sub ボタン1_Click」でもよいです。

コントロールツールのコマンドボタンは、

コントロールツールボックスを、ワークシート画面の－表示メニュー－ツールバーから出します。（メニューバーのところを右クリックしても可能です。）

次に、コントロールツールボックスの中のコマンドボタンをワークシートに貼り付けます。

その[コマンドボタン]は、編集可能状態ですから、それを「ダブルクリックする」と、VB　Editr 画面が自動的に現れます。

そうすると、
----------------------------------
Private Sub CommandButton1_Click()

End Sub
----------------------------------

という中身のコードのない文字が出ているはずです。

そこに、私の書いた中身を入れてあげます。
それを、Alt + Q で、画面を閉じます。

今は、コントロールツール-ボックスの先頭の青い三角定規▲（直角の二等辺三角形）が、[オン]になっていますから、それをクリックして、[オフ]にして、コントロールツールボックスを非表示にしたら、出来上がりです。

p.s.
余計な知識ですが、Excelの一般のマクロは、標準モジュールに入れますが、シートに直結したコントロールツールは、シートモジュール－左側のプロジェクト・エクスプローラの、Sheet1, Sheet2, Sheet3 に入れることになります。しかし、フォームツールのコントロールは、一般的に、標準モジュールに入ります。なぜ違うかというと、Excelのプログラム・オブジェクトの管理する場所が違うからです。

一応、Excel2000以降は、これらボタン等のツールは、コントロールツール側が主流になりました。フォーム側は簡単ですが、Ver.5 を主に使われてきたもので、若干、使い方が違います。

回答No.1

Wendy02

こんばんは。

そのマクロは、比較的良く知られたものだと思います。
ただし、人によって解釈の差はありますから、若干違いはあります。

コントロールツールのコマンドボタン－ダブルクリックして、VBEを開いたら、中身をのみを登録してください。

ファイル名は、日付型にしましたので、071118.xls　というものになります。同名がある場合は、07118a ～z .xls までとなります。
'--------------------------------------------

Private Sub CommandButton1_Click()
　　Dim fname As String
　　Dim mDeskTop As String
　　Dim i As Integer
　　Dim OrgSh As Worksheet
　　mDeskTop = "C:\Documents and Settings\[ユーザー名]\デスクトップ\"
　　'または、
　　'mDeskTop = "C:\Documents and Settings\All Users\デスクトップ\"
　　Set OrgSh = ActiveSheet
　　fname = mDeskTop & Format$(Date, "yymmdd")
　　With Workbooks.Add
　　　　OrgSh.Cells.Copy
　　　　ActiveSheet.Cells.PasteSpecial xlPasteValues
　　　　ActiveSheet.Cells(1, 1).Select

　　　　'同名のファイルがある場合
　　　　Do Until Dir(fname & ".xls") = "" Or i > 25
　　　　　　fname = Mid$(fname, 1, Len(fname) - 1) & Chr(97 + i)
　　　　　　i = i + 1
　　　　Loop
　　　　If i < 27 Then
　　　　　　.SaveAs fname & ".xls"
　　　　Else
　　　　　　MsgBox "デスクトップに同名ファイルがあります。", 48
　　　　　　Exit Sub
　　　　End If
　　End With
　　Set OrgSh = Nothing
End Sub

お礼

大変詳しくご説明いただきありがとうございました。コピーできました。教えていただき助かりました。本当にありがとうございます。

補足

ご回答ありがとうございます。早速登録させてもらおうと思ってやってみたのですが、登録がうまくできませんでした。
いつもはBVEの標準モジュールを開いて、そこに入力またはコピーしています。そして閉じて、ボタンを配置すると、自動で登録するマクロを指定するウィンドウが現れるので、指定する。というようにボタンを利用できるようにしています。Wendy02さんが仰る、
>コントロールツールのコマンドボタン－ダブルクリックして、VBEを開いたら、中身をのみを登録してください。
この「コントロールツールのコマンドボタン－ダブルクリック」がどういうことなのか理解できませんでした。勉強不足でスミマセン、もう一度お教え願えないでしょうか。