Author Name and User Name - Does anyone know the VBA and cell formulas to use?

[Tyron]

Hey Guys,

Probably something that isn't very hard. I have actually found out how to do the User Name.

VBA:

Username = Environ("UserName")

Cell Formula:

=Username()

This takes care of the Username.

I can't, for some reason, find anything online about grabbing the workbook's author(s) name though. Anyone out there know how to do this?

later

Ty

 

[L. Howard]

Hi Tyron,

With the function in a standard module.
Use this on the worksheet

=LastAuthor()

Option Explicit

Public Function LastAuthor() As String
   Application.Volatile
   LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Or with this sub in the sheet module and the above function in a standard module.
To list author in a cell by code.

Sub MisterAuthor()
 Range("C1").Formula = "=LastAuthor()"
End Sub

Regards,
Howard

 

[Tyron]

Hey L. Howard,

Thanks for the reply. I will give it a try and let you know how it goes.

In your first example it seems to show that you have created a UDF and make a call to that UDF with the =LastAuthor(). Is this correct?

Also, would I be able to just type =builtindocoumentproperties.lastauthor("Last Author")
in a cell so I would not need any VBA?

I apologize if my previous post is confusing. I was actually looking for two different independent ways to find the information. 1.)using VBA so I can make it part of my macro 2.)Using Excel formula only so I can verify the information through a second source that isn't part of the macro.

Thanks again for your help.

Ty

 

[L. Howard]

Hi Tyron,

In your first example it seems to show that you have created a UDF and make a call to that UDF with the =LastAuthor(). Is this correct?

Yes, that is true.

Also, would I be able to just type =builtindocoumentproperties.lastauthor("Last Author")
 in a cell so I would not need any VBA?

2.)Using Excel formula only so I can verify the information through a second source that isn't part of the macro.

Not that I know of. But may be possible.

1.)using VBA so I can make it part of my macro

The function I googled and posted does that, if I read you correct.

Put this in a standard module and type in two XX's in F1, then run Sub UseMyLastAuthor()

Option Explicit

Public Function LastAuthor() As String
   Application.Volatile
   LastAuthor = ThisWorkbook.BuiltinDocumentProperties("Last Author")
End Function

Sub UseMyLastAuthor()
If Range("F1") = "XX" Then

  MsgBox "There are two XX's in Cell F1 and they were put there by " _
         & vbCr & LastAuthor() & vbCr & "That is me."

End If
End Sub

Howard

 

[Tyron]

Hey L. Howard,

Thanks for your help. I have discovered I think a problem though. It seems to be getting the name from somewhere other than the author. I guess I would need to know where it gets the name from within excel. I only say this because it runs normal, but for the name it is blank. This is strange.

I am unable to substantiate whether we can just directly put the information in to a cell formula as I cannot get the UDF to work. Probably not the UDF's fault. I just need to find out where in the computer I can go to visually confirm and/or change the "Authors" name so that it can work.

Let me know if you figure it. I am off to hunt this bugger down.

later

Ty