Macro: Open native dialog box in center of Excel

[centerdialog]

I am trying to make an Excel macro that opens the worksheet "Activate" dialog box (normally opened by right clicking on the navigation arrows in the bottom left of the workbook) in the center of the Excel workbook (must work for a multi-monitor set up).


The below code (from: https://www.mrexcel.com/forum/excel-questions/5268-macro-choose-worksheet-view-all-wor-3.html) is just about the most elegant way to open the "Activate" dialog box. I would like to amend this code so that the native dialog box will always open in the center of the Excel workbook no matter its size or which monitor Excel is running in. I do not want to make a Userform or Msgbox for this.


Unfortunately, I do not know how to code or where to begin with making this amendment. Would anyone be able to help or does anyone have any idea how this would be done? Thanks very much.



<code class="yklcuq-7 iRRQrr">x = ActiveWorkbook.Sheets.Count
If x > 16 Then
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
Else
Application.CommandBars("Workbook Tabs").ShowPopup
End If</code>

 

[Jaafar Tribak]

Okay, if you happen to have some spare time, please do let me know (I haven't been able to test to improved flicker speed with the shortcut yet). Also, if you happen to know a method to call the Activate dialog box no matter how many worksheets there are (I don't think it's possible, as the xldialogbox Activate command opens the workbooks rather than the worksheets, refer to https://docs.microsoft.com/en-us/off...argument-lists), please let me know.

Not sure I understand ... Which xldialogbox are you refering to ? If you can rephrase the question

I'm sorry, I was unable to get the shortcut to work. I tried to place the provided code in a new ThisWorkbook module and even in the module which holds the actual code (even tried various locations within this module), but neither seemed to work. Should I rename the shortcut code so that it matches the name of the of the original macro[i.e., Public Sub Center_Sheets_List_Dialog ( ) vs. Private Sub Workbook_Activate ( )]? Regarding the macro, could you explain what the purpose of the Deactivate ( ) line is, given that I can close both the Commandbar and dialog box using ESC?

Application.OnKey "+^{W}", "Center_Sheets_List_Dialog"
simply assigns the Shif + Ctrl + W keys combination to the Center_Sheets_List_Dialog Macro when the workbook is first opened or activated.

Application.OnKey "+^{W}"
Resets the key combination to its default behaviour when the workbook is closed or whenever the user activates another workbook because I was assuming you only wanted this to apply to the workbook containing the code.

Just to be clear, the intent of this entire exercise is to save the macro to the Personal.xlsb file so that I can run the macro using the shortcut on any excel file I open from my computer. Will ultimately not save the final code(s) to ThisWorkbook.

That's doable - No problem .

 

[centerdialog]

Once I get the shortcut working I will see how much the flicker has improved. If you have any time, please do see if it's possible to get rid of the flicker entirely.

Regarding the link, I am referring to the first dialog box: xlDialogActivate. This command calls the same Activate window that we have been working with but instead of listing the worksheets, it lists all the open Excel workbooks. Reading the entire list of dialog boxes in the website led me to conclude that it is not possible to open the worksheet Activate dialog box using any of those commands. Thus, the code that I first posted in the original post. Am I mistaken? Is it actually possible to call the Activate dialog box for the worksheets without using the method I first posted? Right clicking the navigation arrows in the bottom left of Excel even when there are fewer than 16 worksheets will open the dialog box which has me wondering if the Commandbar is even necessary.


I see. Yes, I am not interested in resetting the key combination. I want the macro to forever be assigned to the designated shortcut across all Excel files opened on my computer. Should I use a different code to accomplish this?

Thanks for your help again.

 

[Jaafar Tribak]

The flicker that I get is hardly noticeable and personally I don't see much of a problem there.

AFAIK, it is not possible to open the worksheet Activate dialog box using any of those commands.

You connot even use the DialogBoxParam API to load and hook the dialogbox because excel's built-in DialogBoxes are not propper windows dialogs.

The one thing you can do as previously sugggested, is to replicate the excel built-in worksheet activate dialog with a propperly designed UserForm that looks and behaves the same... That would be the easiest method and would work regardless of the number of worksheets and would cause no screen flicker.

 

[My Aswer Is This]

I wonder why you are opposed to a Userform

A Userform on Opening could load all the sheet names into a Combobox.
When you choose the sheet you want activated that sheet would be activated and the Userform would automatically close.

This Userform could be loaded into your Personal Workbook making this work on any workbook with just one small script in your Personal workbook.
Like: Userform1.show

And would always include all sheets in the active workbook.
Script would be run with a shortcut key.

Here is the script you would need:

Private Sub ComboBox1_Change()
'Modified  10/6/2018  9:29:21 AM  EDT
Sheets(ComboBox1.Value).Activate
Unload Me
End Sub
Private Sub UserForm_Initialize()
'Modified  10/6/2018  9:29:21 AM  EDT
Dim i As Long
    For i = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(i).Name
    Next
End Sub

 

[centerdialog]

I managed to assign the shortcut to the macro (after saving the workbook and opening it again). The shortcut works perfectly for the Commandbar, but when the Activate dialog box is opened using the macro, it looks perfectly normal, but does not move to the selected worksheet after pressing enter or clicking OK. That is, the shortcut calls the dialog box, but the dialog box does not seem to do its job once visible. Would you mind confirming if you have this issue as well?

Edit: assigning a shortcut through the "Options" method using Alt + F8 also has the same results for me.


Also, I have successfully created the Personal.xlsb file and saved the macro to a module in that file. But if I would like the shortcut to also carry over across all workbooks opened on my computer, where should I save the shortcut code? I tried to save it to the ThisWorkbook within the Personal.xlsb file but it doesn't seem to work.

Thanks everyone.

 

[Jaafar Tribak]

1-

the shortcut calls the dialog box, but the dialog box does not seem to do its job once visible. Would you mind confirming if you have this issue as well?

Yes, I could replicate the problem ... Strangely enough, the issue doesn't happen when executing the code from button - Only happens if calling the dialog box via the short cut keys.

That looks like a very weird bug !

Fortunately, I have figured out a simple hack to fix the issue .. It seems that by just holding the code execution for 1 second via the use of the Wait Method before calling the dialog box solves the problem.

Change in red :

Public Sub Center_Sheets_List_Dialog()

    If ActiveWorkbook.Sheets.Count <= 16 Then
        SetTimer Application.hwnd, Application.hwnd, 0, AddressOf SetListPos
        SendMessage GetAncestor(Application.hwnd, GA_PARENT), ByVal WM_SETREDRAW, ByVal 0&, 0&
        Application.CommandBars("Workbook Tabs").ShowPopup
    Else
        [COLOR=#ff0000][B]Application.Wait Now + TimeSerial(0, 0, 1)[/B][/COLOR]
        SetTimer Application.hwnd, 0, 0, AddressOf SetListPos
        Application.CommandBars("Workbook Tabs").Controls(16).Execute
    End If
    
End Sub

2-

Also, I have successfully created the Personal.xlsb file and saved the macro to a module in that file. But if I would like the shortcut to also carry over across all workbooks opened on my computer, where should I save the shortcut code? I tried to save it to the ThisWorkbook within the Personal.xlsb file but it doesn't seem to work.

You should set the shortcut keys in the Open and Close events of the Personal workbook instead of the Activate and Deactivate events becuase the Personal workbook is supposed to be behind the scenes and never gets activated pr deactivated.

Try this in the ThisWorkbook Module of the Personal workbook:

Private Sub Workbook_Open()
    Application.OnKey "+^{W}", "Center_Sheets_List_Dialog"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "+^{W}"
End Sub

 

[centerdialog]

Yes, this is very peculiar, the issue also doesn't occur if the macro is run from the Alt + F8 window. Could the Application.OnKey assignment be causing this? The one second delay is a great workaround, but it is quite disruptive and the lowest I could get the timer to was 0.55 while still allowing the macro to work.



I see, thanks for the help. Yes, I have the shortcut properly working in the Personal.xlsb now. Thanks again for all of your help.

 

[centerdialog]

I'm seeing now that the problem is caused by the code that I posted in the original post. That is, executing "More sheets..." will cause the dialog box to appear but it will not function properly. Is there a way to fix this line of code without adding the delay timer?


If not, perhaps the best approach would be to relocate the cursor to the bottom left of the active Excel workbook, execute a right click, center the Active dialog box, and more the cursor back to its previous location. How practical would such a macro be?

 

[My Aswer Is This]

I still think my Userform suggestion would be a lot easier.

 

[Jaafar Tribak]

I'm seeing now that the problem is caused by the code that I posted in the original post. That is, executing "More sheets..." will cause the dialog box to appear but it will not function properly. Is there a way to fix this line of code without adding the delay timer?

If not, perhaps the best approach would be to relocate the cursor to the bottom left of the active Excel workbook, execute a right click, center the Active dialog box, and more the cursor back to its previous location. How practical would such a macro be?

The introduced delay time can go as low as 0.55 secs - Is that still causing you an issue ? In my testings , the delay is hardly noticeable.

Executing a right-click on the bottom left of the activeworkbook window to invoke the Dialog sheets is doable but will most probably worsen the flicker issue. In addition, this approach assumes that the worksheet tabs bar is visible which is not always necessarly the case in which case, one will first have to display the worksheet tabs, execute the code and then hide it back again... That's ugly coding.

I'll however revisit the code and see if I can figure out a way of eliminating the need for introducing the brieve delay

Regards.