• ベストアンサー

Private Subをコピーしない

シートにPrivate Subが組み込まれた状態で、そのシートを別のブックに移動した場合、Private Subも同時に移動されてしまいます。 シートの移動を行った場合Private Subのみ移動しない方法はないのでしょうか？ 埋め込んであるのは Private Sub Worksheet_Deactivate() On Error Resume Next CommandBars("××").Delete Call ＊＊＊ End Sub となっています。 別ブックに移動した場合、Resume Nextとなっているのにエラーで停止します。このエラーを出さない方法でも構いません。 別の方法として新しいシートを作成してCellsコピーを行うと共に、印刷範囲を指定してから移動する方法も試したのですが、シートに複数のページがある場合は微妙に印刷範囲がずれてしまって上手くいきませんでした。 宜しくお願い致します。

質問者が選んだベストアンサー

  • ベストアンサー
  • onlyrom
  • ベストアンサー率59% (228/384)
回答No.2

コードをSheetモジュールではなくて ThisWorkbookモジュールのSheetDeactivateイベントに書いておけばいいと思いますが。 '--------------------------------------------- Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) 　　If Sh.Name = "該当シー名" Then 　　　　On Error Resume Next 　　　　CommandBars("××").Delete 　　　　Call XXXX 　　End If End Sub '---------------------------------------------- 以上。

ae-1sp
質問者

お礼

自己解決いたしました。 CommandBars("××").Deleteを Application.CommandBars("××").Visible = Falseとすると実行する事が出来ました。 ありがとうございます。

ae-1sp
質問者

補足

Callは出来るのですがCommandBars("××").Deleteが実行されません。 On Errorを解除すると 「実行時エラー’91’ オブジェクト変数またはwithブロック変数が設定されていません。」 と出てきます。 Sheetモジュールに埋め込んでいる場合は問題なく動作するのに何故なのでしょうか？

全文を見る
すると、全ての回答が全文表示されます。

その他の回答 (1)

  • BellBell
  • ベストアンサー率54% (327/598)
回答No.1

VBAのエディタで、標準モジュールを追加して、関数をPublicで宣言してそこに書いたらダメですかね？ その場合、シートのコピーや移動をしても、プログラムコードは埋め込まれていないように思います。

ae-1sp
質問者

補足

シートは複数有って、特定のシートを閉じたときのみCommandBars("××").Deleteを実行したいのです。 ＰublicではそのシートがDeactivateになった時に自動的に実行できるのでしょうか？

全文を見る
すると、全ての回答が全文表示されます。
  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • Private Subについて教えてください

    こんにちは。 マクロ初心者です。 現在マクロを書いていて、ちょっと困ったことがありました。 初心者なのでネットで調べたり、以前こちらにも質問させて頂いたりしてやっと出来たのですが・・・。 現在やりたいことはSheet2に数字を入れるとSheet1のセルの色が変わるようにしています。 1～15までの数字にそれぞれカラー設定して数字を入力すると思ったようにSheet1のセルの色が変わるのですが、今後使用していくにあたって データを値貼り付けするということです。 値貼り付けだとマクロが走りません。 何か良い方法はありますか？ ちなみにこちらが問題のマクロです。 Private Sub Worksheet_Change(ByVal Target As Range) Dim IColor As Integer Dim R As Range For Each R In Target Select Case R Case "1" IColor = 56 Case "2" IColor = 16 Case "3" IColor = 13 Case "4" IColor = 39 Case "5" IColor = 17 Case "6" IColor = 37 Case "7" IColor = 41 Case "8" IColor = 11 Case "9" IColor = 10 Case "10" IColor = 4 Case "11" IColor = 6 Case "12" IColor = 46 Case "13" IColor = 40 Case "14" IColor = 22 Case "15" IColor = 26 Case Is >= 16 IColor = 3 End Select Next i = Target.Row Worksheets("Sheet1").Range("B3:F7").Cells(i - 3).Interior.ColorIndex = IColor End Sub モジュールですとマクロ実行で走りますが、Private Subの場合で値貼り付けで走る方法などありますか？ また、もし書いたマクロが違うようであれば手直しなど一緒にして頂けると助かります。 どなたか詳しい方宜しくお願い致します。

  • エクセル2003で作成したVBAを2007で使用したい

    エクセル2003で下記のようなVBAを作成していました。 作成した物を使用者が印刷設定を変更できないように ブックを開くと同時に印刷ボタンなどを非表示にしていたのですが・・・ 2007でも使用出来るようにしたいと思ったのですが 2007ではこのままではエラーが出てしまうようです。 2007はまったく扱ったことがないのでどのようにすればよいかわかりません。 どなたかご教授いただけたらと思います。 Private Sub Workbook_Open() Application.DisplayStatusBar = False 'ステータスバー非表示 'ブックを開く時ページ設定と印刷を非表示 On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("ファイル(&F)") _ .Controls("ページ設定(&U)...").Enabled = False Application.CommandBars("Worksheet Menu Bar").Controls("ファイル(&F)") _ .Controls("印刷(&P)...").Enabled = False Application.CommandBars("Worksheet Menu Bar").Controls("ファイル(&F)") _ .Controls("印刷プレビュー(&V)").Enabled = False Application.CommandBars("Worksheet Menu Bar").Controls("ファイル(&F)") _ .Controls("印刷範囲(&T)").Enabled = False Application.CommandBars("Standard").Enabled = False End Sub

  • マクロでセルのコピー

    シート2のE1の値をシート1のJ3にコピーしよおとマクロで書いたのですがエラーが出てうまくいきません。 Private Sub Worksheet_Deactivate() 　Sheet1.Range("J3").Value = Sheet2.Range("E1") End Sub エラー内容は、実行時エラー'1004' アプリケーション定義またはオブジェクト定義のエラーです。 マクロがあまりくはしくないので修正方法がわかりません。よろしくお願いします。

  • エクセルVBA　保護シート＆フィルタ実行　全シート

    VBA超初心者です。 たくさんのシートのあるエクセルで、 シート保護後もフィルタを使用できるようにＶＢＡを設定したいと思ってます。 （現在エクセル2000を使用してます） ネットで調べてVBAを設定してみました。 しかし下記のようにするとコンパイルエラーになってしまうのですが、 正しい方法を教えていただけると助かります。 Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) 　　Application.CommandBars("Cell").Reset End Sub Private Sub Workbook_Open() 　　With Application.CommandBars("Cell").Controls.Add( _ 　　　　　　　　　　 Type:=msoControlButton, Before:=1, Temporary:=True) 　　　　.Caption = "AutoFilter" 　　　　.OnAction = "ThisWorkbook.filter" 　　End With 　　With Worksheets.Select 　　　　.Unprotect 　　　　.EnableAutoFilter = True 　　　　.Protect UserInterfaceOnly:=True 　　End With End Sub Private Sub filter() 　　On Error Resume Next 　　Selection.AutoFilter End Sub

  • Private Sub Workbook_BeforeSaveについて

    エクセル2000です。 腑に落ちない現象が起きているので質問させてください。 Sheet1にテキストボックス（OLEオブジェクトのTextboxではありません）とボタンが貼ってあり、Visble=Falseで非表示にしています。それを保存する際には表示させたいので、BeforeSaveイベントで Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheet1.TextBoxes("テキスト").Visible = True Sheet1.Buttons("ボタン").Visible = True MsgBox "保存されちゃった！" End Sub と書いてみました。 手動で保存する場合には正しく、テクストボックスやボタンは正しく表示されます。もちろんMsgboxも出ます。 ところが、標準モジュールに書いた以下のコード、 Sub 保存() ActiveWorkbook.Save End Sub を走らせてみても、"保存されちゃった！"と、Msgboxは出ますが、肝心のテクストボックスやボタンが表示されません。 試行錯誤の結果、 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheet1.Shapes("テキスト").Visible = True Sheet1.Shapes("ボタン").Visible = True MsgBox "保存されちゃった！" End Sub と書くと、標準モジュールから、ActiveWorkbook.Saveでも表示されることがわかりました。 どうしてでしょうか？

  • エクセルのPrivate Sub Worksheet_Change

    エクセルのPrivate Sub Worksheet_Change すいません。どうやっても動作しません。 シート１のＢ５のセルに英数字を入力しエンターを押して確定すると シート２のＣ列から同レコードを検出し、その検出された行のＢ～Ｅ列の値を シート１のＨ４、Ｈ５、Ｈ６、Ｊ７に値をかえします。 またＢ５の値はＨ８にも表示されます。 データがあった場合には、シート１のＨ４、Ｈ５、Ｈ６、Ｊ７に値が表示されています。 シート２にデータが無い場合はシート１のＨ４、Ｈ５、Ｈ６、Ｊ７には「＃Ｎ／Ａ」と表示されます。 ここまでは関数と計算式なのでマクロではありません。 ここでフォームで作成した「印刷ボタン」をクリックします。 すると「印刷ボタン」に登録したマクロが起動します。 シート１のセルＧ４～Ｊ１１までが印刷されます。 マクロの内容は シート１のＨ４に「＃Ｎ／Ａ」が表示されている場合はメッセージボックスで 「データがありません」と出ます。印刷は実行されません。 Ｈ４が「＃Ｎ／Ａ」以外の場合は印刷が実行されますがその前に シート３のセルＡ１の数値をプラス１してシート３のＢ２に、セルＡ１の値の先頭にＡを付けて 表示します。（セルＡ１が０００２ならＢ１はＡ０００２） それがシート１のＨ１２に表示されます。（Ｈ１２への表示は計算式で対応） 印刷ボタンを押すごとにシート３のセルＡ１は１づつ繰り上がります。 シート１のＨ１２の値が変わってから印刷がされます。 と同時に シート１のセルＢ５がDELETEされセルＢ５がアクティブになります。 結果シート１のＨ４、Ｈ５、Ｈ６、Ｊ７、Ｈ８は「＃Ｎ／Ａ」が表示されます。 Ｈ１２はクリアされません。 印刷終了後にもセルの値が変化する事になります。 もう一つフォームで作成した「初期化」というボタンがあります。 これをクリックするとマクロが起動して シート１のセルＢ５がDELETEされセルＢ５がアクティブになります。 よってシート１のセルＨ４、Ｈ５、Ｈ６、Ｊ７、Ｈ８も「＃Ｎ／Ａ」に値が変わります。 ここまでは完璧に動作しています。 でこの「印刷ボタン」を押してマクロが起動ではなく セルＢ５に入力しエンターを押したらマクロを起動させたいです。 Private Sub Worksheet_Change(ByVal Target As Range) を利用しましたが動作しません。 シート１のＢ５のセルに入力しエンターを押すと シート１の一部のセルの値が変化。 印刷ボタンを押すと、シート３の一部のセルの値が変化し その後再びシート１のセルの一部が変化し印刷が開始され その後シート１のセルＢ５がDELETEされアクティブに。 それに伴いシート１の一部のセルの値が変化する。 マクロが起動すると、シート１とシート３のセルの値が数回変化します。 このような場合はPrivate Sub Worksheet_Change(ByVal Target As Range)で セルＢ５の入力後エンターで確定しての上記のマクロ起動は無理なのでしょうか？

  • VBAでVBAを削除？

    作業中のブックから一枚のシートを以下のSub 別ファイル保存()プロシージャで別のブックとして保存します。それはうまくいったのですが、この元になるシートには Private Sub Worksheet_SelectionChange(ByVal Target As Range) '途中略 End Sub と Private Sub Worksheet_Deactivate() '途中略 End Sub の二つのVBAが記述してあり、これまで一緒に別のブックに入ってしまいます。 これを防ぐにはどのようにすればよいのでしょうか？ Sub 別ファイル保存() mypath = ThisWorkbook.Path fn = Sheets("Declarations").Range("H15").Value & Format(Date, "yymmdd") ffn = mypath & "\" & fn & ".xls" Sheets("Declarations").Copy ActiveWorkbook.SaveAs Filename:=ffn ActiveWorkbook.Close End Sub

  • コマンドボタンを２回目クリックでエラー発生する

    windows7 Excel2007でマクロ作成の初心者です。以下の点で困ってます。ご指導お願いします。 次のコマンドボタンを実行すると、最初のクリックでは実行できるのに 同じボタンを再度クリックするとエラーがでます。 エラーが出ないようにするにはどうしたらよろしいでしょうか。 ちなみに、ブックはＡ、Ｂ、Ｃの3個です。 Private Sub CommandButton8_Click() ActiveWorkbook.Close SaveChanges:=True On Error Resume Next Workbooks(2).Activate 'Bブックをアクティブに Resume Next Unload Me 評価.Show　 End Sub

  • VBA コマンドバーを消した後に残る空白部分

    お世話になります。 ExcelVBAで、コマンドバーの類を全て消去した後に残る、 空白部分ができる原因が解りません。 この部分をダブルクリックすると、エラーになって強制終了に なってしまいます。 完全に消してしまいたいのですが、この状況になってしまう、 原因と対策を教えてください。 内容は、『システム』という名のシートにある『ロック』ボタンを 押すと、コマンドバーを全て消去して、Sheet1に切り替わるといった 流れです。 ↓モジュール側 ------------------------------------------------------------- Dim myCB As CommandBar Public Sub onCmdBarAttr() DoEvents With Application For Each myCB In Application.CommandBars myCB.Enabled = True Next myCB .CommandBars("Standard").Visible = True .CommandBars("Formatting").Visible = True .CommandBars("Visual Basic").Visible = True .CommandBars("Worksheet Menu Bar").Enabled = True .CommandBars("CELL").Enabled = True ' タスクバーに表示させる .ShowWindowsInTaskbar = True ' 数式バーを表示 .DisplayFormulaBar = True ' ステータスバーを表示 .DisplayStatusBar = True End With End Sub Public Sub offCmdBarAttr() Dim myCB As CommandBar On Error Resume Next For Each myCB In Application.CommandBars myCB.Enabled = False Next myCB On Error GoTo 0 With Application .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars("CELL").Enabled = False ' 数式バーを消去 .DisplayFormulaBar = False ' ステータスバーを消去 .DisplayStatusBar = False ' タスクバーに表示させない .ShowWindowsInTaskbar = False End With End Sub ' シートロック Public Sub protectSheet(ByVal stSheetName As String) Application.ScreenUpdating = False ' 一旦シート保護をかけ直す With Worksheets(stSheetName) .Activate .Unprotect .Protect UserInterfaceOnly:=True ' カーソルの移動範囲を設定する .ScrollArea = "$A$1" ' 左上セルを選択 .Range("H16").Select ' 保護されたセルは選択不可にする .EnableSelection = xlNoRestrictions End With Application.ScreenUpdating = True End Sub ' シートロック解除 Public Sub unprotectSheet(ByVal stSheetName As String) ' 一旦シート保護をかけ直す With Worksheets(stSheetName) .Unprotect ' スクロール範囲を解除する .ScrollArea = "" ' 保護されたセルでも選択可能にする .EnableSelection = xlUnlockedCells End With End Sub Public Sub setBookAttribute(ByVal bFlg As Boolean) With Windows(ThisWorkbook.Name) ' タブを設定 .DisplayWorkbookTabs = bFlg ' スクロールバーを設定 .DisplayHorizontalScrollBar = bFlg .DisplayVerticalScrollBar = bFlg ' グリッドを設定 .DisplayGridlines = bFlg ' 行列数表示を設定 .DisplayHeadings = bFlg End With End Sub ↓シート側 ------------------------------------------------------ Private Sub btnProtect_Click() ' アプリケーション全体の処理 Call Module1.offCmdBarAttr ' シート単位の処理 Call Module1.protectSheet("Sheet1") ' ブック単位の処理 Call setBookAttribute(False) Worksheets("Sheet1").Activate End Sub Private Sub btnUnprotect_Click() Call Module1.onCmdBarAttr Call Module1.unprotectSheet("Sheet1") Call setBookAttribute(True) End Sub 以上、よろしくお願いいたします。

  • VBA Private Sub Worksheet_Calculate()

    「ユーザー定義関数」で計算されるセルがセルB2にある場合、シートモジュールの Private Sub Worksheet_Calculate()で、他の再計算は無視し、そのユーザー定義関数が計算されたことを判断するためにはどのような記述をすればいいのでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する