Hello There, Does anybody know of a VB code that as soon as you exi
excel, will run and delete contents in a specific cell - so when yo
next open the file the cell is empty

--
spar
-----------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...fo&userid=1678
View this thread: http://www.excelforum.com/showthread.php?threadid=38818

you need to create a private macro that clears the data in a BeforeClos
macro. Go to your VBA editor and select ThisWorkbook in the VBA Projec
Window and then go to the left drop down in the Code Window and selec
Workbook. Then go to the drop down on the right and scroll up unti
you see BeforeClose. Once you select that all you need to do is ente
the code in between Private Sub and End Su

--
BABaron
-----------------------------------------------------------------------
BABarone's Profile: http://www.excelforum.com/member.php...fo&userid=2532
View this thread: http://www.excelforum.com/showthread.php?threadid=38818

If you put this kind of thing in a workbook_beforeclose routine, you'll have to
save the workbook. And if the user wants to discard changes, you could be
messing up that user's workbook (or common workbook).

I think I'd clear the cell when I opened the workbook:

option explicit
sub auto_open()
thisworkbook.worksheets("sheet99").range("b23").value = ""
end sub

So no matter how the workbook was closed/saved, it'll be nice when you reopen
it.

sparx wrote:
>
> Hello There, Does anybody know of a VB code that as soon as you exit
> excel, will run and delete contents in a specific cell - so when you
> next open the file the cell is empty.
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=388182

--

Dave Peterson

Hello, I tried your advice and the code worked up until the time the
file actually got saved then exited - i had a VBA error and it
stopped.
Although I can use Excel, I am new to VBA code - the reason for this
program is that I want a specific cell to be empty upon a file opening
then I can put a formula inside that cell so when the file gets opened
it will display a specific text - ie getmacaddress() that for some
reason diaplays the same address if I use the code on several PC's
within an excel file. - any help would be a great help.


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=388182

I don't see anything that would cause an error when the workbook is closing. Do
you have existing code in that workbook that could be the culprit?

And I used that GetMACAddress function in that other thread (via scripting) and
I could see it recalculate when I reopened the workbook.

I had this in a cell:
=getmacaddress()


But make a minor change to clean up that address--it was part of the auto_open
routine--but now that you want to use it in a worksheet cell, you might as well
clean it up in the function.

This portion:
If InStr(Data, "MAC ADDRESS") Then
MACAddress = Trim(Split(Data, "=")(1))
Exit Do
End If
should change to:
If InStr(Data, "MAC ADDRESS") Then
MACAddress = Trim(Application.Clean(Split(Data, "=")(1)))
Exit Do
End If

And add this in your auto_open routine:

application.calculate

to see if it helps.

sparx wrote:
>
> Hello, I tried your advice and the code worked up until the time the
> file actually got saved then exited - i had a VBA error and it
> stopped.
> Although I can use Excel, I am new to VBA code - the reason for this
> program is that I want a specific cell to be empty upon a file opening
> then I can put a formula inside that cell so when the file gets opened
> it will display a specific text - ie getmacaddress() that for some
> reason diaplays the same address if I use the code on several PC's
> within an excel file. - any help would be a great help.
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=388182

--

Dave Peterson

Hello Dave, thanks for returning and helping - i am unsure though how to
use your answer - please let me explain what im trying to do.
I have several files that are quite complicated and work using formulas
- I want to try and stop the average jo using my files that they do have
access to - so I want somehow use the MAC address of the PC the file
opens on and write a formula that will switch off my formulas if the
MAC address dont match some data I have entered somewhere.

I searched the internet for ages and found several sites that gave some
vba code to get the MAC address and have supplied it below.

I have put this VBA into module1 as a general thing.

The file does work fine - it gives me my MAC address if I type
=getmacaddress() but if I save the file say on my laptop then copy the
file to another PC and use that PC to open the file, my laptop MAC
address is still displayed.

Its only until I retype =getmacaddress() over the old mac address
result when it finally changes to the new MAC address of the PC.

Strange!!

So - this prompted me to ask if anybody knew of a way to clear the cell
with the formula =getmacaddress() typed in so when the file was opened
up on a PC it would clear what ever old mac address was diaplayed and
correct itself.

This way it would only show the current MAC address of the PC the file
is open on.

You sound as though you know what your on-about and I thank you very
much for your time you have already provided me - but please would you
be so kind to assist me in how to achieve my goal within the code below
so once used, it would reset itself and display the correct MAC address
of each PC its working on.

My email address if it helps is: (E-Mail Removed)

--------------------------------------------------------------------------------------------------------

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


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=388182

I only have one pc, so I can't do that same type of testing.

I'd try this first:

sub auto_open()
application.calculate
end sub

If that doesn't help.

You could use
sub auto_open()
thisworkbook.worksheets("sheet1").range("a1").formula = "=Getmacaddress()"
end sub

Or maybe...

sub auto_open()
with thisworkbook.worksheets("sheet1").range("a1")
.clearcontents
.formula = "=Getmacaddress()"
end with
end sub

Or maybe just drop the value into the cell--why use a formula at all:


sub auto_open()
with thisworkbook.worksheets("sheet1").range("a1")
.value = getmacaddress
end with
end sub

I would think one of these would work. (Actually, I thought all of them
would--but I can't test it on different pcs.)

sparx wrote:
>
> Hello Dave, thanks for returning and helping - i am unsure though how to
> use your answer - please let me explain what im trying to do.
> I have several files that are quite complicated and work using formulas
> - I want to try and stop the average jo using my files that they do have
> access to - so I want somehow use the MAC address of the PC the file
> opens on and write a formula that will switch off my formulas if the
> MAC address dont match some data I have entered somewhere.
>
> I searched the internet for ages and found several sites that gave some
> vba code to get the MAC address and have supplied it below.
>
> I have put this VBA into module1 as a general thing.
>
> The file does work fine - it gives me my MAC address if I type
> =getmacaddress() but if I save the file say on my laptop then copy the
> file to another PC and use that PC to open the file, my laptop MAC
> address is still displayed.
>
> Its only until I retype =getmacaddress() over the old mac address
> result when it finally changes to the new MAC address of the PC.
>
> Strange!!
>
> So - this prompted me to ask if anybody knew of a way to clear the cell
> with the formula =getmacaddress() typed in so when the file was opened
> up on a PC it would clear what ever old mac address was diaplayed and
> correct itself.
>
> This way it would only show the current MAC address of the PC the file
> is open on.
>
> You sound as though you know what your on-about and I thank you very
> much for your time you have already provided me - but please would you
> be so kind to assist me in how to achieve my goal within the code below
> so once used, it would reset itself and display the correct MAC address
> of each PC its working on.
>
> My email address if it helps is: (E-Mail Removed)
>
> --------------------------------------------------------------------------------------------------------
>
> 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
>
> --
> sparx
> ------------------------------------------------------------------------
> sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
> View this thread: http://www.excelforum.com/showthread...hreadid=388182

--

Dave Peterson