I wanted to secure my excel workbook, i used the hard disk serial number, and used the forge user to enable macros vba. Before i had some hidden worksheets which i don't want users to see, but when one enables the macros, all my hidden worksheets are no-longer hidden, even if i hide them, when i save, they re appear, even if i use the verryhidden option.
The other problem is that after this vba when the workbook is opened in excel 2007, all password protected worksheets can be unprotected without a password.
here is the code that i used
Option Explicit
Const WelcomePage = "Macros"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Macro Purpose: Ensure that the macro instruction sheet is saved as the only
' visible worksheet in the workbook
Dim ws As Worksheet
Dim wsActive As Worksheet
Dim vFilename As Variant
Dim bSaved As Boolean
'Turn off screen flashing
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'Record active worksheet
Set wsActive = ActiveSheet
'Save workbook directly or prompt for saveas filename
If SaveAsUI = True Then
vFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xlsm), *.xlsm")
If CStr(vFilename) = "False" Then
bSaved = False
Else
'Save the workbook using the supplied filename
Call HideAllSheets
On Error Resume Next
ThisWorkbook.SaveAs vFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Select Case Err.Number
Case Is = 1004
'User opted not to overwrite
Case Else
MsgBox "Unknown error, file not saved."
bSaved = False
GoTo ExitPoint
End Select
On Error GoTo 0
'Add file to most recent files list
Application.RecentFiles.Add vFilename
Call ShowAllSheets
bSaved = True
End If
Else
'Save the workbook
Call HideAllSheets
ThisWorkbook.Save
Call ShowAllSheets
bSaved = True
End If
ExitPoint:
'Restore file to where user was
wsActive.Activate
'Restore screen updates
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
'Set application states appropriately
If bSaved Then
ThisWorkbook.Saved = True
Cancel = True
Else
Cancel = True
End If
End Sub
Private Sub Workbook_Open()
'Macro Purpose: Unhide all worksheets since macros are enabled
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub
Private Sub HideAllSheets()
'Macro Purpose: Hide all worksheets except the macro welcome page
Dim ws As Worksheet
Worksheets(WelcomePage).Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws
Worksheets(WelcomePage).Activate
End Sub
Private Sub ShowAllSheets()
'Macro Purpose: Show all worksheets except the macro welcome page
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
Dim oFSO As Object
Dim drive As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set drive = oFSO.GetDrive("C:\")
ActiveSheet.Range("A1").Value = drive.SerialNumber
Set oFSO = Nothing
Set drive = Nothing
'release memory
End Sub
i combined the HHD serial and force macros vba
where did i go wrong
coz i also want to use some of the worksheets to be hidden based on values of certain cells, but that is no-longer possible, as the worksheets are shown hidden as soon as one saves the workbook
Bookmarks