Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting

Why not just protect your code to prevent changes. In the VB editor,
ToolsVBAProjectPropertiesProtect

You can set a password to prevent unauthorized changes by most users.
Of course, passwords can be overcome by astute users, but it would be
effective against most.

" wrote:

Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting

No, I permit source code change, instead of protecting it.

In VBA project, some constants are defined, which serve as
configuration purpose. When configuration changes, I need
to do something like re-formatting in the workbook the next time
it is opened (according to the new configuration).

So the most expected behaviour is that some mark can be done
"automatically" once VBA project is changed.

Ting


On Mar 19, 12:23 pm, JLGWhiz
wrote:
Why not just protect your code to prevent changes. In the VB editor,
ToolsVBAProjectPropertiesProtect

You can set a password to prevent unauthorized changes by most users.
Of course, passwords can be overcome by astute users, but it would be
effective against most.

" wrote:
Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting

I don't believe VBA supports a self editing capability, other than the
compiler, syntax and structure edits. If the changes do not generate errors,
then the built in edits would never detect the changes.

I don't know if the 'LastModified' property would be that useful since it
might not refer to code modifications. You can check that out in VBA help.

" wrote:

No, I permit source code change, instead of protecting it.

In VBA project, some constants are defined, which serve as
configuration purpose. When configuration changes, I need
to do something like re-formatting in the workbook the next time
it is opened (according to the new configuration).

So the most expected behaviour is that some mark can be done
"automatically" once VBA project is changed.

Ting


On Mar 19, 12:23 pm, JLGWhiz
wrote:
Why not just protect your code to prevent changes. In the VB editor,
ToolsVBAProjectPropertiesProtect

You can set a password to prevent unauthorized changes by most users.
Of course, passwords can be overcome by astute users, but it would be
effective against most.

" wrote:
Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting

The two macros below allow you to check for the date a file was last
modified. Don't know if that will help you with what you are trying to do,
but you can make that evaluation. The short macro is used to activate the
longer one. Just substitute your actual file name for the value of fName.

Sub getdate()
fName = "YourFileName.xls"
ShowFileAccessInfo (fName)
End Sub

Sub ShowFileAccessInfo(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(filespec) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub

" wrote:

No, I permit source code change, instead of protecting it.

In VBA project, some constants are defined, which serve as
configuration purpose. When configuration changes, I need
to do something like re-formatting in the workbook the next time
it is opened (according to the new configuration).

So the most expected behaviour is that some mark can be done
"automatically" once VBA project is changed.

Ting


On Mar 19, 12:23 pm, JLGWhiz
wrote:
Why not just protect your code to prevent changes. In the VB editor,
ToolsVBAProjectPropertiesProtect

You can set a password to prevent unauthorized changes by most users.
Of course, passwords can be overcome by astute users, but it would be
effective against most.

" wrote:
Hello,

Is is possible to detect the code change happened to VBA project? Or
something like "save" event of VBA project (not workbook)?

I intend to leave a mark in worksheet cells if VBA code has been
edited, and next time when the book is opened, I can perform some
processing if the mark is on and then turn off it.

Thanks in advance.

Ting