Saving an Excel Sheet as PDF automatically through VBA to Dropbox using multiple users on Macintosh

Question

This is my first post so please let me know if I have entered anything improperly or ways to improve my questions.

I have a time sheet spread sheet which I would like to be saved to a company dropbox folder once filled out and closed. I can get it to work using only a single user but I would like to be able to save it regardless of the user. I have seen a similar question in the linked post VBA: How to save Excel Workbook to Desktop regardless of user? however it does not solve my problem as I am on a mac and I don't know how to define the username. I had thought about the person uploading the timesheet to enter their name as the user however they do not each have their own computer and will be sharing.

I am using Sheebs as the user name in my code

here is what i have for code so far

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

MSG1 = MsgBox("Upload Timesheet?", vbOKCancel, "Save Timesheet and Update Payroll Matrix?")
If MSG1 = Cancel Then GoTo lastline Else: GoTo Saveas


Saveas:


Dim fName As String
With ActiveSheet
    fName = .Range("D8").Value & "-" & .Range("D7").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:="Macintosh HD:Users:Sheebs:Dropbox:Time        Sheet:" & .Range("B8").Value & ":" & .Range("C8").Value & ":" & fName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

lastline:
ThisWorkbook.Saved = True

End Sub

Answer 1

From here:

VBA Get Username on OSX (or Mac alternative to Environ("username"))

You may be able to use the function described in the link above to get the username (untested):

Dim fName As String
Dim uName As String
Dim savePath as String

uName = GetUserNameMac()

With ActiveSheet
    fName = .Range("D8").Value & "-" & .Range("D7").Value
    savePath = "Macintosh HD:Users:" & uName & ":Dropbox:TimeSheet:" & _
        .Range("B8").Value & ":" & .Range("C8").Value & ":" & fName

    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, _
    Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

The function:

Function GetUserNameMac() As String
    Dim sMyScript As String

    sMyScript = "set userName to short user name of (system info)" & vbNewLine & "return userName"

    GetUserNameMac = MacScript(sMyScript)
End Function