#?Name

[JD]

Everytime I try to setup a function via the visual basic editor I get '?Name
returned. What am I doing wrong??

tried this:

Function lastsaved() As Double
lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function

and this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "i14" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("h14").Value = Range("h14").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

both return #?name

Any help appreciated!

 

[Bob Phillips]

Are you putting the function in a standard code module, not the worksheet
code module?

 

[JD]

Sorry - this isn't my field...this is what I have done:

open excel
alt 11 to bring up visual basic
under this workbook - insert the code
saved and returned to excel
put in the formula

thats it

I have managed to get the following code working - it adds the date modifed
to the footer...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
For Each Sheet In ThisWorkbook.Sheets
Sheet.PageSetup.LeftFooter = "Last saved: " & Format(Date, "dd-mm-yy") & " "
& Time
Next Sheet
End Sub

I have the feeling that I am doing something wrong!!! Thanks for your help

 

[Gord Dibben]

Functions are stored in a General Module(Insert>Module) and called from a
formula in the worksheet like =lastsaved() entered in any cell.

Worksheet events are stored in the sheet module and the particular type you
posted will run when the value in I14 is manually changed. H14 accumulates
whatever is entered in I14

I would change the Worksheet_Change code......................

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address = "$I$14" And .Value <> "" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("h14").Value = Range("h14").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

You've got the Workbook_BeforeSave in the correct module(Thisworkbook).

I would suggest a chnage in that code also.

Sheet.PageSetup.LeftFooter = "Last saved: " & _
Format(Now, "dd-mm-yy hh:mm:ss")


Gord Dibben MS Excel MVP

 

[JD]

thanks for your help

can I just check this - I have cut and pasted the code - do I need to do
anything else for it to work - do I need to add anything in H14?

 

[Gord Dibben]

H14 needs no entry.............any entries in I14 will accumulate in H14


Gord