Calling a public function from another Macro

Question

Hi There

I am currently attempting to use this VBA on the 'ThisWorkbook' project page:

Private Sub Workbook_Open()

Worksheets("Overview").Activate

Call UserName

End Sub

To call this function:

Public Function UserName()

    UserName = Environ$("UserName")

End Function

to display the current user name in cell A15 using this formula:

=UserName()

But it doesn't seem to be working.

Is that something that should work?

Answer 1

Luke,

Make sure that "Option Private Module" is not used in Module1.

You don't need to call the function in the workbook_open sub.

Entering the formula in any cell should return the user name.

Answer 2

Hi there

The Public Function is in Module1, The 'Call UserName' code is on the 'ThisWorkbook' Project.

Many thanks

Luke

Answer 3

LudeJS,

RE:  public functions

Public functions should be in a standard module as worksheet and workbook modules are private.

'---

Custom_Functions add-in (19 of them)

Download from MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents