Excel Document-level customization - Setting width of Action Pane (VB.net 4.8)

Steffen Pedersen 1 Reputation point
2023-01-06T16:43:10.667+00:00

I am trying create a simple Action Pane Control in a Document-level VSTO, writting in VB.Net. [.net 4.8]

With the code I have now I open the Action Pane on Sheet1 and hides it on other sheets.

The issue I can't find a solution to is to set the width of the action pane and its control - It is simple to narrow as default.
Every time the workbook is opened, the action pane opens with the default widt, so every single time you have to start out by resizing the action pane. That is an annoyance.

Example image:

276890-axtion-pane-width.jpg

Every topic I have found on this refers to setting the width as this ( link: actions-pane):

Me.CommandBars("Task Pane").Width = 200  

or

Application.CommandBars("Task Pane").Width = 200  

My code for sheet1:
(apcWareList is the Action Pane Control)

 Imports Microsoft.Office.Interop.Excel  
      
    Public Class Sheet1  
        Private tpWareList As New apcWareList  
  
Private Sub Sheet1_Startup() Handles Me.Startup  
      
            Globals.ThisWorkbook.ActionsPane.Controls.Add(tpWareList)  
      
            Application.CommandBars("Task Pane").Width = 500  
            Globals.ThisWorkbook.Application.DisplayDocumentActionTaskPane = True  
      
      
        End Sub  
      
        Private Sub Sheet1_Shutdown() Handles Me.Shutdown  
      
        End Sub  
      
        Private Sub Sheet1_Deactivate() Handles Me.Deactivate  
      
            Globals.ThisWorkbook.Application.DisplayDocumentActionTaskPane = False  
        End Sub  
      
        Private Sub Sheet1_ActivateEvent() Handles Me.ActivateEvent  
            Globals.ThisWorkbook.ActionsPane.Controls.Add(tpWareList)  
      
            Globals.ThisWorkbook.Application.DisplayDocumentActionTaskPane = True  
      
      
        End Sub  
    End Class  

Anyone who can spot my error?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,063 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Steffen Pedersen 1 Reputation point
    2023-01-11T15:56:11.02+00:00

    #Not_*Impressed_*Microsoft

    If I throw in this line:

       MsgBox(Application.CommandBars("Task Pane").Name)
    

    I get a prompt with the text "Document Actions", which funny enough is the one I need to update the width of..

    But the line from any documentation states that Application.CommandBars("Task Pane").Width = 800 should update the width. Which is not happening.

    Is there any settings in Visual studio that needs to be corrected? any imports that must be included?

    0 comments No comments

  2. Артур Набиуллин 0 Reputation points
    2024-05-15T13:07:00.4366667+00:00

    Hello! I encountered the same problem and found a way to set the width:

    First you need to attach the ActionsPane to the left side of the window

    Application.CommandBars("Task Pane").Position = msoBarLeft
    

    then set the width

    Application.CommandBars("Task Pane").Width = 800
    

    then attach it back to the right side

    Application.CommandBars("Task Pane").Position = msoBarRight
    

    It works!

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.