VBA counter

[Mark1]

Ok, I've made some VBA code that keeps track of how many
times a spreadsheet is open. That value shows up on my
spreadsheet in cell A1. When the spreadsheet is open,
that number increases by one. Problem: when the user
closes down the spreadsheet they are asked if they want to
save changes. If they hit No, the counter will revert
back to the previous value (the eighth user opens the
spreadsheet, the value in A1 goes to eight; but if they
don't save changes, the value goes back to seven).

Here's what I want: I want VBA to store the value, and
increase it by one on open, without displaying the value
on the spreadsheet. Thereby, the user would not be asked
if he/she wants to save changes. Here's my code, so far:

Public Sub Workbook_Open()
a = Worksheets("Sheet1").Range("A1").Value
a = AdditionA(a)
Worksheets("Sheet1").Range("A1").Value = a
End Sub

Function AdditionA(b)
AdditionA = b + 1
End Function

Help is appreciated!!

 

[Bob Phillips]

Mark,

That sounds right to me. If the user doesn't save his changes, why should it
increment. However ...

As to your suggestion, it is not possible that way. The counter has to be
part of the spreadsheet, so no matter how you store it, the spreadsheet will
have been changed, so it will need saving.

One way is to force a save, via BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

one way:

Private Sub Workbook_Open()
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
Me.Save
End Sub

 

[Mark1]

Yep, that's it. I will add a save code in there that will
save as soon as the code finishes running. Thanks for the
help, guys!

I'm learning that VBA is not about what keywords you know,
but how you can manipulate code.