You are here How to use BSTaskPaneX and BSTaskPane

How to use BSTaskPaneX and BSTaskPane

Task Pane is a soft window located on the 4 sides of the Excel window or floating. Programming VBA to create it combines embedded Userform to help you get a beautiful and professional user interface. This is a detailed tutorial for you to know how to do from scratch with BSAC to create Task Pane.

 
Loading BSAC controlsBefore programming with BSAC, the first thing you need to know is to import controls into the Toolbox window.
Step 1: Open any Userform,
Step 2: Click on the Userform Toolbox window that appears, perform the manipulations as shown below:
alt

 

(1) Right-click on the "Controls" tab in the Toolbox window, select "Import page" (2), look in the folder of A-Tools or the SetupBSAC library to unzip, select the file "ImportToToolbox.pag" (3 ), finally select Ok (4) to finish.
 
Create Task Pane with Userform with two methods BSTaskPaneX and BSTaskPane
1. Using BSTaskPaneX
Embed control BSTaskPaneX

alt

Drag the BSTaskPaneX control from the Toolbox to the Userform. Click this control on the Userform and then look at the Properties Window, click "Custom..." to enter information for BSTaskPaneX.

alt

On the window that opens: Text is the name of the required Task Pane, other items are ignored.
 
Next, click on the Userform, in the Properties window, edit ShowModal = False . This specification is required for any method of creating Task Pane.
 
Conduct programming in the UserForm_Initialize() event

Private Sub UserForm_Initialize() 
Case 1: Use BSTaskPaneX 
   BSTaskPaneX1.AllowHide = False  'Prevent use from clicking close [X]
   BSTaskPaneX1.Create Me, , ActiveWindow 
End Sub 

Private Sub cmdClose_OnClick() 
   Unload Me 
End Sub 

Thus, you have successfully created the Task Pane and embedded the Userform inside.
Press F5 to run Userform or you create Module to write macro to open Userform1
 
Go to menu Insert -> Module, now write code to open UserForm

Sub ShowForm1() 
   UserForm1.Show 
End Sub 

Place the cursor inside the ShowForm1 macro, press F5 to run the Userform, or you assign this macro to the command menu, command button.

alt

It's great but it's so simple!
 
Create task pane with class BSTaskPane
 
This is the standard way, we encourage you to follow this method because of its portability.
 
Embed BSAC library in Excel file
The first thing you have to do is make sure the BSAC library is embedded in your Excel file (VBAProject). Normally, when you drag any of the BSAC and Userform controls, the BSAC library is already embedded in this Excel file. If you are not using any BSAC control on the UserForm, but only using BSAC classes, you must do the embedding as shown below.

alt

(BSAC.ocx file selection window, select file type "Activex Controls")

Note: Office 32-bit, select BSAC.ocx 32-bit, in the folder "C:\Windows\SysWow64"; Office 64-bit, select BSAC.ocx 64-bit, in the folder "C:\Windows\System32".
 
Write code to create Task Pane using BSTaskPane
 
Create a module (if you don't have one or need a new one to store your own block of code)

Option Explicit 
Private WithEvents TP As BSTaskPane  'for Case 2:
Private Sub UserForm_Initialize() 
   'Case 1: Use BSTaskPaneX
   'BSTaskPaneX1.AllowHide = False 'Prevent use from clicking close [X]
   'BSTaskPaneX1.Create Me, , ActiveWindow
   'Case 2: Use BSTaskPane
   Dim TPs As New BSTaskPanes 
   Set TP = TPs.Add("My Task Pane 1", Me, Window:=ActiveWindow) 
   TP.AllowHide = False 
   Set TPs = Nothing 
'SinkControl:
   ' + Userform (Me)
   ' + Handle to window
   ' + ClassName:Title (if only find class name then "ClassName:"
   ' + Title
   'Class and Title of appilcation use "Spy++ tool" or other to find
End Sub 
 Private Sub cmdClose_OnClick() 
   Unload Me 
End Sub 
Đoạn code dưới đây có thể bỏ qua nếu không lập trình sự kiện với Task Pane
Private Sub TP_OnDockChange(ByVal BSTaskPaneInst As BSAC.BSTaskPane) Debug.Print "TP_OnDockChange: " & BSTaskPaneInst.DockPosition End Sub Private Sub TP_OnResize(ByVal Width As Long, ByVal Height As Long) Debug.Print "TP_OnResize: " & Width & "x" & Height End Sub Private Sub TP_OnVisible(ByVal BSTaskPaneInst As BSAC.BSTaskPane) Debug.Print "TP_OnVisible: " & BSTaskPaneInst.Visible End Sub

That's it. Now run/open Userform to see the result.
alt

If you want when scaling the Task Pane but the controls in the Userform change, then program it in the UserForm_Resize() event.
 
Private Sub UserForm_Resize() 
   'Resze any controls if you want
   On Error Resume Next 
   YourControl.Width = Width - YourControl.Left * 2 
End Sub 

So I have detailed instructions on two ways to create Task Pane and embed Userform in it.
 
BASIC COMPLETED!
 
Extensive knowledge of standard Task Pane creation and extensive examples
 
Create a second Userform and draw the controls as shown below

alt

The controls embedded in the Userform include: BSButton, BSImageList, BSDateSelector, BSDateTimePickerm SpinButton, BSImage, Label. (BS* are controls that belong to BSAC).
 
The code to interact with the controls in the Userform is sent by me according to the attachment you download and see. In this article, I only focus on analyzing the code related to the Task Pane.
 
Create a module, copy the code block that creates and controls the standard Task Pane below.
Option Explicit 
'Author: Nguyen Duy Tuan - https://bluesofts.net
' duytuan@bluesofts.net
'Facebook: https://www.facebook.com/groups/hocexcel
'This code for BSAC v3.1
'Check version of BSAC: F5 to open window "Immediate"
'?BSACVersion
'Enter
Sub CreateTaskPaneAndCheck() 
   Dim TPs As New BSTaskPanes 
   Dim TP As BSTaskPane 
   Dim idx As Long, Title As String 
   Title = "My Task Pane 2" 
   idx = TPs.IndexOf(Title, ActiveWindow)  'Find exits task pane?
   If idx >= 0 Then  'Found
      'Get task pane in task pane store
      Set TP = TPs(idx) 
   Else 
      'Create the new task pane
      Set TP = TPs.Add(Title, UserForm2, False, Window:=ActiveWindow) 
      TP.AllowHide = False 
   End If 
   Set TPs = Nothing 
End Sub  
Sub CreateTaskOnNewWindow() 
   Dim TPs As New BSTaskPanes 
   Dim TP As BSTaskPane 
   Dim Wb As Workbook, Title As String 
   Set Wb = Workbooks.Add  'Create new workbook
   Title = "Workbook: " & Wb.Name 
   'Create task pane on the new workbook
   Set TP = TPs.Add(Title, , False, Window:=Wb.Windows(1)) 
   TP.AllowHide = False 
   TP.CTP.Width = 350 
   Set TPs = Nothing 
End Sub  
Sub ClearAllTaskPanesInWindow() 
   Dim TPs As New BSTaskPanes 
   TPs.Clear ActiveWindow 
   Set TPs = Nothing 
End Sub 

Sub ClearAllTaskPanes() 
   Dim TPs As New BSTaskPanes 
   TPs.Clear 
   Set TPs = Nothing 
End Sub 

Ok. Now attach the macros to the menu or command buttons and run, you will get the interface as below:

alt

It's great that when programming VBA combined with BSAC you have a beautiful and very pro interface. Without BSAC, pure code in VBA would be very, very difficult.
 
Download the example file
 
BSAC is an activex control with only one file that needs to be installed on the computer "BSAC.ocx". If you install Add-in A-Tools, BSAC is automatically installed. If you only want BSAC in your computer, read the detailed instructions on how to install it in the BSAC installer.
Author: Nguyen Duy Tuan