Dave, I have not tried your vba as yet but will do some time today - I
must thank you for your time in writing the code as sometimes I can
understand that you are guessing what others have already written. As
at this moment in time my current code for "ThisWorkboo" is:
Option Explicit
Private Sub Workbook_Open()
Application.EnableEvents = True
Run "DisableCut"
Run "Find_Disable_Commands"
Run "DisableMoveorCopy"
UnhideSheets
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheets("Access").Select
ActiveSheet.Unprotect Password:="Somethinginthisarea"
Range("E9
9").Select
Selection.ClearContents
ActiveSheet.Protect Password:="Somethinginthisarea"
'sourced from Nick Hodge's post at
http://www.adras.com/VBA-Excel-File-SaveAs.t469-5.html
'to stop it going into an endless "before save" loop by stopping _
Excel from "seeing" the save events in this macro.
Application.EnableEvents = False 'press F9 on this line
'Creating variables for use later
Dim FilePath As String
Dim NewFileName As String
Dim CurrentFileName As String
'to stop file saving (effectively telling Excel that you pressed a
cancel button)
Cancel = True
'to check how file was being saved & save as you want it to save.
Select Case SaveAsUI
Case False
ThisWorkbook.Save
Case True
'to identify variables
FilePath = ThisWorkbook.Path
NewFileName = "Book1 - Old.xls" '*
CurrentFileName = ThisWorkbook.name
'to save a copy & inform user.
ActiveWorkbook.SaveCopyAs FilePath & "\" & NewFileName '*
MsgBox "A copy of """ & CurrentFileName & """" & " is now saved, in
the same directory, as """ & NewFileName & """."
End Select
'to reset Excel's ability to "see" events such as save
Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "EnableCut"
Run "Find_Enable_Commands"
Run "EnableMoveorCopy"
HideSheets
End Sub
Private Sub HideSheets()
Dim sht As Object
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Information").Visible = xlSheetVisible
For Each sht In ThisWorkbook.Sheets
If sht.name <> "Information" Then sht.Visible = xlSheetVeryHidden
Next sht
Application.ScreenUpdating = True
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub
Private Sub UnhideSheets()
Dim sht As Object
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next sht
ThisWorkbook.Sheets("Information").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub
As you can see, there is another piece of code that when you press
"SaveAs" creates a backup of the file automatically for you. I kept
getting loops when saving - the reason for my original query - The
getmacaddress code is below - it simply looks at your PC's MAC address
and displays it.
Option Explicit
Private Const NCBASTAT As Long = &H33
Private Const NCBNAMSZ As Long = 16
Private Const HEAP_ZERO_MEMORY As Long = &H8
Private Const HEAP_GENERATE_EXCEPTIONS As Long = &H4
Private Const NCBRESET As Long = &H32
Private Type NET_CONTROL_BLOCK 'NCB
ncb_command As Byte
ncb_retcode As Byte
ncb_lsn As Byte
ncb_num As Byte
ncb_buffer As Long
ncb_length As Integer
ncb_callname As String * NCBNAMSZ
ncb_name As String * NCBNAMSZ
ncb_rto As Byte
ncb_sto As Byte
ncb_post As Long
ncb_lana_num As Byte
ncb_cmd_cplt As Byte
ncb_reserve(9) As Byte 'Reserved, must be 0
ncb_event As Long
End Type
Private Type ADAPTER_STATUS
adapter_address(5) As Byte
rev_major As Byte
reserved0 As Byte
adapter_type As Byte
rev_minor As Byte
duration As Integer
frmr_recv As Integer
frmr_xmit As Integer
iframe_recv_err As Integer
xmit_aborts As Integer
xmit_success As Long
recv_success As Long
iframe_xmit_err As Integer
recv_buff_unavail As Integer
t1_timeouts As Integer
ti_timeouts As Integer
Reserved1 As Long
free_ncbs As Integer
max_cfg_ncbs As Integer
max_ncbs As Integer
xmit_buf_unavail As Integer
max_dgram_size As Integer
pending_sess As Integer
max_cfg_sess As Integer
max_sess As Integer
max_sess_pkt_size As Integer
name_count As Integer
End Type
Private Type NAME_BUFFER
name As String * NCBNAMSZ
name_num As Integer
name_flags As Integer
End Type
Private Type ASTAT
adapt As ADAPTER_STATUS
NameBuff(30) As NAME_BUFFER
End Type
Private Declare Function Netbios Lib "netapi32" (pncb As
NET_CONTROL_BLOCK) As Byte
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(hpvDest As _
Any, ByVal hpvSource As Long, ByVal cbCopy As Long)
Private Declare Function GetProcessHeap Lib "kernel32" () As Long
Private Declare Function HeapAlloc Lib "kernel32" (ByVal hHeap As Long,
ByVal _
dwFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function HeapFree Lib "kernel32" (ByVal hHeap As Long,
ByVal dwFlags _
As Long, lpMem As Any) As Long
Public Function GetMACAddress() As String
Dim x As Integer
Dim tmp As String
Dim pASTAT As Long
Dim NCB As NET_CONTROL_BLOCK
Dim AST As ASTAT
NCB.ncb_command = NCBRESET
Call Netbios(NCB)
NCB.ncb_callname = "* "
NCB.ncb_command = NCBASTAT
NCB.ncb_lana_num = 0
NCB.ncb_length = Len(AST)
pASTAT = HeapAlloc(GetProcessHeap(), HEAP_GENERATE_EXCEPTIONS Or
HEAP_ZERO_MEMORY, NCB.ncb_length)
If pASTAT = 0 Then
Debug.Print "memory allocation failed!"
Exit Function
End If
NCB.ncb_buffer = pASTAT
Call Netbios(NCB)
CopyMemory AST, NCB.ncb_buffer, Len(AST)
For x = 0 To 5
tmp = tmp & Right$("00" & Hex(AST.adapt.adapter_address(x)), 2) &
"-"
Next x
tmp = Left(tmp, Len(tmp) - 1)
HeapFree GetProcessHeap(), 0, pASTAT
GetMACAddress = tmp
End Function
