Count number of times a worksheet has been opened 1

[owentmoore]

I have a file containing many pivot tables. I do not want all of these pivot table to refresh when I open them, only the activesheet. In this way I can reduce the time to open.

I want to select a sheet and in the worksheet activate event, count how many times the sheet has been open. If it has not been opened (since the file was opened), then I will run a macro. I'll then need to increment the counter. If it has been opened I will not run the macro.
I've sample code pasted below. The problem with it is that it is not storing the value that is incremented. How do I do this?

Private Sub Worksheet_Activate()

Dim i As Integer

 If i = 0 Then

    i = i + 1
    
    ....do various tasks....    
    
    Else

    .... do various tasks....

 End If

End Sub

 

[fumei]

Make your variable counter global? As it is, it only exists within scope of the Sub.

Gerry
My paintings and sculpture

 

[SkipVought]

On a hiddeen sheet, use A1 as the UpdateDate

In the Workbook_Open event

  If Date<>YourHiddenSheet.[A1] then
     'do your updates here
     YourHiddenSheet.[A1] = Date
     Activeworkbook.Save
  end if

it will do the updates ONCE per day when opened the first time.

Skip,

_[red][/red]

 

[owentmoore]

Thanks Guys.

The update I need is only required for a specific sheet, not the workbook. So say 10 guys open the file during the day, each guy may want to go to his specific sheet. When he does the sheet updates then and only then. If he clicks off and then back onto the sheet the update will not happen then. If he closes the workbook and reopens, clicks his sheet, the update will happen again etc... The file is write protected so users cannot mess up the data.

 

[SkipVought]

Same principle, only a separate date for each sheet.

Skip,

_[red][/red]

 

[owentmoore]

Works great Skip - thanks as usual!!

Private Sub Worksheet_Activate()

If Date <> Worksheets("Bernhard").[E25] Then

...my code....

     Worksheets("Bernhard").[E25] = Date
  
   
GoTo EndNow
  
  End If
  
...my code........
    
EndNow:
    
...my code

End Sub

 

[SkipVought]

The other tip that I'd offer is to use the CodeName in your code. I routinely change the CodeName to something like
[tt]
wsQuery
wsReport
wsParms
[/tt]
Then, when I execute a query...

With wsQuery.QueryTables(1)
...

or reference a parameter

wsParms.[LastUpdate] = Date
...

The reason is that the USER can change sheet tab names. This helps to bullet-proof the application.



Skip,

_[red][/red]

 

[owentmoore]

I see your point - I don't think I'll need to use it just yet - until that is soneone decides to mess with the mfile and start changing sheet names!!