Can a variable generated by workbook_open be available to other modules?

[saltire1963]

Is there a way I can stop work colleagues from deleting rows from a file they have opened other than the rows they add after opening it? What I mean is, it is important that the data in the file when they open it cannot be deleted. I thought about creating a variable called lastRowNumberOnOpening when the workbook is opened and making it available to other modules so I could do something like
If finalrow - lastRowNumberOnOpening < 1 Then .... "cannot delete rows you have not created". I think this means that the variable lastRowNumberOnOpening should be created when the workbook is opened, but have discovered that a public variable is only applicable to a standard module sub and not one that is when the workbook is opened. Any suggestions?

 

[Norie]

A public variable needs to be declared in a standard module but its value can be set in any other module.

 

[saltire1963]

but does that include a workbook_open() event rather than a module? I recall having read it does not apply to these events. Quote from book says "This type of declaration must appear in a standard VBA module, not in a code module for a sheet or a UserForm".

 

[Norie]

Not 100% sure what you are asking.

The Workbook_Open event is located in the ThisWorkbook module so you should be able to set the value of the public variable you have declared in a standard module.

 

[saltire1963]

I'm asking is the ThisWorkbook module considered the same as a standard module as far as variables go or not, because I've read that a a variable generated in a code module for a sheet or a UserForm cannot be used later using the public decleration

 

[Norie]

What do you mean by 'generated'?

You declare the public variable in a standard module and you can set it's value in any other module, including class modules like ThisWorkbook, userform/sheet modules.

 

[saltire1963]

Thanks Norie, looks like I need to look into class modules

 

[Norie]

I'm not sure you do - class modules don't really have anything to do with this.

When you a variable as public, in a standard module, it's available to all modules in the workbook - it's as simple as that.

 

[saltire1963]

Hi Norie, to explain what it is I am trying to do, although you might have a better way of doing it: My sheet contains data that is added to by multiple users at my work. As each user adds a few rows of data each week / month and saves the spreadsheet, I'd like the next user that comes along to be able to add rows and delete rows, but not be able to delete any rows from the spreadsheet that were there before they opened it. That's why I thought of using a public variable that calculates the last row (say originalLastRow) when the workbook open event is triggered in the ThisWorkbook module. I can then have another variable in a standard module that calculates the current last row (say currentLastRow). For example, say the current user adds 5 new rows, and then decides they want to delete some of these 'new' rows, then as long as currentLastRow - originalLastRow > 0, then the row can be deleted. If it is <= 0 then they are trying to delete rows created by an earlier user which is not allowed. Just thinking, if I made originalLastRow a Static variable, would this allow me to create it in the same module as the currentLastRow module and still allow me to make the same calculation?