VBA code not working on Mac

[Linki]

Hi everyone!

I've got a piece of code that @Candyman8019 kindly helped me create.
It works well on windows, but when opened on a Macbook, it does not work.
Would anyone here know how to adjust the below code to work on windows as well as mac?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'if User logged in then don't auto-close
' NOTE:  Since this uses the Windows login name, make sure it is correct below.
If Environ("username") = "User1" Or Environ("username") = "User2" Then
    Call TimeStop
    Exit Sub
End If

'For all other users...timer is enabled.
   Call TimeStop
   Call TimeSetting
End Sub


Private Sub Workbook_Open()
Dim email As String
Dim sht As Worksheet
Dim LastRow As Long


'Check if anyone is currently using the workbook
    If Sheet2.Range("B4").Value <> "" Then
        MsgBox "Workbook is in use by " & Sheet2.Range("B4").Value & ".  Please try again later."
        ActiveWorkbook.Close Savechanges:=False
    Else
       Sheet2.Range("B4").Value = Environ("username")
       ActiveWorkbook.Save
    End If

'Start workbook auto-close timer
Call TimeSetting

'hide all sheets...at least one sheet must remain visible.
'Use VeryHidden to ensure users cannot simply unhide other sheets without going into VBA.
For Each sht In Worksheets
    If sht.CodeName <> "Sheet1" Then sht.Visible = xlSheetVeryHidden
Next

'determine email of current user
Dim objOutlook As New Outlook.Application
email = Trim(objOutlook.getnamespace("MAPI").currentuser.AddressEntry)

'Email = CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\ADUserName")
'allows opening of app on non-domain connected computer.
On Error Resume Next


LastRow = Sheet4.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
'Display sheet applicable to department
For x = 2 To LastRow
    If email = Sheet4.Range("A" & x).Value Then
        Worksheets(Sheet4.Range("B" & x).Value).Visible = True
    End If
Next x
'Select Case Dept
'    Case Is = "DataCenter Management"
'        Sheet2.Visible = xlSheetVisible
'    Case Is = "Deskside Support"
'        Sheet3.Visible = xlSheetVisible
'End Select

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "CurrentUser" Then
              ws.Protect Password:=”iwantin”, Userinterfaceonly:=True

             ws.EnableOutlining = True
    End If
Next ws

End Sub

Private Sub workbook_beforeclose(Cancel As Boolean)

'hide all sheets except message sheet'
'Use VeryHidden to ensure users cannot simply unhide other sheets without going into VBA.

    For Each sht In Sheets
        If sht.CodeName <> "Sheet1" Then
        sht.Visible = xlSheetVeryHidden
        End If
    Next sht

'clear current user so next user can access the sheet.
    Sheet2.Range("B4").Value = ""
   
End Sub

Thanks in advance!
Have a wonderful weekend

Linki

 

[Jon Peltier]

Describe what you mean by "it does not work". Does the code run or not? Does nothing happen when the code runs? Do you get an error message? If so, what is the message (not the error number, which may not be very helpful), and what line is highlighted when you click Debug in the error message?

I suspect the problem may be with Environ, but I am not at my own Mac to test this.

 

[Candyman8019]

that would make sense since that is looking for a windows environment variable. Is there an equivalent to get the username on a MAC?

 

[Jon Peltier]

I don't know off the top of my head. You could search Google for 'mac excel vba get user name'.

 

[Candyman8019]

Thanks Jon. Did a quick google search. @Linki, try replacing your workbook_sheetchange routine with this and see how things work for the MAC.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'if User logged in then don't auto-close
' NOTE:  Since this uses the Windows login name, make sure it is correct below.

'MAC
If Environ("USER") = "User1" Or Environ("USER") = "User2" Then
    Call TimeStop
    Exit Sub
End If

'Windows
If Environ("username") = "User1" Or Environ("username") = "User2" Then
    Call TimeStop
    Exit Sub
End If

'For all other users...timer is enabled.
   Call TimeStop
   Call TimeSetting
End Sub

 

[Linki]

Thank you both! Sorry for the slow reply!
And you're so right Jon! Don't know how I could've forgotten to attach that!
The error message I get is this:

 

[Jon Peltier]

I have had absolutely no joy trying to automate one Office program from another on a Mac. What works fine in Windows, and what worked fine in Mac Office 2011, fails miserably in current Mac Office versions.

 

[Jim Gordon]

Outlook on Mac does not support VBA at all. This page by Excel MVP Ron DeBruin explains how to automate Outlook on Mac:
Mail with Mac Outlook | Mac Excel Automation