Excel VBA Audit Log on Very Hidden Sheet with Interface

helpmeplease76218

New Member
Joined
Jan 11, 2021
Messages
2
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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
Hi welcome to forum

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

Dave
 

helpmeplease76218

New Member
Joined
Jan 11, 2021
Messages
2
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:

VBA Code:
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:

VBA 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top