Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


10324 / 13646 ツリー ←次へ | 前へ→

【22221】シート保存時のvbaコード削除 miyake 05/2/12(土) 11:00 質問[未読]
【22223】Re:シート保存時のvbaコード削除 かみちゃん 05/2/12(土) 11:19 回答[未読]
【22224】Re:シート保存時のvbaコード削除 IROC 05/2/12(土) 11:43 回答[未読]
【22438】Re:シート保存時のvbaコード削除 miyake 05/2/20(日) 22:29 質問[未読]
【22441】Re:シート保存時のvbaコード削除 ichinose 05/2/21(月) 7:45 発言[未読]
【22449】Re:シート保存時のvbaコード削除 Jaka 05/2/21(月) 11:10 回答[未読]
【22469】Re:シート保存時のvbaコード削除 Jaka 05/2/22(火) 9:13 発言[未読]

【22221】シート保存時のvbaコード削除
質問  miyake  - 05/2/12(土) 11:00 -

引用なし
パスワード
   いつもお世話になっております。複数あるシートの中で、あるシートだけ保存する場合、そのシートを新規ブックにコピーし保存するやり方があると思います。私はシートにコマンドボタンを配置し、ボタンを押すと、そのシートがコピーされ、不要なコマンドボタンを削除し保存できるように作りましたが、ここで問題が発生しました。保存したそのシートを開く際、マクロ有効・無効のダイアログが出てしまいます。コピーされたシートには、必要なくなったコード(コマンドボタンのイベントプロシージャなど)が残ってしまいます。シートを保存する際、このコードを消し去る処理はできるのでしょうか。あったら教示してください。

【22223】Re:シート保存時のvbaコード削除
回答  かみちゃん  - 05/2/12(土) 11:19 -

引用なし
パスワード
   こんにちは。かみちゃん です。

> コピーされたシートには、必要なくなったコード(コマンドボタンのイベントプ
> ロシージャなど)が残ってしまいます。シートを保存する際、このコードを消し
> 去る処理はできるのでしょうか。あったら教示してください。

過去ログを参照してください。
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=21998;id=excel

【22224】Re:シート保存時のvbaコード削除
回答  IROC  - 05/2/12(土) 11:43 -

引用なし
パスワード
   シートをコピーせずにセルをコピーしてはいかがですか?

【22438】Re:シート保存時のvbaコード削除
質問  miyake  - 05/2/20(日) 22:29 -

引用なし
パスワード
   いつもお世話になっております。

>過去ログを参照してください。
>http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=21998;id=excel

上記を参考にして作ったところおおむねうまくいきましたが、失敗することがあります。

〜プログラムの概要〜
 シート( Sheet1 )にコマンドボタン( cmd選択シート保存 )が貼り付けられ、そのボタンを押すと、そのシートのみ新規ブックにコピーして保存するプログラムです。
 コマンドボタンを押すと、標準モジュールの選択シート保存プロシージャを呼び出し、Sheet1を新規ブックにコピーし、コピー先シートに残っているコード( Private Sub cmd選択シート保存_Click() )とコマンドボタンを削除するもの。

*うまくいくケース
 コピー元のブックのVBAプロジェクトを一度開いた後に実行すると、コピー先のシートのコードがきれいに消されている。
(コードは消されているが、ファイルを開くときに、どういう訳かマクロを有効にするか否かのメッセージが出てきてしまう)

*うまくいかないケース
 コピー元のブックのVBAプロジェクトを一度も開かずに実行すると、コピー先のシートのコードが残ってしまう。(コードが消されない)

対処方法があればご教示ください。
コードは下記のとおりです。


〜Sheet1のイベントプロシージャ〜

Private Sub cmd選択シート保存_Click()

Module1.選択シート保存

End Sub

〜module1の選択シート保存プロシージャ〜

Sub 選択シート保存()

Dim ShName As String
ShName = ActiveSheet.Name

'アクティブシートを新規ブックにコピー
ActiveSheet.Copy

'コピー先のシートモジュールを削除
With ActiveWorkbook.VBProject.VBComponents.Item(2).CodeModule
     .DeleteLines 1, .CountOfLines
End With

'コピー先シートのオブジェクトを削除
Dim cmdShape As Shape

For Each cmdShape In ActiveSheet.Shapes
  'コマンドボタン
  If Left(cmdShape.Name, 3) = "cmd" Then
    cmdShape.Select
    Selection.Delete
  'テキストボックス
  ElseIf Left(cmdShape.Name, 3) = "txt" Then
    cmdShape.Select
    Selection.Delete
  'ラベル
  ElseIf Left(cmdShape.Name, 3) = "lbl" Then
    cmdShape.Select
    Selection.Delete
  'コンボボックス
  ElseIf Left(cmdShape.Name, 3) = "cmb" Then
    cmdShape.Select
    Selection.Delete
    
  End If

Next cmdShape

'保存するかどうかの確認
DlgAnswer = Application.Dialogs(xlDialogSaveAs).Show(ShName)

If DlgAnswer = True Then
  ActiveWorkbook.Close
Else
  ActiveWorkbook.Saved = True
  ActiveWorkbook.Close
End If

End Sub

【22441】Re:シート保存時のvbaコード削除
発言  ichinose  - 05/2/21(月) 7:45 -

引用なし
パスワード
   ▼miyake さん:
おはようございます。再投稿です。
>いつもお世話になっております。
>
>>過去ログを参照してください。
>>http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=21998;id=excel
>
>上記を参考にして作ったところおおむねうまくいきましたが、失敗することがあります。
>
>〜プログラムの概要〜
> シート( Sheet1 )にコマンドボタン( cmd選択シート保存 )が貼り付けられ、そのボタンを押すと、そのシートのみ新規ブックにコピーして保存するプログラムです。
> コマンドボタンを押すと、標準モジュールの選択シート保存プロシージャを呼び出し、Sheet1を新規ブックにコピーし、コピー先シートに残っているコード( Private Sub cmd選択シート保存_Click() )とコマンドボタンを削除するもの。
>
>*うまくいくケース
> コピー元のブックのVBAプロジェクトを一度開いた後に実行すると、コピー先のシートのコードがきれいに消されている。
>(コードは消されているが、ファイルを開くときに、どういう訳かマクロを有効にするか否かのメッセージが出てきてしまう)
>
>*うまくいかないケース
> コピー元のブックのVBAプロジェクトを一度も開かずに実行すると、コピー先のシートのコードが残ってしまう。(コードが消されない)
>
>対処方法があればご教示ください。
>コードは下記のとおりです。
>
>
>〜Sheet1のイベントプロシージャ〜
>
>Private Sub cmd選択シート保存_Click()
>
>Module1.選択シート保存
>
>End Sub
'======================================================
Sub 本当に保存()
'保存するかどうかの確認
  Dim ShName As String
  ShName = Me.Name
  DlgAnswer = Application.Dialogs(xlDialogSaveAs).Show(ShName)
  DoEvents
  If DlgAnswer = True Then
   ActiveWorkbook.Close
  Else
   ActiveWorkbook.Saved = True
   ActiveWorkbook.Close
  End If
End Sub


>
>〜module1の選択シート保存プロシージャ〜
>
>Sub 選択シート保存()
>
>Dim ShName As String
>ShName = ActiveSheet.Name
>
>'アクティブシートを新規ブックにコピー
>ActiveSheet.Copy
>
>'コピー先のシートモジュールを削除
With ActiveWorkbook.VBProject.VBComponents.Item(ShName).CodeModule
'   シート名を変数に入れたのですからここで活用しましょう!!
>     .DeleteLines 1, .CountOfLines
>End With
>
>'コピー先シートのオブジェクトを削除
>Dim cmdShape As Shape
>
>For Each cmdShape In ActiveSheet.Shapes
>  'コマンドボタン
>  If Left(cmdShape.Name, 3) = "cmd" Then
>    cmdShape.Select
>    Selection.Delete
>  'テキストボックス
>  ElseIf Left(cmdShape.Name, 3) = "txt" Then
>    cmdShape.Select
>    Selection.Delete
>  'ラベル
>  ElseIf Left(cmdShape.Name, 3) = "lbl" Then
>    cmdShape.Select
>    Selection.Delete
>  'コンボボックス
>  ElseIf Left(cmdShape.Name, 3) = "cmb" Then
>    cmdShape.Select
>    Selection.Delete
>    
>  End If
>
>Next cmdShape
>
Application.OnTime Now(), "'" & ThisWorkbook.Name & "'!sheet1.本当に保存"
>
>End Sub

これで確認してみて下さい。

ですが・・・・。


このコードをご自分以外の他の方にも使ってもらうものだとしたら
いくつかの問題があります。

・VbprojectがExcel2002以降では標準の設定ではアクセスできないこと。
 Excel2000等で可能でもいつかは、Officeのバージョンアップをしなければ
 なりませんよね?その時に大きくコードを変更を強いられる事になるかもしれませんよ

・「コントロールツールボックス」コマンドボタン等を削除するコードがありますが、
 このツールバーのコントロールの動的な作成・削除には、問題がありそうです。
 「マクロを有効にするか否か」のメッセージはこれが原因かと思われます。
 ここに同様のご質問が以前にもこのサイトでありました。

http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=14064;id=excel

↑ここでさらにリンクしていますから、そこもご覧になって下さい。

動的な作成・削除には、他にも問題があるかもしれません。
それを洗い出す問題提起なら、それはそれで意義はありますけどね!!

IROCさんが
>シートをコピーせずにセルをコピーしてはいかがですか?

と言っていますよね?
>Private Sub cmd選択シート保存_Click()
   Set bk = Workbooks.Add
   Cells.Copy bk.Worksheets(1).Range("a1")
>End Sub

とすれば、コードもコントロールも含まないコピーが可能です。

Vbprojectは、サイトの検索で取得した知識として他に活用場所もあるかと
思います。

検討してみて下さい。

【22449】Re:シート保存時のvbaコード削除
回答  Jaka  - 05/2/21(月) 11:10 -

引用なし
パスワード
   こんにちは。

>(コードは消されているが、ファイルを開くときに、どういう訳かマクロを有効にするか否かのメッセージが出てきてしまう)
この現象、私も前から気になってて色々試した結果、下みたいに面倒な事をすると何とかでなくなりました。
シートの別名保存ではなく、ブックの複製ですけど....。
シート1だったと思いますが、ボタン等も消してます。

Sub 別名保存後マクロ削除()
  Dim Shp As Shape
  pic = ThisWorkbook.Path & "\"
  myFileName = "new.xls"
  ThisWorkbook.SaveCopyAs Filename:=pic & myFileName
  
  'Workbooks.Open Filename:=pic & myFileName
  Workbooks.Open pic & myFileName
  With Workbooks(myFileName).VBProject
    For Each VBC In .VBComponents
      Select Case VBC.Type
      Case 100
        With VBC.CodeModule
          .deletelines 1, .Countoflines
        End With
      Case Else
          .VBComponents.Remove VBC
      End Select
    Next
  End With
  
  With Workbooks(myFileName).Worksheets("Sheet1")
     'アクティブXコントロール全部
     For i = 1 To .OLEObjects.Count
      .OLEObjects.Item(i).Delete
     Next
     'フォームボタン
     '.Shapes("Button 1").Delete
     '.Shapes("Button 2").Delete
     'マクロ登録されているファームボタン等削除。
     For Each Shp In .Shapes
       If Shp.OnAction <> "" Then
        Shp.Delete
       End If
     Next
  End With
  Workbooks(myFileName).Save
  Workbooks(myFileName).Close
  
  DoEvents
  Workbooks.Open (pic & myFileName)
  Workbooks(myFileName).Save
  Workbooks(myFileName).Close '(True)
  
  'ThisWorkbook.Close (False)
End Sub

【22469】Re:シート保存時のvbaコード削除
発言  Jaka  - 05/2/22(火) 9:13 -

引用なし
パスワード
   すみません。
こんな感じに直してください。

With ThisWorkbook.Worksheets("Sheet1")
  'アクティブXコントロール全部
  For i = 1 To .OLEObjects.Count
    .OLEObjects.Item(i).Delete
  Next
End With

  ↓

Dim acob As Object
With ThisWorkbook.Worksheets("Sheet1")
  'アクティブXコントロール全部
  For Each acob In .OLEObjects
    acob.Delete
  Next
End With

10324 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free