Increment Cell Value on Worksheet Change

[Dee]

Hi,

In cell B3, I have a worksheet version number that starts with 1.

Various people view, edit and print the file. I would like the version
number to increment by 1 each time the worksheet is opened and changes are
made.

Perhaps triggered upon open and the person can either view and not keep
changes to cancel the incrementation, or make changes and save to keep it?

Any help would be greatly appreciated!

 

[Bob Phillips]

Private Sub Workbook_Open()

With Worksheets("Sheet1").Range("B3")

.Value = .Valaue + 1
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[Dee]

Hi Bob,

Thanks for your response. I had tried something similar, but had input it
as a worksheet event.

I had also considered Worksheet_Change. Would this also work, or would it
increment at each and every change on the worksheet?

Thanks!

 

[Bob Phillips]

Hi Bob,

Thanks for your response. I had tried something similar, but had input it
as a worksheet event.

I had also considered Worksheet_Change. Would this also work, or would it
increment at each and every change on the worksheet?

Indeed it would.

 

[Dee]

Another question, if I may.

If I want this code to be easily available to users, what do you feel is the
best approach?

I had thought of adding a macro to the personal.xls that would input the
code to the active workbook, but would like a message to pop up asking which
cell contains the number and also what number they would like to start with.

Any advice on that?

Thanks!

 

[Bob Phillips]

You have a real problem if you add Personal.xls as the code would work on
that workbook, not your target workbook. You could make it application code,
but then you would need some way of determining whether the active workbook
is one that you want this code to work on.

I am not a fan of plugging code into a workbook, you might make a non macro
enabled workbook macro enabled, and the user might not appreciate that. Plus
if the workbook is macro clean, the code won't fire when you plug it in.

 

[Dee]

I'm not sure if I was clear in my last post. I didn't mean that I would put
the code in the Personal.xls. What I meant was, I would add a macro to the
Personal.xls that would essentially add the code to the active workbook, with
a message box that would pop up, asking the user which cell they wish to
increment, which would in turn be modified in the code for that specific
workbook.

Basically, I'm looking for a way for users to run the macro to add the code
to their workbook so that they can do so easily as required.

I hope this makes sense!