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:
(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
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.
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.
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.
(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 PanePrivate 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.
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
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:
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