Share via

Setting focus back to worksheet from modeless UserForm on Excel for Mac

Anonymous
2021-04-22T18:03:44+00:00

I have a macro-enabled workbook that is used on both Windows & Mac.  (Probably 80% of my users are on Mac.)  I'm using the lastest version (16.48) of Excel on the Mac (part of Office 365).

I just added a UserForm that I show as vbModeless.  When a user presses a button on it, I want the focus to shift back to the ActiveSheet ("Sheet1").

I added a line of code at the end of the button click sub:

AppActivate Excel.ThisWorkbook.Application.Caption

This works perfectly on Windows.  On Mac, however, it causes the following error:

Run-time error 5: Invalid procedure call or argument

My first question is, does AppActivate work on the Mac at all?  If yes, any ideas why the line of code would be causing an error?  Do I need to change anything about it to make it work on the Mac?

Assuming AppActivate doesn't work on the Mac, I have tried to come up with an alternate way of doing the same thing.  I tried some AppleScript, like so:

Dim myScript As String

myScript = "tell application " & Chr(34) & "Microsoft Excel" & Chr(34) & Chr(10) & _

            "tell worksheet " & Chr(34) & "Sheet1" & Chr(34) & " of active workbook" & Chr(10) & _

            "activate" & Chr(10) & _

            "end tell" & Chr(10) & _

            "end tell"

MacScript (myScript)

To make it easier to read, here is what myScript actually looks like:

tell application "Microsoft Excel"

  tell worksheet "Sheet1" of active workbook

    activate

  end tell

end tell

Unfortunately, this code doesn't set the focus back to the worksheet; it stays on the UserForm.  Does anyone know of a way to alter this code to make it set the focus to the worksheet?

Thanks for any help you can offer!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-04-24T23:19:04+00:00

Thanks for all your efforts, Bernie and Eduardo.  Unfortunately neither suggestion worked for what I am trying to do.

I reached out to a Mac Excel VBA "expert" I know.  He has a way of getting in touch with the developers at Microsoft, so he's alerted them to this problem.  He believes it's a bug in Excel for Mac, so hopefully in a future update of Excel, it will be solved.  Unfortunately, since Microsoft isn't updating Excel 2016 for Mac anymore, any update will only affect 2019/365.

Was this answer helpful?

0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-04-22T22:24:38+00:00

    Hi Eduardo,

    My workbook is really large, and it has some sensitive information on some of the sheets.  However, I made a test workbook with just a "Sheet1" and a button.  I used the same code that is causing me the error on Mac.

    I do not know how to attach a file to this forum post, so I put it on my Dropbox.  You can download it here:

    https://www.dropbox.com/s/ivoynrpc3gbo0w3/TestWorkbook1.xlsm?dl=0

    If you open the file on Excel for Windows and press the button, it will return the focus to "Sheet1", just as it is supposed to.  If you try it on Excel for Mac, it will cause a run-time error.

    The line of code that is causing the error is:

    AppActivate Excel.ThisWorkbook.Application.Caption

    If you can help me adjust the code so it works on Mac, that would be most appreciated. 

    Thank you!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-04-22T21:44:50+00:00

    Hi Bernie,

    I tried ThisWorkbook.Worksheets("Sheet1").Activate, but unfortunately that has no effect.

    As for the UserForm_Terminate(), the UserForm is not being terminated yet.  Basically, my UserForm has several buttons on it where the user must click one, and it records the address of the ActiveCell.  Every time they click one of the buttons, I want the focus to go back to the ActiveSheet so they can press one of the arrow keys to easily move to another cell.  But without the focus going back to the ActiveSheet, when they press the arrow keys, it moves around the buttons on the UserForm.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-04-22T20:27:10+00:00

    Hi mcomp72

    My name is Eduardo. I am an independent consultant, first of all thanks for being part of the Microsoft Community, I will try to help you.

    The AppActivate works on MAC, there is a tutorial about in:

    https://docs.microsoft.com/en-us/office/vba/lan...

    About your code, if you wish you can share with me your file and I will try to fix your code.

    I hope this information is useful for you. If you still have questions, answer here so I can continue helping you.

    Sincerely,

    Eduardo

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-04-22T19:35:00+00:00

    Try using code like

    ThisWorkbook.Worksheets("Sheet1").Activate

    or use the userform's event code, like:

    Private Sub UserForm_Terminate()

        'code

    End Sub

    where the code is something like

    Workbooks("BookName.xlsm").Activate

    ActiveWorkbook.Select

    Was this answer helpful?

    0 comments