シートをVBAで削除しようとしているが良い指定方法が分からない

お世話になっております。

VBAは初心者ゆえ基本的な質問失礼いいたします。

前任者は退職しているため誰にも聞けないので相談にのっていだけないでしょうか？

今excel2016で

ID　シート名　　ボタン
================================
1	あああ	「削除」
2	いいい	「削除」
3	ううう	「削除」
「行を追加」
================================
[概要][あああ][いいい][ううう]←シート名

との表があり　↑の「削除」ボタンを押すとその行と
その指定の文字列のシートを削除するVBAを組もうと思っております。

その行は前任者が消しているのでシートの方も楽勝だと思ったのですが
スパゲッティソースで訳がわからないので相談させてください。

↑のIDを前任者が使っていれば良かったのですが
「行を追加」を押しても内部で利用しているIDは分からないのです……
（デバッグで表示させたが不明で下記関数のリターン値を持ってきている）

Public Function AddRow(Sh As Object) As Long

    Dim LastRo As Long
    Dim TargetRo As Long
    Dim Tpos As Double
    Dim RefNum As Long
    
    With Sh
        LastRo = Module1.GetLastRow(Sh, .Rows.Count, 2)
        TargetRo = LastRo + 1
        AddRow = TargetRo
        .Rows(TargetRo).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Rows(TargetRo).RowHeight = 20
        '整理番号
        RefNum = TargetRo - 5
        'グローバル変数;記入対象の行
        CiteRo = TargetRo
        .Cells(TargetRo, 2).Value = RefNum
        '「工事名」の記入
        .Cells(TargetRo, 5).Value = "未記入"
    End With
    Tpos = (RefNum - 1) * 19.8 + 90.6
    'Tpos = (RefNum - 1) * 19.584 + 96.768
    '罫線引き
    Call DrawRuledLines(Sh, TargetRo, 2, 13)
    '[記入]と[削除]ボタン作成
    Call MakeCmdButton(Sh, 73.8, Tpos, 35.4, 19.8, RefNum)
    'Call MakeCmdButton(Sh, 70.848, Tpos, 35.136, 20.1, RefNum)
    '[記入]ボタンのプロシージャ作成
    Call ShowProcInsert(Sh.CodeName, RefNum, TargetRo)
    '[削除]ボタンのプロシージャ作成
    Call DelButtonProcInsert(Sh.CodeName, RefNum)
    '[適用追加]ボタンの Enable=False/True
    Call CiteAddButtonEnabledChange(Sh)
    
End Function

なんかVBAだと特殊は返り値があるのでしょうか？

もう本当にスパゲッティソースで訳がわからないのですが
とっかかりが掴めないので質問失礼します。

上記シート【いいい】を削除する方法で良い方法は無いでしょうか？？？

自分が混乱しているのは自覚しているのですが何か案があればと書き込み失礼いいたします。

【追加】ココから================================

Private Sub DelButtonProcInsert(ShCodeName As String, Num As Long)
'[削除]ボタンのプロシージャ作成

    Dim DelProcName As String
    Dim Cnt As Long
    
    DelProcName = "Del" & Num & "_Click"

    With ThisWorkbook.VBProject.VBComponents(ShCodeName).CodeModule
    
        On Error Resume Next
        '同名のプロシージャがなければエラー発生、あればCnt>0
        Cnt = .ProcBodyLine(DelProcName, 0)
        On Error GoTo 0
        If Cnt > 0 Then Exit Sub '←同名のプロシージャがなければ Cnt=0
        
        .insertlines 1, "Private Sub " & DelProcName & "()"
        .insertlines 2, "'Del" & Num & "Button"
        .insertlines 3, vbTab & "Call ThisWorkbook.DelButtonClick(Activesheet," & Num & ")"
'        .insertlines 3, vbTab & "If Not Application.EnableEvents Then Exit Sub"
'        .insertlines 4, vbTab & "Call Module3.DelRow(Activesheet," & Num & ")"
        .insertlines 4, "End Sub"
    End With

End Sub

Public Function AddRow(Sh As Object) As Long

    Dim LastRo As Long
    Dim TargetRo As Long
    Dim Tpos As Double
    Dim RefNum As Long
    
    With Sh
        LastRo = Module1.GetLastRow(Sh, .Rows.Count, 2)
        TargetRo = LastRo + 1
        AddRow = TargetRo
        .Rows(TargetRo).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Rows(TargetRo).RowHeight = 20
        '整理番号
        RefNum = TargetRo - 5
        'グローバル変数;記入対象の行
        CiteRo = TargetRo
        .Cells(TargetRo, 2).Value = RefNum
        '「工事名」の記入
        .Cells(TargetRo, 5).Value = "未記入"
    End With
    Tpos = (RefNum - 1) * 19.8 + 90.6
    'Tpos = (RefNum - 1) * 19.584 + 96.768
    '罫線引き
    Call DrawRuledLines(Sh, TargetRo, 2, 13)
    '[記入]と[削除]ボタン作成
    Call MakeCmdButton(Sh, 73.8, Tpos, 35.4, 19.8, RefNum)
    'Call MakeCmdButton(Sh, 70.848, Tpos, 35.136, 20.1, RefNum)
    '[記入]ボタンのプロシージャ作成
    Call ShowProcInsert(Sh.CodeName, RefNum, TargetRo)
    '[削除]ボタンのプロシージャ作成
    Call DelButtonProcInsert(Sh.CodeName, RefNum)
    '[適用現場を追加]ボタンの Enable=False/True
    Call CiteAddButtonEnabledChange(Sh)
    
End Function

torisan3

2021/04/07 08:36

私も良く分からないなりで言いますが、提示のコードって『行を追加』のコードではないですか？他にコードはありませんか？アドインはありませんか？

ma2hiro

2021/04/07 08:52

他にコードはあります……丸投げになりそうだったので必要かな？と思われる部分を表記しました……

jinoji

2021/04/07 11:57

ID　シート名　　ボタン ================================ 1 あああ「削除」 2 いいい「削除」 3 ううう「削除」「行を追加」 ================================ というところまでが表で、それが[概要]シートに書かれている？ Public Function AddRow のコードは、どのモジュールに書かれている？

ma2hiro

2021/04/08 00:46

jinoji様コメントありがとうございます。仰る通りそれが【概要】シートに記載されています。 Public Function AddRow のコードは標準モジュールのModule3に記載しており質問文に追記しました。そのAddRowにて内部で使用しているID？が分かる気がします…… 前任者は何かドキュメントを残して欲しかったです……

jinoji

2021/04/08 01:04

「行を追加」ボタンを押すと、どんな処理がどんな順番で動くのですか。（ThisWorkbook.AddButtonClickとかでしょうか？？）なお、Module3.AddRowファンクションは、概要シートの表の最下行に新規行を追加し、そこにボタンを配置し、そのボタンで呼び出されるVBAコードを作成する、という流れに見えます。最下行に追加するので、もし実行時にあるIDが１～３だったら、追加する行のIDは４になるのだと思います。今回、削除ボタンを押したときの処理を作るのであれば、どの行の削除ボタンを押したか、によってIDが決まることになるはずで、 AddRowとは少し考え方が異なるのではないでしょうか。

行動規範の内容に同意します

回答2件

ベストアンサー

当て推量で書くと、

VBA
1Function DelRow(Sh As Object, Num As Long)
2    With Sh
3        Dim ShName As String
4        ShName = .Cells(WorksheetFunction.Match(Num, .Columns(1), False), 2).Value
5        Sheets(ShName).Delete
6    End With
7End Function

みたいなことをModule3に書けば、【いいい】のシートを削除するだけならできそう。

でも、もし【いいい】のシートを削除するだけじゃなくて

1 あああ「削除」
2 いいい「削除」
3 ううう「削除」
「行を追加」
[概要][あああ][いいい][ううう]

↓

1 あああ「削除」
2 ううう「削除」
「行を追加」
[概要][あああ][ううう]

のようにするなら、もう少し腰を据えて考えないといけなさそうです。

＜追記＞
想像力を目一杯働かせてみると、
いまの表が書かれているシートのモジュールあたりに、

VBA
1Private Sub Del1_Click()
2'Del1Button"
3	Call ThisWorkbook.DelButtonClick(Activesheet, 1)
4        If Not Application.EnableEvents Then Exit Sub
5        Call Module3.DelRow(Activesheet,1)
6End Sub
7
8Private Sub Del2_Click()
9'Del2Button"
10	Call ThisWorkbook.DelButtonClick(Activesheet, 2)
11        If Not Application.EnableEvents Then Exit Sub
12        Call Module3.DelRow(Activesheet,2)
13End Sub
14
15Private Sub Del3_Click()
16'Del3Button"
17	Call ThisWorkbook.DelButtonClick(Activesheet, 3)
18        If Not Application.EnableEvents Then Exit Sub
19        Call Module3.DelRow(Activesheet,3)
20End Sub

のようなコードが表の行数分書かれていて、
たとえば

2 いいい「削除」

の削除ボタン（Del2Button）を押すと、

Del2_Clickの処理が呼び出される、という仕組み？？？

ThisWorkbook.DelButtonClick の方は、
第1引数に表のあるSheetオブジェクト、第2引数に 2 が渡されてきて、
表からID=2に対応するシート名【いいい】を取得して、
Sheets("いいい").Delete をする処理になっているのかな？？？
（追記前に最初に書いた処理）

一方、Module3.DelRow の方は、
表の2行目を消したり、削除ボタンを消したり、
VBAコードのDel2_Clickの処理を消したりするのかな？？？

投稿2021/04/07 11:16

編集2021/04/07 12:28

jinoji

総合スコア4585

jinoji

2021/04/07 11:39

あ、このコードは ThisWorkbook.DelButtonClick の方の内容かも。

ma2hiro

2021/04/08 07:08

jinoji様本当にコメントありがとうございます。仰るように　ThisWorkbook.DelButtonClick　は以下のようになっています…… ``` Public Sub DelButtonClick(Sh As Object, CiteRo As Long) If Not Application.EnableEvents Then Exit Sub 'ここに入れるとDelRowで落ちるのでModule3.DelRow(Sh, CiteRo)内でシートを削除するか…… ' Call ActiveWorkbook.Sheets(CiteRo).Delete 'ちなみに　　　　　　　　　　↑ってSheetの番号じゃない気がします…… Call Module3.DelRow(Sh, CiteRo) End Sub ``` ちなみに上記で呼び出しているDelRowは ``` Public Sub DelRow(Sh As Object, RefNum As Long) '[削除]による行削除 Dim Tpos As Double Dim LastRo As Long Dim maxRefNum As Long Dim i As Long '[削除]ボタンのプロシージャが削除されるのでここに記述する Application.EnableEvents = False Application.ScreenUpdating = False Sh.Unprotect Set Sh = Sheets("概要") 'ma2addkokokara…… Debug.Print "\nスタートRefNum>>>"; RefNum; "dou?\n" Call Sheet1.Sample3 'ma2addkokomade…… '[記入][削除]ボタンの削除 Call DeleteButton(Sh, RefNum) 'MsgBox "[記入][削除]ボタンの削除-終了 " & Sh.Name '「概要」中の対応プロシージャを削除 Call DeleteProc(Sh, RefNum) 'MsgBox "「概要」中の対応プロシージャを削除-終了 " & Sh.Name '当該行の削除 Sh.Rows(RefNum + 5).Delete Shift:=xlUp 'MsgBox "当該行の削除除-終了 " & Sh.Name '上行の罫線引き直し Call DrawRuledLines(Sh, RefNum + 4, 2, 13) 'MsgBox "上行の罫線引き直し-終了 " & Sh.Name '「整理番号」の付け直し LastRo = Module1.GetLastRow(Sh, Sh.Rows.Count, 2) For i = 6 To LastRo Sh.Cells(i, 2).Value = i - 5 Next 'MsgBox "「整理番号」の付け直し-終了 " & Sh.Name '[記入]と[削除]ボタン作成 maxRefNum = LastRo - 5 For i = RefNum To maxRefNum Tpos = (i - 1) * 19.8 + 90.6 'Tpos = (i - 1) * 19.584 + 96.768 Call MakeCmdButton(Sh, 73.8, Tpos, 35.4, 19.8, i) 'Call MakeCmdButton(Sh, 70.848, Tpos, 35.136, 20.1, i) '19.584 '[記入]ボタンのプロシージャ作成 Call ShowProcInsert(Sh.CodeName, i, i + 5) '[削除]ボタンのプロシージャ作成 Call DelButtonProcInsert(Sh.CodeName, i) Next 'ma2addkokokara Debug.Print "\n並び替え前\n" Call Sheet1.Sample3 'ma2addkokomade '各シートのCiteRo宣言（=概要シートでの行番号）の書き換えma2addってやっているからココでCiteRoの宣言をしているっぽい Call ExplicitRewrite(RefNum) Call CiteAddButtonEnabledChange(Sh) 'ma2addkokokara Debug.Print "\n並び替え後\n" Call Sheet1.Sample3 'ma2addkokomade Sh.Protect Application.ScreenUpdating = True Application.EnableEvents = True 'ma2addkokokara Debug.Print "\nシート消してみる……RefNum>>>"; RefNum; "dou?\n" Debug.Print "\nプロテクト後ココから\n" Call Sheet1.Sample3 Debug.Print "\nプロテクト後ココまで\n" 'ma2addkokomade End Sub ``` 私のIDから分かるように 'ma2addkokokara というのは私が追加したのですがなんか引数で渡ってきたIDと【概要】のIDが違っているのです…… なんかIDと内部のIDが一緒になるように整理してみようかと思います。光が見えてきました。ありがとうございます。

ma2hiro

2021/04/09 00:23

ありがとうございます。ご助力頂きソースを落ち着いて読んで改善点が見つかりました。 ``` Public Sub DelButtonClick(Sh As Object, CiteRo As Long) If Not Application.EnableEvents Then Exit Sub 'ma2add ここに入れるとDelRowで落ちるのでModule3.DelRow(Sh, CiteRo)内でシートを削除するかな…… ' Call ActiveWorkbook.Sheets(CiteRo).Delete 'ma2addココから Debug.Print "DelButtonClicのCiteRo>>>"; CiteRo; "<<<dou?\n" Dim ma2test As String Dim tmp As Integer tmp = CiteRo + 5 ma2test = Cells(6, 5).Value ma2test = Cells(tmp, 5).Value Debug.Print "なのでシート名は>>>"; ma2test; "<<<dou?\n" 'ma2addココまで Call Module3.DelRow(Sh, CiteRo) 'ma2addココまで 'ma2add ここに入れるとDelRowで落ちるのでModule3.DelRow(Sh, CiteRo)内でシートを削除するかな…… ActiveWorkbook.Sheets(ma2test).Delete '←←←←←←←←←←←←←←←←でシート削除 End Sub ``` 本当にありがとうございましたっm(_ _)m 大変助かりましたっ

行動規範の内容に同意します

削除なら　Sheets("いいい").Delete　で出来ると思います。
Addrow関数のことはとりあえず置いて、DelButtonProcInsert関数を調べた方がよいかもしれません。
あとすいません、スパゲッティソースじゃなくてスパゲッティコードかと。

投稿2021/04/07 08:33

mdj

総合スコア220

ma2hiro

2021/04/07 08:57 編集

mdj様コメントありがとうございます。仰るとおりDelButtonProcInsertの部分を追記しました。ココからシート名が分かる気がしますので頭をスッキリさせてみてみます。本当にありがとうございました。スパゲッティソースだと　かける方になってしまいますね…… 頭がパニクっていて失礼しました。m(_ _)m

行動規範の内容に同意します

あなたの回答