Excel VBA Audit Log on Very Hidden Sheet with Interface

[helpmeplease76218]

Hi Everyone,

I have a quite specific question:
I am trying to create a VBA Audit Log of a Very Hidden Sheet that is edited via an interface:
So in my excel document I have a visible 'Interface' Sheet that allows individuals to edit and inset data into a database. This 'Database' Sheet is veryhidden to protect it from the users. The VBA code for this 'Database' sheet contains the code to create an Audit log (not as a module but in the sheet itself); I tested this code separately without the hidden sheet and it is working find so this code is not the issue.
So when the user edits, for example, data in the interface sheet and hits a button to save it (this save button runs a module in the VBA code), it changes the values in the database sheet and of this I want to create a log. However, since the database sheet is veryhidden and the user is only interacting with the interface, the audit code I have written for the 'Database' sheet does not run.
Also note that while the code for the save button (a module interacting with all these sheets) un-hides the database sheet, this still does not run the code on that sheet.
Hope you understand that.

Any idea how I can do this: run code saved to a veryhidden sheet that is made visible via a different save module with a user interacting only with the visible interface sheet?

Thanks in advance!

 

[dmt32]

Hi welcome to forum

Post the your code here - plenty on forum to offer suggestions to resolve the issue

Dave

 

[helpmeplease76218]

Hi Dave,

Here is the code for the audit log but as mentioned I have gotten this code to work on its own. This code is written in the VBA code for the 'Database' Sheet which is very hidden:

Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Audit As Worksheet
Dim row As Integer

Set Audit = Sheet4

    If Target.Value <> PreviousValue Then
        Audit.Select
        Audit.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = now() & " " & Application.UserName & " changed cell " & Target.Address & " from " & PreviousValue & " to " & Target.Value
      
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PreviousValue = Target.Value
End Sub

Then I have a 'save' button on an interface sheet which will run the following code:

Sub save()
Application.ScreenUpdating = False
Worksheets("Database").Visible = xlSheetVisible
Worksheets("Database").Activate

Dim interface As Worksheet
Dim database As Worksheet
Dim i As Integer
Dim storageBoxID As Integer
Dim databaseFinalRow As Integer
Dim interfaceFinalRow As Integer

Set interface = Sheet1
Set database = Sheet2

interface.Unprotect ("password3")
interface.Select
interfaceFinalRow = Cells(Rows.count, 2).End(xlUp).row

If ValidateForm Then
database.Select
databaseFinalRow = Cells(Rows.count, 1).End(xlUp).row
    For i = 2 To databaseFinalRow
        If (database.Cells(i, 2) = storageBoxID) Then
            interface.Select
            interface.Range("F11").Copy
            database.Select
            database.Cells(i, 3).PasteSpecial xlPasteFormulasAndNumberFormats
           
            interface.Select
            interface.Range("F12").Copy
            database.Select
            database.Cells(i, 8).PasteSpecial xlPasteFormulasAndNumberFormats

        End If
        Next
        interface.Select
       
End If
interface.Protect Password:= "password3"
Worksheets("Database").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Even though I refer to the database sheet and activate it, hoping it would run the code above for this audit log, its not working for me.