XL2BB - Excel Range to BBCode 2.1

[psycoperl]

Hi, @psycoperl

Thank you for reporting the issue.

The add-in has been tested on macOS Big Sur 11.1 without problems, and there are no known problems. Therefore I assume it is a system-specific problem in the host application settings and/or configuration/version, so we need to find out what is not compatible at your side to be able to find an answer or fix any possible bug in the add-in that causes the problem in that specific conditions.

Some questions that might help to narrow it down:

1. Are you using Office 365 on Mac? Although the add-in has not been tested on Office 2016 and 2019 versions, it should be working without problems in these Office versions but Office 2011 on Mac.
2. Is it the same specific range that you have tested the add-in? Have you tried a simple range without any formulas or specific ranges containing names, data validation, or conditional formatting?
3. Have you ever run the add-in successfully on Mac before the Big Sur upgrade? (Trying to make sure if it started with Big Sur upgrade).
4. Are you sure that you have the add-in installed only once, so you have only one ribbon tab called MrExcel?
5. Are you using the internal monitor only, or do you also have an external monitor attached to your MacBook?

Regarding your questions:

1. I am using the Excel of Mac that is provided by the university that I work for. According to the About Excel screen it has a Microsoft 365 License. (here is the About Excel Screen)

2. I have tried both the same and different ranges. I have tried ranges that are plain text, plain formatting, conditional formatting, contain formulas and a mix thereof. No difference. No popup window.
When I restart Excel, I get a message that a problem was detected with the add-on and Excel has disabled it.

3. I had issues with it in Catalina as well. In MacOS Big Sur the issues are just more prevalent -- possibly because I was trying to use it more.

4. Yes it is only installed once.

5. I have a dual monitor setup. I have tried it with Excel on both monitors. When I disconnected the second monitor, tried selecting a range on a blank new Excel file, the dialog/popup appeared.
Further testing: If I reconnected the external monitor, the dialog/popup then worked.

 

[psycoperl]

Regarding your questions:

1. I am using the Excel of Mac that is provided by the university that I work for. According to the About Excel screen it has a Microsoft 365 License. (here is the About Excel Screen)

View attachment 29000


2. I have tried both the same and different ranges. I have tried ranges that are plain text, plain formatting, conditional formatting, contain formulas and a mix thereof. No difference. No popup window.
When I restart Excel, I get a message that a problem was detected with the add-on and Excel has disabled it.
View attachment 29001

3. I had issues with it in Catalina as well. In MacOS Big Sur the issues are just more prevalent -- possibly because I was trying to use it more.

4. Yes it is only installed once.
View attachment 29003

View attachment 29002
5. I have a dual monitor setup. I have tried it with Excel on both monitors. When I disconnected the second monitor, tried selecting a range on a blank new Excel file, the dialog/popup appeared.
Further testing: If I reconnected the external monitor, the dialog/popup then worked.

Additional test - If ran from the external monitor it fails while running on Internal monitor works.

 

[smozgur]

Thanks for the answers.
Note: I removed the error logs you posted since they are not relevant other than the last page that shows the system information including the screen properties that you already provided. We don't prefer the detail of your personal configuration to be exposed in public.

5. I have a dual monitor setup. I have tried it with Excel on both monitors. When I disconnected the second monitor, tried selecting a range on a blank new Excel file, the dialog/popup appeared.
Further testing: If I reconnected the external monitor, the dialog/popup then worked.

So we are now sure that it is related to the dual monitor setup.

From your initial post:

if I use XL2BB from the MrExcel Ribbon > Capture Range causes the application to freeze, menus to be greyed out and any clicks in the UI results in error beeps.

This is the expected behavior. The user form is displayed, all menus are disabled, and if you click anywhere other than the user form or its controls then you hear the beep since it is a modal dialog window. This is all normal - except you don't see the user form and normally think the application freeze.

Since the VBA user forms are not entirely portable between Windows and Mac, there is a special procedure in XL2BB that resizes the user form and its elements if it is running on Mac, so it shows the user form at 100% scale successfully instead of 75% default when you run the same user form on Mac. The procedure also sets the location of the user form on the screen. Although my configuration is almost identical to yours with the dual monitor settings on Big Sur, I am not testing on MacBook Pro with a secondary monitor attached which I believe is the actual reason.

Could you please check the other monitor when you run the Capture Range?
If you still can't see the user form on the other monitor (assuming only the Excel application is working for testing purposes), please also try the following.
After you run the Capture Range (and the Excel application looks like frozen), right-click on the Excel application icon on the dock, and select the "Show All Windows" command, and check both screens.

You should see the Excel application window, and also the "missing" user form. Please let me know the result of this.

 

[psycoperl]

Thanks for the answers.
Note: I removed the error logs you posted since they are not relevant other than the last page that shows the system information including the screen properties that you already provided. We don't prefer the detail of your personal configuration to be exposed in public.


So we are now sure that it is related to the dual monitor setup.

From your initial post:


This is the expected behavior. The user form is displayed, all menus are disabled, and if you click anywhere other than the user form or its controls then you hear the beep since it is a modal dialog window. This is all normal - except you don't see the user form and normally think the application freeze.

Since the VBA user forms are not entirely portable between Windows and Mac, there is a special procedure in XL2BB that resizes the user form and its elements if it is running on Mac, so it shows the user form at 100% scale successfully instead of 75% default when you run the same user form on Mac. The procedure also sets the location of the user form on the screen. Although my configuration is almost identical to yours with the dual monitor settings on Big Sur, I am not testing on MacBook Pro with a secondary monitor attached which I believe is the actual reason.

Could you please check the other monitor when you run the Capture Range?
If you still can't see the user form on the other monitor (assuming only the Excel application is working for testing purposes), please also try the following.
After you run the Capture Range (and the Excel application looks like frozen), right-click on the Excel application icon on the dock, and select the "Show All Windows" command, and check both screens.

View attachment 29016

You should see the Excel application window, and also the "missing" user form. Please let me know the result of this.

The VBA form does NOT display on either monitor. When I go to the Show All Windows it shows up if I select it, it disappears. I also checked Mission Control and the same behavior of showing up but disappears when clicked on.

As an image is worth 1000 words and a video worth many more... I have screen captures of the behavior both hosted on a streaming service similar to YouTube and for download via Google Drive of higher-res files. (please note the timestamp in the upper right corner for "syncing")

Download: View External Monitor Screen Capture
Download: View Internal Monitor Screen Capture

 

[smozgur]

@psycoperl, thank you for testing and also for the videos.

It is clear that the userform is somehow hiding in the off-screen area of the internal monitor - it rarely happens to me for different applications. I will test the add-in in MacBook Pro with an external monitor attached to be able to reproduce the same behavior and try to adapt the application. Hopefully, it does the same thing on my computer that will prove that it is not a very unusual case depends on your configuration.

If I can't get the same result, and it is also convenient for you, then I would like to send you a slightly modified version of the add-in for testing purposes to find a solution.

I'll try to revisit this discussion by Tuesday. Thanks again for reporting the issue and for the help you provided so far.

 

[psycoperl]

@psycoperl, thank you for testing and also for the videos.

It is clear that the userform is somehow hiding in the off-screen area of the internal monitor - it rarely happens to me for different applications. I will test the add-in in MacBook Pro with an external monitor attached to be able to reproduce the same behavior and try to adapt the application. Hopefully, it does the same thing on my computer that will prove that it is not a very unusual case depends on your configuration.

If I can't get the same result, and it is also convenient for you, then I would like to send you a slightly modified version of the add-in for testing purposes to find a solution.

I'll try to revisit this discussion by Tuesday. Thanks again for reporting the issue and for the help you provided so far.

I would be willing to help you debug and test this project.

While I do not use it alot, when I do it is extrememly valuable when trying to illustrate the my excel deliemas.

On a side note, I was also wondering, does the BBCode output work on other forums that run BBCode or is it a customization exclusinve to the myExcel forum?

 

[RoryA]

It's exclusive to MrExcel, so won't work on other XenForo sites such as Chandoo, I'm afraid.

 

[smozgur]

I would be willing to help you debug and test this project.

@psycoperl - Thank you.

I'll be able to test this in a MacBook Pro by the weekend earliest and let you know if we will need further testing on your computer.

 

[psycoperl]

@psycoperl - Thank you.

I'll be able to test this in a MacBook Pro by the weekend earliest and let you know if we will need further testing on your computer.

OK

 

[smozgur]

OK

@psycoperl - I tested the add-in in my MacBook with dual monitor settings, found the issue, and also realized why I can't reproduce the same behavior on iMac with dual monitor.

The following screenshot shows my display arrangements on my desktop environment:

However, when I am using the MacBook Pro with the external monitor, it is something like the following since I am placing the external monitor in a higher position to be able to see it behind the MacBook screen:

There is a special routine in the code that relocates the user form considering the dual-monitors to avoid it to be displayed in the primary monitor if the application window is being used in the secondary monitor. Therefore, the user form is being moved but displayed in the blank area just below the external monitor in this arrangement.

There is no way (or at least a workaround that I am aware of) to calculate this difference between screen top-left coordinates in VBA. Even internal applications like the screenshot tool that remembers the previously selected area messes up when the screen vertical positions are set to be different. I am not sure if it also fails in Windows as well and It is kind of possible to find a workaround by using various API methods in Windows, but this is not in the XL2BB development plans since it should support Windows and Mac platforms at the same time.

However, I am happy to say that the next XL2BB update will also solve this dual monitor issue. Until then, as a workaround that I believe you already figured out, you can launch Excel in the primary monitor, and set the additional information options as you wish (likely selecting all checkboxes), and then you can use the context menu command button to generate mini-sheets.

Thanks again for bringing this up, and for all your help. You'll already receive a notification when the new version is released, however, I will also post here about it.