Assigned Command Button is getting deleted when Macro Runs

[fareedexcel]

Hi,

I have 2 macro, when I run the code using Alt + F11, it is working fine. I try to assign the code using command button and when I click the button, the button gets deleted and the code result populates in the column A.

Is there any way to keep the button enabled, so that every time i dont want to assign control.

These are my two codes where I would like to keep in the Tab Summary Page with command buttons,

CODE 1 :

Sub ListWorkSheetNamesNewWs()
Dim checklist As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
xTitleId = "Tab Summary"
Application.Sheets(xTitleId).Delete
Application.Sheets.Add Application.Sheets(1)
Set checklist = Application.ActiveSheet
checklist.Name = xTitleId
For I = 2 To Application.Sheets.Count
checklist.Range("A" & (I - 1)) = Application.Sheets(I).Name
Next
Application.DisplayAlerts = True
End Sub

CODE 2:

Public Sub DeleteCompletelyBlankRows()

Dim WS As Worksheet
Dim R As Long

On Error GoTo EndMacro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each WS In Worksheets
With WS.UsedRange
For R = .Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(.Rows(R).EntireRow) = 0 Then
.Rows(R).EntireRow.Delete
End If
Next R
End With
Next WS

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Logit]

I believe, in your second macro (Code 2), with this portion :

For Each WS In Worksheets
With WS.UsedRange
For R = .Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(.Rows(R).EntireRow) = 0 Then
.Rows(R).EntireRow.Delete
End If

Edit: For R = .Rows.Count To 1 Step -1

To be : For R = .Rows.Count To 2 Step -1

Move your data down one row to begin on Row #2. Place your command buttons
anywhere in Row #1.

 

[fareedexcel]

Move your data down one row to begin on Row #2. Place your command buttons
anywhere in Row #1.

As per the Code 1, data is populating starting from Row 1 and the command button is getting deleted after macro runs

 

[Logit]

I presumed if I ran the code here (as you initially presented it in Post #1) I would experience the same error you are seeing there.

Just put together the two macros and ran both. I am unable to re-create the disappearance of the command buttons here. I did not change your code.

There is something else going on with your project .. on your end.

????

 

[fareedexcel]

Could you please guess or figure out where my code is going wrong. My intention is to keep both the command buttons in the Sheet and whenever i run the command the button should not get deleted

 

[Logit]

If you can post your workbook here for review ... would be best way. That way I will be able to determine if the error occurs here with your workbook that you have there.

Using just the code you posted above, I am unable to reproduce the error here.

If you post your workbook, remove any confidential data from the sheet/s but do not change or delete any code.

 

[Logit]

It is late here now. Will review in the morning.