VBA to Add a Control Button that allows for the sheet to be deleted

[SeagullWardy]

I am trying to get some code to work. What I am trying to do is write some code that when a user clicks on a cell it

• Creates a new worksheet based on the value of the selected cell
• Queries a different worksheet in the same workbook and posts the required information in to the new worksheet
• It then adds a button and assigns it a macro
• This macro deletes the current worksheet

The idea is that the user will be looking at summary sheet and can click on a cell to get some more detailed information.

At the moment I have the code working that creates the new sheet, does the query, posts the information, creates the button and assigns the macro to it. The problem is when I click the button I get an error message about the macro not being available in the worksheet. However, if I run the macro manually it does work so I am a little confused.

My code for creating the button and the deleting the sheet are as follows:

Public Sub AddButton()​

Application.ScreenUpdating = False​

ActiveSheet.Buttons.Delete​

ActiveSheet.Buttons.Add(Range("K1").Left, Range("K1").Top, 90, 25).Select​

Selection.Name = "btnDeleteSheet"​

Selection.OnAction = "DeleteCurrentSheet"​

ActiveSheet.Shapes("btnDeleteSheet").Select​

With Selection​

.Characters.Text = "Close Sheet"​

With .Font​

.Name = "Arial"​

.FontStyle = "Bold"​

.Size = 12​

End With​

End With​

ActiveSheet.Cells(1, 1).Select​

End Sub​

​

​

Public Sub DeleteCurrentSheet()​

Application.DisplayAlerts = False​

ActiveWindow.SelectedSheets.Delete​

Application.DisplayAlerts = True​

​

End Sub​

Anyone able to identify where I am going wrong?

Thanks

 

[JLGWhiz]

Button code must be in the sheet code module of the host sheet and uses 'Private' as the Sub type.
You can put the delete code in a public code module and call it from the button code.

Private Sub CommandButton1_Click()
delSheet
End Sub

The above goes in the sheet module

Sub delSheet()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

The above code goes in module1.

 

[SeagullWardy]

Button code must be in the sheet code module of the host sheet and uses 'Private' as the Sub type.
You can put the delete code in a public code module and call it from the button code.

Private Sub CommandButton1_Click()
delSheet
End Sub

The above goes in the sheet module

Sub delSheet()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

The above code goes in module1.

Thanks for the answer JLGWhiz.

If I am understanding you correctly what you are saying is that the first code snippet goes against the sheet with the button on and the second code snippet goes against the workbook or somewhere else that it is public.

I am very new to VBA though and not quite sure how to achieve via code what you have said. The sheet that I am putting the button on is created by code and the button is added by code. So I guess I need a way to add the required code against that sheet.

Sorry if it is a silly question.

 

[JLGWhiz]

Yes, the Active-X button code must be entered in the sheet code module. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. That displays the vbeditor with a code pane for that sheet. I don't know what type of button you are putting on the sheet, but I assume it will be active=X. .If your button is Forms Contol, then you do not need the sheet code to use the delSheet macro. With a Form Control button, you can assign the delSheet macro directly to the button by right clicking o the button, then click 'Attach Macro'. or 'Assign Macro', I forget the wording. The delSheet code goes in Module1. To access Module1, press Alt + F11 while the Excel sheet is viible, or double click on the name Module1 in the Project box at upper left in the vb editor.