PC Review


Reply
Thread Tools Rate Thread

How too set last edited date in excel and which user was it?

 
 
=?Utf-8?B?amlubGFyc2U=?=
Guest
Posts: n/a
 
      21st Mar 2006
I can't find a function too set a formula in top of a document in a excel
document. It should work like the log book in excel.
 
Reply With Quote
 
 
 
 
=?Utf-8?B?R2FyeSBMIEJyb3du?=
Guest
Posts: n/a
 
      21st Mar 2006
Create a user-defined-function.....

'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/

HTH,
--
Gary Brown
(E-Mail Removed)
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jinlarse" wrote:

> I can't find a function too set a formula in top of a document in a excel
> document. It should work like the log book in excel.

 
Reply With Quote
 
 
 
 
=?Utf-8?B?amlubGFyc2U=?=
Guest
Posts: n/a
 
      22nd Mar 2006
I have made a udf and saved in global.xls and returned to excel. I worksheet
I type =LastSaved()

But nothing happened?

And I also wan't to now which user was it who saved last time? Is it possible?

Thanks
jinlarse


"Gary L Brown" wrote:

> Create a user-defined-function.....
>
> '/==========================================/
> Public Function LastSaved()
> On Error Resume Next
> LastSaved = _
> activeworkbook.BuiltinDocumentProperties("Last Save Time").value
> End Function
> '/==========================================/
>
> HTH,
> --
> Gary Brown
> (E-Mail Removed)
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "jinlarse" wrote:
>
> > I can't find a function too set a formula in top of a document in a excel
> > document. It should work like the log book in excel.

 
Reply With Quote
 
=?Utf-8?B?R2FyeSBMIEJyb3du?=
Guest
Posts: n/a
 
      22nd Mar 2006
Couple of thoughts.
The good news is...the fact that you are not getting a '#NAME?' error
message means that Excel is finding and recognizing the function. The bad
news is that I don't know why the function is not working.

Question 1) Do you have Macros disabled? If so, the function won't run.
Question 2) Are you using a language other than English? If so, see below.

I'm giving you 3 macros.
Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
properties listed out
Sub GetWorkbookProperties() - creates a message box with all the workbook
properties listed out
Sub LastModified_LastSavedBy() - puts the last modified date/time in the
current cell and who last saved the file in next cell down

Run one of the first two macros ['ListWorkbookProperties()' or
'GetWorkbookProperties()']. This will give you a list of your worksheet
properties.

In reference to Question 2...
Look down the list. Is there an item that says 'Last save time'? If not,
is there another term that means the same thing? If so, THAT is the term you
should put in the function 'LastSaved()'.

By-the-way,
Original Author of workbook:
ActiveWorkbook.BuiltinDocumentProperties("Author").value
Last person to save workbook:
ActiveWorkbook.BuiltinDocumentProperties("Last Author").value


'========= MACROS START HERE =================
'/===========================================/
Sub ListWorkbookProperties()
'List Workbook Properites to new worksheet
' both Built-in and Custom
Dim iRow As Integer, iWorksheets As Integer
Dim i As Integer
Dim x As Integer, y As Integer
Dim objProperty As Object
Dim strResultsTableName As String
Dim strOrigCalcStatus As String

On Error Resume Next

'* Variables * * * * * * * * * * * *
strResultsTableName = "Workbook_Properties"
iRow = 1
'* * * * * * * * * * * * * * * * * *

'save calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = "Automatic"
Case xlCalculationManual
strOrigCalcStatus = "Manual"
Case xlCalculationSemiautomatic
strOrigCalcStatus = "SemiAutomatic"
Case Else
strOrigCalcStatus = "Automatic"
End Select

'set workbook to manual
Application.Calculation = xlManual

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'redim array
ReDim aryHiddensheets(1 To iWorksheets)

'put hidden sheets in an array, then unhide the sheets
For x = 1 To iWorksheets
If Worksheets(x).Visible = False Then
aryHiddensheets(x) = Worksheets(x).Name
Worksheets(x).Visible = True
End If
Next

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If UCase(Worksheets(x).Name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").Value = "Type"
ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
ActiveWorkbook.ActiveSheet.Range("C1").Value = _
"Value"
Range("A1:C1").Font.Bold = True

iRow = iRow + 1

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
Cells(iRow, 1) = "Builtin"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
Cells(iRow, 1) = "Custom"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'formatting
ActiveWindow.Zoom = 75
Columns("A:C").EntireColumn.AutoFit
Columns("C:C").Select
If Selection.ColumnWidth > 80 Then
Selection.ColumnWidth = 80
End If

With Selection
.WrapText = True
.HorizontalAlignment = xlLeft
End With

Range("A2").Select
ActiveWindow.FreezePanes = True

're-hide previously hidden sheets
On Error Resume Next
y = UBound(aryHiddensheets)
For x = 1 To y
Worksheets(aryHiddensheets(x)).Visible = False
Next

Range("A2").Select

're-set to original calculation method
Select Case strOrigCalcStatus
Case "Automatic"
Application.Calculation = xlCalculationAutomatic
Case "Manual"
Application.Calculation = xlCalculationManual
Case "SemiAutomatic"
Application.Calculation = _
xlCalculationSemiautomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

Application.Dialogs(xlDialogWorkbookName).Show

End Sub
'/===========================================/
Sub GetWorkbookProperties()
'List Workbook Properites to Msgbox
' both Built-in and Custom
Dim objProperty As Object
Dim strAnswer As String

On Error Resume Next

'List Workbook name and size
strAnswer = "Workbook: " & vbCr & _
Excel.ActiveWorkbook.FullName & vbCr & _
" - Workbook File Size: " & _
Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
"#,##0") & " kb" & vbCr

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Builtin - " & _
.Name & " : " & .Value
End With
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Custom - " & _
.Name & " : " & .Value
End With
Next

MsgBox strAnswer

End Sub
'/===========================================/
Sub LastModified_LastSavedBy()
'put last modified date/time in current cell
'put who last saved the file in next cell down
On Error GoTo err_Sub

ActiveCell.Value = "Last Modified: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: LastModified_LastSavedBy - " & _
"Module: Module2 - " & Now()
GoTo exit_Sub
End Sub
'/===========================================/
'========= MACROS END HERE =================

Good Luck and Hope This Helps,
--
Gary Brown
(E-Mail Removed)
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jinlarse" wrote:

> I have made a udf and saved in global.xls and returned to excel. I worksheet
> I type =LastSaved()
>
> But nothing happened?
>
> And I also wan't to now which user was it who saved last time? Is it possible?
>
> Thanks
> jinlarse
>
>
> "Gary L Brown" wrote:
>
> > Create a user-defined-function.....
> >
> > '/==========================================/
> > Public Function LastSaved()
> > On Error Resume Next
> > LastSaved = _
> > activeworkbook.BuiltinDocumentProperties("Last Save Time").value
> > End Function
> > '/==========================================/
> >
> > HTH,
> > --
> > Gary Brown
> > (E-Mail Removed)
> > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > Post Helpfull to you?''.
> >
> >
> > "jinlarse" wrote:
> >
> > > I can't find a function too set a formula in top of a document in a excel
> > > document. It should work like the log book in excel.

 
Reply With Quote
 
=?Utf-8?B?amlubGFyc2U=?=
Guest
Posts: n/a
 
      23rd Mar 2006
Thanks, I have tried this one now and it's working.

The macro "LastModified_LastSavedBy()" do exatly what I want, but I have to
run the macro every time to get this activited and it put the result in that
cell where I am, not in that cell I want it to be (same cell every time). It
is possibly to let this run automatic on every save? And there is many users
who use this workbook and I cant get them to run macros everytime, so can
this be used of every one. The worksheet is saved in a common filearea for
the users.

I dont now if this is possibly, but I'm very pleased of your info about this
and your answers you already have produced to me, thanks.

jinlarse

"Gary L Brown" wrote:

> Couple of thoughts.
> The good news is...the fact that you are not getting a '#NAME?' error
> message means that Excel is finding and recognizing the function. The bad
> news is that I don't know why the function is not working.
>
> Question 1) Do you have Macros disabled? If so, the function won't run.
> Question 2) Are you using a language other than English? If so, see below.
>
> I'm giving you 3 macros.
> Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
> properties listed out
> Sub GetWorkbookProperties() - creates a message box with all the workbook
> properties listed out
> Sub LastModified_LastSavedBy() - puts the last modified date/time in the
> current cell and who last saved the file in next cell down
>
> Run one of the first two macros ['ListWorkbookProperties()' or
> 'GetWorkbookProperties()']. This will give you a list of your worksheet
> properties.
>
> In reference to Question 2...
> Look down the list. Is there an item that says 'Last save time'? If not,
> is there another term that means the same thing? If so, THAT is the term you
> should put in the function 'LastSaved()'.
>
> By-the-way,
> Original Author of workbook:
> ActiveWorkbook.BuiltinDocumentProperties("Author").value
> Last person to save workbook:
> ActiveWorkbook.BuiltinDocumentProperties("Last Author").value
>
>
> '========= MACROS START HERE =================
> '/===========================================/
> Sub ListWorkbookProperties()
> 'List Workbook Properites to new worksheet
> ' both Built-in and Custom
> Dim iRow As Integer, iWorksheets As Integer
> Dim i As Integer
> Dim x As Integer, y As Integer
> Dim objProperty As Object
> Dim strResultsTableName As String
> Dim strOrigCalcStatus As String
>
> On Error Resume Next
>
> '* Variables * * * * * * * * * * * *
> strResultsTableName = "Workbook_Properties"
> iRow = 1
> '* * * * * * * * * * * * * * * * * *
>
> 'save calculation setting
> Select Case Application.Calculation
> Case xlCalculationAutomatic
> strOrigCalcStatus = "Automatic"
> Case xlCalculationManual
> strOrigCalcStatus = "Manual"
> Case xlCalculationSemiautomatic
> strOrigCalcStatus = "SemiAutomatic"
> Case Else
> strOrigCalcStatus = "Automatic"
> End Select
>
> 'set workbook to manual
> Application.Calculation = xlManual
>
> 'Count number of worksheets in workbook
> iWorksheets = ActiveWorkbook.Sheets.Count
>
> 'redim array
> ReDim aryHiddensheets(1 To iWorksheets)
>
> 'put hidden sheets in an array, then unhide the sheets
> For x = 1 To iWorksheets
> If Worksheets(x).Visible = False Then
> aryHiddensheets(x) = Worksheets(x).Name
> Worksheets(x).Visible = True
> End If
> Next
>
> 'Check for duplicate Worksheet name
> i = ActiveWorkbook.Sheets.Count
> For x = 1 To i
> If UCase(Worksheets(x).Name) = _
> UCase(strResultsTableName) Then
> Worksheets(x).Activate
> If Err.Number = 9 Then
> Exit For
> End If
> Application.DisplayAlerts = False
> ActiveWindow.SelectedSheets.Delete
> Application.DisplayAlerts = True
> Exit For
> End If
> Next
>
> 'Add new worksheet at end of workbook
> ' where results will be located
> Worksheets.Add.Move _
> After:=Worksheets(Worksheets.Count)
>
> 'Name the new worksheet and set up Titles
> ActiveWorkbook.ActiveSheet.Name = strResultsTableName
> ActiveWorkbook.ActiveSheet.Range("A1").Value = "Type"
> ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
> ActiveWorkbook.ActiveSheet.Range("C1").Value = _
> "Value"
> Range("A1:C1").Font.Bold = True
>
> iRow = iRow + 1
>
> 'List Builtin Document Properties
> For Each objProperty In _
> ActiveWorkbook.BuiltinDocumentProperties
> With objProperty
> Cells(iRow, 1) = "Builtin"
> Cells(iRow, 2) = .Name
> Cells(iRow, 3) = .Value
> End With
> iRow = iRow + 1
> Next
>
> 'List Custom Document Properties
> For Each objProperty In _
> ActiveWorkbook.CustomDocumentProperties
> With objProperty
> Cells(iRow, 1) = "Custom"
> Cells(iRow, 2) = .Name
> Cells(iRow, 3) = .Value
> End With
> iRow = iRow + 1
> Next
>
> 'formatting
> ActiveWindow.Zoom = 75
> Columns("A:C").EntireColumn.AutoFit
> Columns("C:C").Select
> If Selection.ColumnWidth > 80 Then
> Selection.ColumnWidth = 80
> End If
>
> With Selection
> .WrapText = True
> .HorizontalAlignment = xlLeft
> End With
>
> Range("A2").Select
> ActiveWindow.FreezePanes = True
>
> 're-hide previously hidden sheets
> On Error Resume Next
> y = UBound(aryHiddensheets)
> For x = 1 To y
> Worksheets(aryHiddensheets(x)).Visible = False
> Next
>
> Range("A2").Select
>
> 're-set to original calculation method
> Select Case strOrigCalcStatus
> Case "Automatic"
> Application.Calculation = xlCalculationAutomatic
> Case "Manual"
> Application.Calculation = xlCalculationManual
> Case "SemiAutomatic"
> Application.Calculation = _
> xlCalculationSemiautomatic
> Case Else
> Application.Calculation = xlCalculationAutomatic
> End Select
>
> Application.Dialogs(xlDialogWorkbookName).Show
>
> End Sub
> '/===========================================/
> Sub GetWorkbookProperties()
> 'List Workbook Properites to Msgbox
> ' both Built-in and Custom
> Dim objProperty As Object
> Dim strAnswer As String
>
> On Error Resume Next
>
> 'List Workbook name and size
> strAnswer = "Workbook: " & vbCr & _
> Excel.ActiveWorkbook.FullName & vbCr & _
> " - Workbook File Size: " & _
> Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
> "#,##0") & " kb" & vbCr
>
> 'List Builtin Document Properties
> For Each objProperty In _
> ActiveWorkbook.BuiltinDocumentProperties
> With objProperty
> strAnswer = strAnswer & vbCr & "Builtin - " & _
> .Name & " : " & .Value
> End With
> Next
>
> 'List Custom Document Properties
> For Each objProperty In _
> ActiveWorkbook.CustomDocumentProperties
> With objProperty
> strAnswer = strAnswer & vbCr & "Custom - " & _
> .Name & " : " & .Value
> End With
> Next
>
> MsgBox strAnswer
>
> End Sub
> '/===========================================/
> Sub LastModified_LastSavedBy()
> 'put last modified date/time in current cell
> 'put who last saved the file in next cell down
> On Error GoTo err_Sub
>
> ActiveCell.Value = "Last Modified: " & _
> ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
> ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
> ActiveWorkbook.BuiltinDocumentProperties("Last author").Value
>
> exit_Sub:
> On Error Resume Next
> Exit Sub
>
> err_Sub:
>
> Debug.Print "Error: " & Err.Number & " - (" & _
> Err.Description & _
> ") - Sub: LastModified_LastSavedBy - " & _
> "Module: Module2 - " & Now()
> GoTo exit_Sub
> End Sub
> '/===========================================/
> '========= MACROS END HERE =================
>
> Good Luck and Hope This Helps,
> --
> Gary Brown
> (E-Mail Removed)
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "jinlarse" wrote:
>
> > I have made a udf and saved in global.xls and returned to excel. I worksheet
> > I type =LastSaved()
> >
> > But nothing happened?
> >
> > And I also wan't to now which user was it who saved last time? Is it possible?
> >
> > Thanks
> > jinlarse
> >
> >
> > "Gary L Brown" wrote:
> >
> > > Create a user-defined-function.....
> > >
> > > '/==========================================/
> > > Public Function LastSaved()
> > > On Error Resume Next
> > > LastSaved = _
> > > activeworkbook.BuiltinDocumentProperties("Last Save Time").value
> > > End Function
> > > '/==========================================/
> > >
> > > HTH,
> > > --
> > > Gary Brown
> > > (E-Mail Removed)
> > > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > > Post Helpfull to you?''.
> > >
> > >
> > > "jinlarse" wrote:
> > >
> > > > I can't find a function too set a formula in top of a document in a excel
> > > > document. It should work like the log book in excel.

 
Reply With Quote
 
=?Utf-8?B?R2FyeSBMIEJyb3du?=
Guest
Posts: n/a
 
      23rd Mar 2006
Here are 2 separate user-defined-functions that will do what you want..

Put them in your workbook.

The syntax is...

=LastSaved()
and
=LastAuthor()


'/=====================================/
' Function Purpose: Last time current workbook was saved
'
Public Function LastSaved()

On Error GoTo err_Function

Application.Volatile

LastSaved = "Last Saved: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value

exit_Function:
On Error Resume Next
Exit Function

err_Function:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & ") - Function: LastSaved - " & Now()
GoTo exit_Function
End Function

'/=====================================/
' Function Purpose: Last Person to save the current workbook
'
Public Function LastAuthor()

On Error GoTo err_Function

Application.Volatile

LastAuthor = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

exit_Function:
On Error Resume Next
Exit Function

err_Function:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: LastAuthor - " & Now()
GoTo exit_Function
End Function

'/=====================================/
--
Gary Brown
(E-Mail Removed)
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jinlarse" wrote:

> Thanks, I have tried this one now and it's working.
>
> The macro "LastModified_LastSavedBy()" do exatly what I want, but I have to
> run the macro every time to get this activited and it put the result in that
> cell where I am, not in that cell I want it to be (same cell every time). It
> is possibly to let this run automatic on every save? And there is many users
> who use this workbook and I cant get them to run macros everytime, so can
> this be used of every one. The worksheet is saved in a common filearea for
> the users.
>
> I dont now if this is possibly, but I'm very pleased of your info about this
> and your answers you already have produced to me, thanks.
>
> jinlarse
>
> "Gary L Brown" wrote:
>
> > Couple of thoughts.
> > The good news is...the fact that you are not getting a '#NAME?' error
> > message means that Excel is finding and recognizing the function. The bad
> > news is that I don't know why the function is not working.
> >
> > Question 1) Do you have Macros disabled? If so, the function won't run.
> > Question 2) Are you using a language other than English? If so, see below.
> >
> > I'm giving you 3 macros.
> > Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
> > properties listed out
> > Sub GetWorkbookProperties() - creates a message box with all the workbook
> > properties listed out
> > Sub LastModified_LastSavedBy() - puts the last modified date/time in the
> > current cell and who last saved the file in next cell down
> >
> > Run one of the first two macros ['ListWorkbookProperties()' or
> > 'GetWorkbookProperties()']. This will give you a list of your worksheet
> > properties.
> >
> > In reference to Question 2...
> > Look down the list. Is there an item that says 'Last save time'? If not,
> > is there another term that means the same thing? If so, THAT is the term you
> > should put in the function 'LastSaved()'.
> >
> > By-the-way,
> > Original Author of workbook:
> > ActiveWorkbook.BuiltinDocumentProperties("Author").value
> > Last person to save workbook:
> > ActiveWorkbook.BuiltinDocumentProperties("Last Author").value
> >
> >
> > '========= MACROS START HERE =================
> > '/===========================================/
> > Sub ListWorkbookProperties()
> > 'List Workbook Properites to new worksheet
> > ' both Built-in and Custom
> > Dim iRow As Integer, iWorksheets As Integer
> > Dim i As Integer
> > Dim x As Integer, y As Integer
> > Dim objProperty As Object
> > Dim strResultsTableName As String
> > Dim strOrigCalcStatus As String
> >
> > On Error Resume Next
> >
> > '* Variables * * * * * * * * * * * *
> > strResultsTableName = "Workbook_Properties"
> > iRow = 1
> > '* * * * * * * * * * * * * * * * * *
> >
> > 'save calculation setting
> > Select Case Application.Calculation
> > Case xlCalculationAutomatic
> > strOrigCalcStatus = "Automatic"
> > Case xlCalculationManual
> > strOrigCalcStatus = "Manual"
> > Case xlCalculationSemiautomatic
> > strOrigCalcStatus = "SemiAutomatic"
> > Case Else
> > strOrigCalcStatus = "Automatic"
> > End Select
> >
> > 'set workbook to manual
> > Application.Calculation = xlManual
> >
> > 'Count number of worksheets in workbook
> > iWorksheets = ActiveWorkbook.Sheets.Count
> >
> > 'redim array
> > ReDim aryHiddensheets(1 To iWorksheets)
> >
> > 'put hidden sheets in an array, then unhide the sheets
> > For x = 1 To iWorksheets
> > If Worksheets(x).Visible = False Then
> > aryHiddensheets(x) = Worksheets(x).Name
> > Worksheets(x).Visible = True
> > End If
> > Next
> >
> > 'Check for duplicate Worksheet name
> > i = ActiveWorkbook.Sheets.Count
> > For x = 1 To i
> > If UCase(Worksheets(x).Name) = _
> > UCase(strResultsTableName) Then
> > Worksheets(x).Activate
> > If Err.Number = 9 Then
> > Exit For
> > End If
> > Application.DisplayAlerts = False
> > ActiveWindow.SelectedSheets.Delete
> > Application.DisplayAlerts = True
> > Exit For
> > End If
> > Next
> >
> > 'Add new worksheet at end of workbook
> > ' where results will be located
> > Worksheets.Add.Move _
> > After:=Worksheets(Worksheets.Count)
> >
> > 'Name the new worksheet and set up Titles
> > ActiveWorkbook.ActiveSheet.Name = strResultsTableName
> > ActiveWorkbook.ActiveSheet.Range("A1").Value = "Type"
> > ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
> > ActiveWorkbook.ActiveSheet.Range("C1").Value = _
> > "Value"
> > Range("A1:C1").Font.Bold = True
> >
> > iRow = iRow + 1
> >
> > 'List Builtin Document Properties
> > For Each objProperty In _
> > ActiveWorkbook.BuiltinDocumentProperties
> > With objProperty
> > Cells(iRow, 1) = "Builtin"
> > Cells(iRow, 2) = .Name
> > Cells(iRow, 3) = .Value
> > End With
> > iRow = iRow + 1
> > Next
> >
> > 'List Custom Document Properties
> > For Each objProperty In _
> > ActiveWorkbook.CustomDocumentProperties
> > With objProperty
> > Cells(iRow, 1) = "Custom"
> > Cells(iRow, 2) = .Name
> > Cells(iRow, 3) = .Value
> > End With
> > iRow = iRow + 1
> > Next
> >
> > 'formatting
> > ActiveWindow.Zoom = 75
> > Columns("A:C").EntireColumn.AutoFit
> > Columns("C:C").Select
> > If Selection.ColumnWidth > 80 Then
> > Selection.ColumnWidth = 80
> > End If
> >
> > With Selection
> > .WrapText = True
> > .HorizontalAlignment = xlLeft
> > End With
> >
> > Range("A2").Select
> > ActiveWindow.FreezePanes = True
> >
> > 're-hide previously hidden sheets
> > On Error Resume Next
> > y = UBound(aryHiddensheets)
> > For x = 1 To y
> > Worksheets(aryHiddensheets(x)).Visible = False
> > Next
> >
> > Range("A2").Select
> >
> > 're-set to original calculation method
> > Select Case strOrigCalcStatus
> > Case "Automatic"
> > Application.Calculation = xlCalculationAutomatic
> > Case "Manual"
> > Application.Calculation = xlCalculationManual
> > Case "SemiAutomatic"
> > Application.Calculation = _
> > xlCalculationSemiautomatic
> > Case Else
> > Application.Calculation = xlCalculationAutomatic
> > End Select
> >
> > Application.Dialogs(xlDialogWorkbookName).Show
> >
> > End Sub
> > '/===========================================/
> > Sub GetWorkbookProperties()
> > 'List Workbook Properites to Msgbox
> > ' both Built-in and Custom
> > Dim objProperty As Object
> > Dim strAnswer As String
> >
> > On Error Resume Next
> >
> > 'List Workbook name and size
> > strAnswer = "Workbook: " & vbCr & _
> > Excel.ActiveWorkbook.FullName & vbCr & _
> > " - Workbook File Size: " & _
> > Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
> > "#,##0") & " kb" & vbCr
> >
> > 'List Builtin Document Properties
> > For Each objProperty In _
> > ActiveWorkbook.BuiltinDocumentProperties
> > With objProperty
> > strAnswer = strAnswer & vbCr & "Builtin - " & _
> > .Name & " : " & .Value
> > End With
> > Next
> >
> > 'List Custom Document Properties
> > For Each objProperty In _
> > ActiveWorkbook.CustomDocumentProperties
> > With objProperty
> > strAnswer = strAnswer & vbCr & "Custom - " & _
> > .Name & " : " & .Value
> > End With
> > Next
> >
> > MsgBox strAnswer
> >
> > End Sub
> > '/===========================================/
> > Sub LastModified_LastSavedBy()
> > 'put last modified date/time in current cell
> > 'put who last saved the file in next cell down
> > On Error GoTo err_Sub
> >
> > ActiveCell.Value = "Last Modified: " & _
> > ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
> > ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
> > ActiveWorkbook.BuiltinDocumentProperties("Last author").Value
> >
> > exit_Sub:
> > On Error Resume Next
> > Exit Sub
> >
> > err_Sub:
> >
> > Debug.Print "Error: " & Err.Number & " - (" & _
> > Err.Description & _
> > ") - Sub: LastModified_LastSavedBy - " & _
> > "Module: Module2 - " & Now()
> > GoTo exit_Sub
> > End Sub
> > '/===========================================/
> > '========= MACROS END HERE =================
> >
> > Good Luck and Hope This Helps,
> > --
> > Gary Brown
> > (E-Mail Removed)
> > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > Post Helpfull to you?''.
> >
> >
> > "jinlarse" wrote:
> >
> > > I have made a udf and saved in global.xls and returned to excel. I worksheet
> > > I type =LastSaved()
> > >
> > > But nothing happened?
> > >
> > > And I also wan't to now which user was it who saved last time? Is it possible?
> > >
> > > Thanks
> > > jinlarse
> > >
> > >
> > > "Gary L Brown" wrote:
> > >
> > > > Create a user-defined-function.....
> > > >
> > > > '/==========================================/
> > > > Public Function LastSaved()
> > > > On Error Resume Next
> > > > LastSaved = _
> > > > activeworkbook.BuiltinDocumentProperties("Last Save Time").value
> > > > End Function
> > > > '/==========================================/
> > > >
> > > > HTH,
> > > > --
> > > > Gary Brown
> > > > (E-Mail Removed)
> > > > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > > > Post Helpfull to you?''.
> > > >
> > > >
> > > > "jinlarse" wrote:
> > > >
> > > > > I can't find a function too set a formula in top of a document in a excel
> > > > > document. It should work like the log book in excel.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display file size and date last edited =?Utf-8?B?RUFVUw==?= Microsoft Frontpage 2 5th Sep 2006 01:22 PM
Last Freeware Version (site which contains the last freeware versions of progs which have gone shareware/commerical. Eugene Esterly III Freeware 2 4th May 2006 01:10 AM
Excel 2003: can I insert a "last edited" date function? =?Utf-8?B?S1RL?= Microsoft Excel Worksheet Functions 1 27th Jun 2005 11:59 PM
Determining user who last opened and edited a file =?Utf-8?B?Y3VydGFpbndhbGw=?= Microsoft Windows 2000 Advanced Server 0 18th Nov 2004 08:10 PM
"The text is too long too be edited" paul Microsoft Access Form Coding 2 23rd Jun 2004 05:00 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:50 PM.