Run a function when a specific worksheet is opened.

[Phil Payne]

Hello all, its been a while.

I have a function that counts the number of conditionally formatted cells in a row.

When there's been little or no activity on the worksheet the cells holding the count results can be blank or show an error message when next you open the worksheet.

I can double click on a problem cell and then hit return and the function runs ok but I need an automated/semi automated solution.

Can anyone help please?

 

[Anfinsen]

Hello all, its been a while.

I have a function that counts the number of conditionally formatted cells in a row.

When there's been little or no activity on the worksheet the cells holding the count results can be blank or show an error message when next you open the worksheet.

I can double click on a problem cell and then hit return and the function runs ok but I need an automated/semi automated solution.

Can anyone help please?

Try Posting your code here so we can see it.

 

[Phil Payne]

Hello Anfinsen, sorry for the delay in reply.

Two functions and the 'in-cell' formula:-
:

Function Dfcolor(ByVal R As Range, ColorVal As Long) As Double
Dim r1 As Range

    Application.Volatile
    Dfcolor = 0
    For Each r1 In R
        If Evaluate("Helper(" & r1.Address() & ")") = ColorVal Then Dfcolor = Dfcolor + 1
    Next r1
    
End Function

Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

In cell code:-

=dfcolor(L9:BV9,255)

I hope you can help.

Thanks.

 

[Anfinsen]

Hello Anfinsen, sorry for the delay in reply.

Two functions and the 'in-cell' formula:-
:

Function Dfcolor(ByVal R As Range, ColorVal As Long) As Double
Dim r1 As Range

    Application.Volatile
    Dfcolor = 0
    For Each r1 In R
        If Evaluate("Helper(" & r1.Address() & ")") = ColorVal Then Dfcolor = Dfcolor + 1
    Next r1
   
End Function

Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

In cell code:-

=dfcolor(L9:BV9,255)

I hope you can help.

Thanks.

Hello,
I am not 100% sure if this will fix your issue, but try adding this code, saving the workbook, close and re open to check results.

Private Sub Workbook_Open()
    Application.Calculate
End Sub

 

[Phil Payne]

Unfortunately that doesn't work.

I want to get the in cell formula to calculate as soon a the WORKSHEET is opened.

At the moment the cells remain empty until i double click a cell and hit return.

Thanks for trying.

.

 

[Phil Payne]

i've settled for a semi automated approach.
I've oplace a command button on the worksheet and named it "Refresh" with the following code:

Private Sub CommandButton7_Click
Worksheets("My_Sheet").calculate
End sub

Thanks for your time.

 

[Anfinsen]

i've settled for a semi automated approach.
I've oplace a command button on the worksheet and named it "Refresh" with the following code:

Private Sub CommandButton7_Click
Worksheets("My_Sheet").calculate
End sub

Thanks for your time.

Hey there,
Good to see you found a solution, hitting the F9 Key will also calculate all open worksheets.