Làm Menu đa lớp trong Excel!

[paulsteigel]

Có thể chủ đề này hơi cũ rồi nhưng mình xin mạn phép chia sẻ với các bạn một cách nho nhỏ để tạo Menu nhiều lớp bằng VBA trong Excel nhé.
Mình định viết chi tiết một chút nhưng do thời gian hạn hẹp nên chỉ xin phép đăng đoạn Code và file ví dụ để mọi người tiện tham khảo.
Giờ có thêm chút thời gian ngẫm nghĩ, mình xin viết cụ thể một chút.
Thực ra ý tưởng này bắt nguồn từ việc xem các menu quản lý Web của Joomla (một phần mềm nguồn mở quản trị nội dung Web khá nổi tiếng)
Cách tiếp cận của Joomla khá đơn giản, chỉ là mối quan hệ cha-con và thứ tự sắp xếp của các chỉ mục của Menu.
Giải thuật của mình đơn giản thế này thôi
1. Tạo một bảng hoặc một vùng để quản lý thực đơn trong đó có một số điểm quan trọng như sau:
+ Chỉ mục thực đơn (thường quản lý bằng cơ chế Autonumber tức là tự động tăng khi thêm dòng).
+ Số chỉ mục của Menu Cha (ở đây sẽ lưu giữ số thẻ chỉ mục của thực đơn cha). Nếu chỉ mục này rỗng thì đó là thực đơn cấp cao nhất.
+ Tên thực đơn (thực ra dịch thế không đúng, nó phải là tiêu đề thực đơn - Caption mà). Tất nhiên để tùy biến về ngôn ngữ người ta cũng có thể thêm trường quản lý ngôn ngữ chẳng hạn Caption_E cho tiếng Anh, Caption_V cho tiếng Việt.
+ Hành động: đây là dữ liệu quy định mục thực đơn đó sẽ kích hoạt thủ tục/ hàm nào khi được chọn.
+ Tag, đây là một trường phụ ghi chép lại các thông tin bổ sung cho mục thực đơn nếu bạn cần, các nội dung của trường này sẽ được ghi vào thuộc tính Tag khi menu được tạo ra.
+ Ngoài ra còn một số trường khác quy định đặc tính, kiểu hiện thị của thực đơn nữa. Nhưng những nội dung trên đã là quan trọng nhất để tạo ra một thực đơn có thể làm việc được rồi đấy.
+ Đôi khi người ta còn bổ sung kiểu thực đơn - dạng nút/ thực đơn con hoặc là Hộp chọn ... nhưng thường thì phần bổ sung này dễ làm nảy sinh lỗi vì người dùng có thể quên lại cho thuộc tính của một thực đơn phụ là msoControlButton chẳng hạn trong khi nó phải là msoControlPopup. Vì thế dân có kinh nghiệm thì hay xử lý phần này trong Code hoặc kiểm soát các đặc tính này rất kỹ.
2. Sau khi đã có bảng, giờ là lúc tạo thực đơn.
Tất nhiên bạn cần phải quyết định là muốn tạo thực đơn hay thanh công cụ, cú pháp và giải thích cụ thể, xin xem trong bài số 3 của mục này.
Cách tạo thực đơn cũng khá đơn giản, với cách này bạn có thể làm rất nhiều thực đơn đa lớp mà không cần biết nó thuộc lớp 1 hay 2 hay 3.
Cách thực thi cụ thể như tôi giải thích trong phần code dưới đây.
3. Một vài gợi ý:
Nếu bạn phải viết một ứng dụng lớn có phân cấp người dùng và phân cấp ứng dụng (chẳng hạn có nút Open/New) bạn muốn dùng chung trong nhiều ứng dụng trong khi nút Print chỉ hiển thị trong một số ứng dụng khác trong khi đó bạn ngại không muốn tạo nhiều thực đơn. Một trong các gợi ý là sử dụng thẻ Tag trên đây nhưng lưu ý dữ liệu lưu vào thuộc tính tag của thực đơn phải được chuẩn hóa.
Sau này bạn chỉ cần đặc tả tham số tìm kiếm cho cú pháp FindControl là có thể tùy biến việc hiển thị hay tắt hiển thị các mục thực đơn một cách dễ dàng.
Chẳng hạn trong thẻ tag tôi ghi như sau 10_NA thì nó sẽ được dịch ra là đây là thực đơn con của thực đơn có thẻ index là 10 và nó chỉ hiển thị khi Người dùng là bình thường/Normal và người dùng là A/Advanced. Đấy chỉ là các cách tùy biến của dân lập trình thôi.
Đây là Code trong Module

Option Explicit

Sub CreateMenu()
    ' Khai bao cac bien chua Command bar truoc
    Dim myMnuBar As CommandBar
    Dim MuMnuButton As CommandBarControl
    Dim mnuTemp  As CommandBarControl
    Dim tblMenu As Range
    Dim MnuType As MsoControlType
    
    ' Khai bao cac bien dem
    Dim i As Long
    
    ' Bay gio doc du lieu tu vung dang chon voi cac header nhu sau cho de nho
    ' Menu ID Parrent ID  Caption Action  Tag
    Set tblMenu = Selection
    
    ' 1. Khoi tao Thanh menu tam thoi, khi thoat excel thi het
    If MenuBarExist("mnuBarTest") Then
        'neu menubar nay da ton tai - xoa ngay
        Application.CommandBars("mnuBarTest").Delete
    End If
    
    ' Gio thi tao menu bar
    Set myMnuBar = Application.CommandBars.Add("mnuBarTest", , , True)
    ' Hien thi no nao
    myMnuBar.Visible = True
    ' Tao cac menu con
    With myMnuBar
        ' Phai bat dau tu 2 vi ta co dong 1 la header
        For i = 2 To tblMenu.Rows.Count
            ' Kiem tra truong ParentID
            If tblMenu.Cells(i, 2) <> "" Then
                ' Day la menu con
                ' Tuy nhien phai kiem tra xem no se co the con co menu con nao nua khong nhe
                ' Sau do thi xac dinh kieu menu do
                MnuType = GetMenuType(tblMenu, 2, tblMenu.Cells(i, 1).Value)
                ' vi mnu co cac mnu con nen phai gan cho no mot gia tri phu nhe
' Nhớ tham số cuối là True nhé - điều này quan trọng nếu không thì sẽ không tìm được đúng mục thực đơn ta cần
                Set mnuTemp = myMnuBar.FindControl(, , tblMenu.Cells(i, 2).Value, , True)
                If mnuTemp Is Nothing Then
                    ' Khong tim thay menu do, chan qua - bo qua va bao loi
                    GoTo ExitSub
                Else
                    Set MuMnuButton = mnuTemp.Controls.Add(MnuType, , , , True)
                End If
            Else
                ' Day la lop menu tren cung, khoi tao
                Set MuMnuButton = .Controls.Add(msoControlPopup, , , , True)
            End If
            ' Thiet lap Caption cho Menu
            MuMnuButton.Caption = tblMenu.Cells(i, 3).Value
            ' Gio nho lai cai tag cua control nay nhe
            MuMnuButton.Tag = tblMenu.Cells(i, 1).Value
        Next
    End With
ExitSub:
    Set mnuTemp = Nothing
    Set MuMnuButton = Nothing
    Set myMnuBar = Nothing
End Sub

Private Function MenuBarExist(MenuBarName As String) As Boolean
' Hàm này xác định xem menubar này có tồn tại không thế.
    Dim myMnuBar As CommandBar
    Dim mnuCount As Long
    On Error GoTo ErrHandler
    Set myMnuBar = Application.CommandBars(MenuBarName)
    mnuCount = myMnuBar.Controls.Count
    MenuBarExist = True
ErrHandler:
End Function

Private Function GetMenuType(DataRange As Range, SearchColumn As Long, MenuTag As String) As MsoControlType
    ' Thu tuc nay xac dinh kieu menu bang cach tim xem co thay menu nao goi no bang cha khong
    Dim SearchRange As Range
    Set SearchRange = DataRange.Range(DataRange.Cells(2, SearchColumn), DataRange.Cells(DataRange.Rows.Count, SearchColumn))
    If GetRangeValue(SearchRange.Find(MenuTag)) <> "" Then
        ' Kieu co the co menu con
        GetMenuType = msoControlPopup
    Else
        ' Kieu khong co menu con
        GetMenuType = msoControlButton
    End If
    Set SearchRange = Nothing
End Function

Private Function GetRangeValue(MyRange As Variant) As String
    ' Ham nay de doi gia tri cua rang thanh dang text de ham thuc hien dung
    On Error Resume Next
    GetRangeValue = MyRange.Value
End Function

Bạn tải file đính kèm và nhớ điều chỉnh chế độ Macro và xem thử.
Nếu có thời gian mình sẽ viết chi tiết.

 

[phamduylong]

Để tham khảo nhiều cách tạo menu, ngoài cách đã làm, paulsteigel có thể thêm cách tạo menu trên menu bar vì nó thông dụng hơn ?

 

[paulsteigel]

Tạo Menu!

Em xin cảm ơn bác Phạm Duy Long rất nhiều về yêu cầu vừa rồi ạ!.
Em xin được tham gia thêm như sau:
Chúng ta phân biệt thế này:
Commandbar thực tế là bao gồm cả Menubar Và Toolbar chỉ khác là kiểu điều khiển trên đó là gì. Tuy nhiên, khi tạo Menu bar, ta cần xác định xem đối tượng cần tạo đó là menu hay toolbar nhé, cú pháp như sau:

Application.CommandBars.Add("mnuBarTest", msoBarTop, True, True) '-> để tạo menubar
Application.CommandBars.Add("mnuBarTest", msoBarTop, , True) '-> để tạo toolbar
'msoBarTop/msoBarFloating/msoBarLeft/msoBarRight/msoBarBottom là vị trí của đối tượng / trên cùng/ luôn nổi, trái hay phải hay dưới cùng

Với Menubar thì kiểu đối tượng để chứa các thực đơn sẽ là msoControlPopup. Có thể tạm gọi cái này là giỏ chứa vậy. Sau khi đã tạo ra được giỏ chứa thì ta có thể thêm các điều khiển khác giống như tại toolbar thôi.
Nhóm các đối tượng điều khiển sau đó là msoControlButton.
Tuy nhiên để tạo ra được menu con thì cái giỏ chứa của các menu con cũng phải là dạng điều khiển msoControlPopup.
Vậy là về nguyên tắc không có sự khác nhau lớn đúng không ạ.
MS cho ta danh sách khá nhiều kiểu điều khiển khác nhau cho Toolbar và menubar nhưng thật đáng tiếc, họ chỉ cho ta điều khiển được 4 loại đối tượng đó là

   msoControlComboBox: Hộp chọn thả xuống
    msoControlButton: các nút bấm, mục thực đơn
    msoControlDropdown: Hộp thả xuống
    msoControlEdit: Nhúng điều khiển để soạn văn bản như dạng textbox (truyền tham số để tìm kiếm chẳng hạn)
    msoControlPopup: Mục thực đơn để chứa các đối tượng khác
    msoControlActiveX: Nhúng điều khiển ActiveX.

Còn các loại điều khiển phức tạp khác thì đến nay vẫn chưa có cách nào để tùy biến được (ít nhất là đến phiên bản 2003).
Ngoài ra, để gắn các hành động vào mỗi mục thực đơn, ta cần chú ý đến thuộc tính OnAction, đây là thuộc tính ghi/đọc và bạn có thể gán khi khởi tạo hoặc trong quá trình sử dụng chương trình theo cú pháp:

MyMenuObject.OnAction=[tên hàm/ thủ tục]()

Như vậy là bạn có thể thực hiện những điều mình muốn rồi.
À, cũng xin giới thiệu thêm một số thanh menu cơ bản của Excel nhé:

CommandBars("Cell") chứa các thực đơn đẩy lên liên quan đến Ô (khi nhấn phải chuột)
CommandBars("Row") chứa thực đơn đẩy lên khi chọn đầu dòng và nhấn phải chuột
CommandBars("Column") chứa thực đơn đẩy lên khi chọn đầu cột và nhấn phải chuột
CommandBars("Worksheet Menu Bar") Đây là thực đơn chính của ứng dụng Excel đấy.

Ngoài ra còn nhiều nữa. Các bạn cứ nghiên cứu, nếu có gì cần thì cứ chia sẻ nhé. Hy vọng bài viết sẽ giúp các bạn được phần nào.

 

[paulsteigel]

Đưa hình ảnh ngoài vào menu Excel!

Hiện có vài cách để đưa các hình ảnh tự tạo vào trong thực đơn của các ứng dụng Office, mình xin phép được chia sẻ với các bạn một ví dụ nho nhỏ về cách đưa các hình ảnh tự tạo vào thực đơn hoặc nút bấm của MS Excel một cách đơn giản.
Để làm điều này, các bạn cần chuẩn bị trước một số thứ:
1. Chuẩn bị sẵn vài ảnh đại diện dạng *.bmp hoặc *.ico hoặc loại khác cũng được với kích thước là 16x16 px.
2. Mở một workbook mới và vào chế độ soạn thảo VBA bằng cách nhấn Alt+F11.
+ Bạn thêm 1 form frmImages chẳng hạn.
+ Thêm vào form vài picture box với tên dễ nhớ như btnSearch, btnConvert chẳng hạn
và đặt thuộc tính picture của các image này trỏ tới các file ảnh mà bạn vừa tạo ra nhé.
+ Đặt thuộc tính Autosize của các Picture box là True nhé.
Lưu lại form đó và bây giờ nhấn kép vào ThisWorkbook, thêm vào đoạn thủ tục này
(Mỗi khi mở bảng tính, thủ tục InstallCommandBar sẽ được kích hoạt)

Private Sub Workbook_Open()
    InstallCommandBar
End Sub

Bây giờ bạn thêm vào một Module mới và đưa vào thủ tục sau:

Option Explicit

Sub InstallCommandBar()
    ' Try to load the command bar
    Dim myCmb As CommandBarControl
    Dim myTlb As CommandBar
    Dim cmbName As String
    
    cmbName = "Custom Tools"
    ' create the toolbar
    If Not ToolBarExist(cmbName) Then
        Set myTlb = Application.CommandBars.Add(cmbName, msoBarTop, False, False)
        With myTlb
            .Visible = True
            .Position = msoBarTop
        End With
    End If
    
    ' Create button bar
    If Not CommandBarExist("Looku&p", cmbName) Then
        ' Code conversion button
        Set myCmb = Application.CommandBars(cmbName).Controls.Add(msoControlButton)
        With myCmb
            .OnAction = "LookupWord"
            .Caption = "Looku&p"
            .Style = msoButtonIconAndCaption
        End With
        SetButtonFace myCmb, "imgSearch"
        
        ' Codetable selection button
        Set myCmb = Application.CommandBars(cmbName).Controls.Add(msoControlButton)
        With myCmb
            .OnAction = "Setting"
            .Caption = "Select dictionar&y"
            .Style = msoButtonIconAndCaption
        End With
        SetButtonFace myCmb, "imgSelect"
        
        ' Release all
        Set myCmb = Nothing
    End If
End Sub

'=============================================
' Commandbar creation and validation
'---------------------------------------------
Function CommandBarExist(cmbCaption As String, toolBarName As String) As Boolean
    Dim CmbObj As CommandBarControl
    ' Now try to count how many command bar
    Dim tlbObj As CommandBar
    For Each CmbObj In Application.CommandBars(toolBarName).Controls
        If CmbObj.Caption = cmbCaption Then
            CommandBarExist = True
            Exit For
        End If
    Next
    Set tlbObj = Nothing
End Function

Function ToolBarExist(tlbName As String) As Boolean
    Dim tlbBarObject As CommandBar
    On Error GoTo ErrHandler
    Set tlbBarObject = Application.CommandBars(tlbName)
    Set tlbBarObject = Nothing
    ToolBarExist = True
ErrHandler:
End Function
'========END OF COMMAND BAR SECTION===========

Public Sub SetButtonFace(CmbObj As CommandBarControl, PictureName As String)
    Dim oPic As StdPicture
    ' Load the picture (.bmp file) to use for the button image.
    Set oPic = frmResources.Controls(PictureName).Picture
    CmbObj.Picture = oPic
    Set oPic = Nothing
End Sub

Lưu lại Workbook và thoát khỏi excel, khởi động lại Excel, mở bảng tính vừa rồi và bạn sẽ thấy có một thanh công cụ Custom tool được tạo ra với các nút có hình như rồi.
Ở đây điều các bạn cần lưu ý đó là thủ tục SetbuttonFace, nó tham chiếu tới ảnh có trong form và gán cho nút bấm đấy

Public Sub SetButtonFace(CmbObj As CommandBarControl, PictureName As String)
    Dim oPic As StdPicture
    ' Load the picture (.bmp file) to use for the button image.
    Set oPic = frmResources.Controls(PictureName).Picture
    CmbObj.Picture = oPic
    Set oPic = Nothing
End Sub

Chi tiết các bạn có thể xem thêm trong file đính kèm.
Ngoài ra, các bạn thêm vào một module với các đoạn code này nhé.

Option Explicit

Sub LookupWord()
    ' A test menu
    MsgBox "Hi! I am good to see you now!"
End Sub

Sub Setting()
    ' A test menu
    MsgBox "Hi! I am good to see you now!"
End Sub

Xin cảm ơn và chúc vui

 

[qhthong]

Hiện có vài cách để đưa các hình ảnh tự tạo vào trong thực đơn của các ứng dụng Office, mình xin phép được chia sẻ với các bạn một ví dụ nho nhỏ về cách đưa các hình ảnh tự tạo vào thực đơn hoặc nút bấm của MS Excel một cách đơn giản.
Để làm điều này, các bạn cần chuẩn bị trước một số thứ:
1. Chuẩn bị sẵn vài ảnh đại diện dạng *.bmp hoặc *.ico hoặc loại khác cũng được với kích thước là 16x16 px.
2. Mở một workbook mới và vào chế độ soạn thảo VBA bằng cách nhấn Alt+F11.
+ Bạn thêm 1 form frmImages chẳng hạn.
+ Thêm vào form vài picture box với tên dễ nhớ như btnSearch, btnConvert chẳng hạn
và đặt thuộc tính picture của các image này trỏ tới các file ảnh mà bạn vừa tạo ra nhé.
+ Đặt thuộc tính Autosize của các Picture box là True nhé.
Lưu lại form đó và bây giờ nhấn kép vào ThisWorkbook, thêm vào đoạn thủ tục này
(Mỗi khi mở bảng tính, thủ tục InstallCommandBar sẽ được kích hoạt)

Private Sub Workbook_Open()
    InstallCommandBar
End Sub

Bây giờ bạn thêm vào một Module mới và đưa vào thủ tục sau:

Option Explicit
 
Sub InstallCommandBar()
    ' Try to load the command bar
    Dim myCmb As CommandBarControl
    Dim myTlb As CommandBar
    Dim cmbName As String
 
    cmbName = "Custom Tools"
    ' create the toolbar
    If Not ToolBarExist(cmbName) Then
        Set myTlb = Application.CommandBars.Add(cmbName, msoBarTop, False, False)
        With myTlb
            .Visible = True
            .Position = msoBarTop
        End With
    End If
 
    ' Create button bar
    If Not CommandBarExist("Looku&p", cmbName) Then
        ' Code conversion button
        Set myCmb = Application.CommandBars(cmbName).Controls.Add(msoControlButton)
        With myCmb
            .OnAction = "LookupWord"
            .Caption = "Looku&p"
            .Style = msoButtonIconAndCaption
        End With
        SetButtonFace myCmb, "imgSearch"
 
        ' Codetable selection button
        Set myCmb = Application.CommandBars(cmbName).Controls.Add(msoControlButton)
        With myCmb
            .OnAction = "Setting"
            .Caption = "Select dictionar&y"
            .Style = msoButtonIconAndCaption
        End With
        SetButtonFace myCmb, "imgSelect"
 
        ' Release all
        Set myCmb = Nothing
    End If
End Sub
 
'=============================================
' Commandbar creation and validation
'---------------------------------------------
Function CommandBarExist(cmbCaption As String, toolBarName As String) As Boolean
    Dim CmbObj As CommandBarControl
    ' Now try to count how many command bar
    Dim tlbObj As CommandBar
    For Each CmbObj In Application.CommandBars(toolBarName).Controls
        If CmbObj.Caption = cmbCaption Then
            CommandBarExist = True
            Exit For
        End If
    Next
    Set tlbObj = Nothing
End Function
 
Function ToolBarExist(tlbName As String) As Boolean
    Dim tlbBarObject As CommandBar
    On Error GoTo ErrHandler
    Set tlbBarObject = Application.CommandBars(tlbName)
    Set tlbBarObject = Nothing
    ToolBarExist = True
ErrHandler:
End Function
'========END OF COMMAND BAR SECTION===========
 
Public Sub SetButtonFace(CmbObj As CommandBarControl, PictureName As String)
    Dim oPic As StdPicture
    ' Load the picture (.bmp file) to use for the button image.
    Set oPic = frmResources.Controls(PictureName).Picture
    CmbObj.Picture = oPic
    Set oPic = Nothing
End Sub

Lưu lại Workbook và thoát khỏi excel, khởi động lại Excel, mở bảng tính vừa rồi và bạn sẽ thấy có một thanh công cụ Custom tool được tạo ra với các nút có hình như rồi.
Ở đây điều các bạn cần lưu ý đó là thủ tục SetbuttonFace, nó tham chiếu tới ảnh có trong form và gán cho nút bấm đấy

Public Sub SetButtonFace(CmbObj As CommandBarControl, PictureName As String)
    Dim oPic As StdPicture
    ' Load the picture (.bmp file) to use for the button image.
    Set oPic = frmResources.Controls(PictureName).Picture
    CmbObj.Picture = oPic
    Set oPic = Nothing
End Sub

Chi tiết các bạn có thể xem thêm trong file đính kèm.
Ngoài ra, các bạn thêm vào một module với các đoạn code này nhé.

Option Explicit
 
Sub LookupWord()
    ' A test menu
    MsgBox "Hi! I am good to see you now!"
End Sub
 
Sub Setting()
    ' A test menu
    MsgBox "Hi! I am good to see you now!"
End Sub

Xin cảm ơn và chúc vui

Cho em hỏi!
Có cách nào xóa được icon excel bên trái thanh menu không (kèm theo hình vẽ)
Chân thành cảm ơn

 

[hmloan]

Cach tao menu

Các anh ơi!
Cho em hỏi. có cách nào khi khởi động excel lên sẽ tự động gọi 1 hàm. vd tao_menu()
Các anh giúp em với. Thanks các anh rất nhiều

 

[muontennguoikhac]

Bạn click đôi vào "ThisWorkbook" trong cửa sổ "Project - VBAProeject" ,
rồi trong khung soạn thảo gõ vào:

Private Sub Workbook_Open()
Call tao_menu
End Sub

Đó là tạo menu khi mở workbook.
Còn muốn làm cái gì đó ngay khi mở Excel thì bạn đưa nó vào thư mục XLStart hoặc đưa vào Add-in.