Two ActiveX Command Buttons, but Only One Works

[reberryjr]

I have 2 ActiveX command button on a worksheet. The cmd_AddNewProj button works as expected, but the cmd_Refresh button does not work. The code works when I hit F5, but when I click the actual button, a 2nd image of the button pops up. I'm not in design mode. Thoughts?

Private Sub cmd_AddNewProj_Click()

frm_NewJob.Show

End Sub
Private Sub cmd_Refresh_Click()

Dim m As Workbook
Dim mS As Worksheet, mI As Worksheet
Dim mILR As Long

Set m = ThisWorkbook
Set mS = m.Sheets("Summaries")
Set mI = m.Sheets("Inventory")

mILR = Sheets("Inventory").Range("D" & Rows.Count).End(xlUp).Row

mS.Activate

With ActiveSheet
    .PivotTables("InvSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A1:R" & mILR))
End With

End Sub

 

[rlv01]

If you set a breakpoint at the first line of code in Sub cmd_Refresh_Click() will it execute to that point if you press the button? It it will, are you able to then single-step (F8) through the code to see what is happening?

 

[RoryA]

ActiveX controls are flaky at best but I suspect that activating a different sheet at the same time as clicking the button doesn't help. Try changing this:

mS.Activate

With ActiveSheet
    .PivotTables("InvSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A1:R" & mILR))
End With

to this:

mS.PivotTables("InvSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A1:R" & mILR))

 

[reberryjr]

ActiveX controls are flaky at best but I suspect that activating a different sheet at the same time as clicking the button doesn't help. Try changing this:

mS.Activate

With ActiveSheet
    .PivotTables("InvSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A1:R" & mILR))
End With

to this:

mS.PivotTables("InvSummary").ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=m.Sheets("Inventory").Range("A1:R" & mILR))

@RoryA simple change, but it appears effective. Many thanks!!