Formulas not refreshing second time button is clicked.

A button is clicked to refresh data.  It works the 1st time but not the second.  Is there anyone that can look at this code and tell me why it would refresh one time and not the other?  It seems to refresh when the sheet is first open and initiated but not the second time.

Dim iD As Integer, iK As Integer
Dim EKN As String

EKNFormula = False
Application.EnableEvents = True
iK = 1
Sheet1.Activate
If Sheet1.CommandButton3.Caption = "Refresh" Then
    iD = Range("J6").End(xlDown).Row
    EKN = "=RData(J6:J" & iD & ",K4,,," & iQt & "CH:Fd" & iQt & ",K5)"
    Sheet1.Range("J4").Formula = EKN
    iD = iRefresh
    Sheet1.CommandButton3.Caption = "Lock Formulas"
   
   
Else
    Sheet1.CommandButton3.Caption = "Refresh"
   End If

EKNFormula = True

End Function
Public Function iRefresh() As Integer

iRefresh = 0
While iRefresh < 55
    On Error Resume Next
     iRefresh = iRefresh + 1
    DoEvents
Wend
ActiveSheet.Calculate
DoEvents
iRefresh = 1

End Function
leezacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
not sure.

the Irefresh simply does a DoEvents 55 times.  Is there a reason for 55 times ?
if you are expecting an externla update or similar, you would be better waiting a few seconds.

have you turned sheet update off somewhere after this code, then dont turn it on again ?
that would explain why the initial calc works, and the first 'refresh' does.
put
Application.ScreenUpdating = True

Open in new window

instead of
iRefresh = 1

Open in new window

as the later is not useful anyway.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leezacAuthor Commented:
Thanks - that is helpful - will try
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.