Beforedoubleclick code only works once

[S Oberlander]

I have the below code to work on a pivot table, the code works fine the first time I use it in a workbook.
When I use it a second time the OnAction is not getting assigned properly and I get this popup when I try to use the command button.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    'On Error GoTo ex
    Selection.ShowDetail = True
    Range("K:K,M:N,E:E,O:Y").EntireColumn.Hidden = True
    ct = Range("F2")
    If Evaluate("isref('" & ct & "'!A1)") Then
      Application.DisplayAlerts = False
      Sheets(ct).Delete
      Application.DisplayAlerts = True
    End If
    ActiveSheet.Name = ct
    ActiveSheet.Buttons.Add(937.5, 23.25, 114.75, 27).Select
    Selection.OnAction = "R:\Macro Data\RP Macro Wrkbk!refreshT"
    Selection.Characters.Text = "Refresh Table"
    Range("A1").Select
ex:
End Sub

 

[rlv01]

1. Why are you adding the button more than once?
2. On the sheet 'ct' are there more buttons that just the one you are adding?

 

[rlv01]

Since your code presumably adds a new button each time you double click, then perhaps you need to make sure you are not accumulating buttons.

ActiveSheet.Name = ct
With ActiveSheet
    'clear out old buttons before adding a new one
    Dim WSButton As Button
    For Each WSButton In .Buttons
        If WSButton.Caption = "Refresh Table" Then
            WSButton.Delete
        End If
    Next WSButton
  
    'add new button
    Set WSButton = .Buttons.Add(937.5, 23.25, 114.75, 27)
    With WSButton
        .OnAction = "R:\Macro Data\RP Macro Wrkbk!refreshT"
        .Characters.Text = "Refresh Table"
    End With
End With
Range("A1").Select

 

[S Oberlander]

Since your code presumably adds a new button each time you double click, then perhaps you need to make sure you are not accumulating buttons.

there is only one button on each sheet, and the old sheet gets deleted, including the button on it, before a new button gets added to the current sheet

 

[S Oberlander]

did you read the path name in the popup and compare it to the pathname in the code?
it is getting distorted somehow and that is why it isn't working

 

[S Oberlander]

I did some more work on this code
see Assign Macro to a button only works first time
for a more clear description of the issue on hand.

 

[rlv01]

did you read the path name in the popup and compare it to the pathname in the code?
it is getting distorted somehow and that is why it isn't working

I cut and pasted the path name from your original post. Did you try the code I posted, and if so what error messages, if any, did you get?

 

[S Oberlander]

I cut and pasted the path name from your original post. Did you try the code I posted, and if so what error messages, if any, did you get?

Same result as before.

The new button being added is going onto a sheet that the macro just created! there are no buttons in the sheet you are trying to delete them from.
Read my other post that is a more accurate description of the problem but was closed as a duplicate
Assign Macro to a button only works first time

 

[rlv01]

Your original code in post#1 above creates a button on sheet "ct" (which is the activesheet). The code I posted does the same. Not sure why it would add the button to a new sheet unless you have changed your code

 

[S Oberlander]

Your original code in post#1 above creates a button on sheet "ct" (which is the activesheet). The code I posted does the same. Not sure why it would add the button to a new sheet unless you have changed your code

the line Selection.ShowDetail = True creates a new sheet and activates it,
the code is then running on this sheet that started out as "sheet3" or "sheet4" etc... and renames it according to the value in F2
if such a sheet name already exists it first deletes the old sheet in order to avoid duplicate issues