Kuidas luua kohandatud menüüsid oma Exceli töölehel VBA kaudu

Exceli linti saab muuta, et see sobiks konkreetse tööga. See harjutus loob uued menüüd, mis suunavad kasutajad konkreetsetele nende ülesandega seotud lehtedele.

Selles artiklis tutvustame, kuidas luua oma Exceli töölehel kohandatud menüüsid, näiteks järgmiselt.

See artikkel eeldab, et lugejal on kuvatud arendaja lint ja ta tunneb VBA redaktorit. Kui ei, siis kasutage Google'i „Exceli arendaja vahekaarti” või „Exceli koodi akent”.

Töövihik

Kasutataval töövihikul on mitu lehte. Soovitame kasutada siit leitud näidist. See näeb välja selline.

Esimesed neli lehte ei tööta ja neid kasutatakse selles harjutuses ainult navigeerimiseks.

Viiendal lehel on selle töövihiku kohandatud menüü struktuur. Praegu peaks see olema tühi, välja arvatud nupp Test.

Lisage menüü struktuur

Kopeerige järgmine tekstiplokk lehe "MenuSheet" lahtrisse A1.

Tase, pealdis, positsioon/makro, jagaja

1,&Kasutaja tööriistad,10

2, Näita armatuurlauda, ​​valige armatuurlaud

2,Lisa uus, ,TRUE

3, Klient, SelectClient

3,Asukoht,Valige asukoht

3,Haldur,SelectManager

2, sule, sulge fail

Vormindage menüü struktuur

Need CSV-andmed jõuavad veergu A. Nende vormindamiseks üksikuteks Exceli lahtriteks valige veerg A ja kasutage "Tekst veergudesse" aasta Andmete vahekaart. Eraldaja oleks "koma".

Kui oleme vastava koodi lisanud, annab ülaltoodu meile vasakpoolse tasemepõhise menüüstruktuuri.

Selle harjutuse esimene tase on menüüribal suvaliselt kümnendana elemendina paigutatud, nagu näete töövihiku täislaiuses kuvamisel.

Menüüde loomine ja hävitamine

Kuna soovime uusi menüüsid ainult selle konkreetse töövihiku jaoks, siis loome ja hävitame need töövihiku avamisel ja sulgemisel.

Allolev kood on algus. See käivitub töövihiku avamisel või sulgemisel. Kopeeri see oma töövihiku moodulisse.

Määrake MenuSheet'i nupp Test suvandile Auto_Open.

Option Explicit

Sub auto_Open()
    Call DeleteMenu
    Call CreateMenu
 End Sub
 
Sub auto_Close()
    Call DeleteMenu
End Sub

Allpool uurime oma menüüstruktuuri lähemalt.

Ühtegi makrot ei käivita Lisa uus kuna sellel pole muud funktsiooni kui alammenüüde põhifunktsioon.

Sellist menüüstruktuuri on lihtne hooldada. Lihtsalt lisage uusi üksusi nende makrodega, olles teadlik menüüst Tase.

Kood

Lisage moodulile järgmine VBA kood. See uurib menüülehte ja loob kohandatud menüü.

Sub CreateMenu()
'   Called from Auto_Open. 'NOTE: There is no error handling in this subroutine
    Dim MenuSheet As Worksheet
    Dim MenuObject As CommandBarPopup

    Dim MenuItem As Object
    Dim SubMenuItem As CommandBarButton
    Dim sRow As Integer
    Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider

    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    Call DeleteMenu

    sRow = 2 '   start row
    
'   Add menus using the structure as per the MenuSheet
    Range("A" & sRow).Select
    Do While ActiveCell > "" '****************
        With MenuSheet
            MenuLevel = .Cells(sRow, 1)
            Caption = .Cells(sRow, 2)
            PositionOrMacro = .Cells(sRow, 3)
            Divider = .Cells(sRow, 4)
            NextLevel = .Cells(sRow + 1, 1)
        End With
        
        Select Case MenuLevel
            Case 1 ' Add the top-level menu to the Worksheet CommandBar
                Set MenuObject = Application.CommandBars(1). _
                    Controls.Add(Type:=msoControlPopup, _
                    Before:=PositionOrMacro, _
                    Temporary:=True)
                MenuObject.Caption = Caption
            Case 2 ' A Menu Item
                If NextLevel = 3 Then
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
                Else
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = PositionOrMacro
                End If
                MenuItem.Caption = Caption
                If Divider Then MenuItem.BeginGroup = True
            Case 3 ' A SubMenu Item
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = Caption
                SubMenuItem.OnAction = PositionOrMacro
                
                If Divider Then SubMenuItem.BeginGroup = True
        End Select
        sRow = sRow + 1
        Range("A" & sRow).Select '***************************************
    Loop
    Sheets("Dashboard").Activate
End Sub

Sub DeleteMenu()
'   This sub will be executed when the workbook is closing
    Dim MenuSheet As Worksheet
    Dim sRow As Integer
    Dim Caption As String
    
    On Error Resume Next
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    sRow = 2
    Range("A" & sRow).Select
    Do While ActiveCell > ""
        If MenuSheet.Cells(sRow, 1) = 1 Then
            Caption = MenuSheet.Cells(sRow, 2)
            Application.CommandBars(1).Controls(Caption).Delete
        End If
        sRow = sRow + 1
        Range("A" & sRow).Select
    Loop
   
    On Error GoTo 0
End Sub

Sub SelectDashboard()
    Sheets("Dashboard").Activate
End Sub
Sub SelectClient()
    Sheets("Client").Activate
End Sub

Sub SelectLocation()
    Sheets("Location").Activate
End Sub

Sub SelectManager()
    Sheets("Manager").Activate
End Sub

Sub CloseFile()
    MsgBox "Close! (write your own code in the module)"
End Sub

Testige koodi nupuga Test. Äsja loodud kasutajamenüü asub menüü positsioonil 10, jaotises Lisandmoodulid.

Exceli taastamine

Excel on kohati ebastabiilne ja jookseb avatud ajal kokku, kahjustades lähtefaili. Kui faili taastamine ebaõnnestub, on kasulik omada tööriist selle parandamiseks Exceli kahjustused Vastasel juhul läheb kogu varundamata töö kaotsi.

Autori sissejuhatus:

Felix Hooker on andmete taastamise ekspert DataNumen, Inc., mis on maailmas juhtiv andmete taastamise tehnoloogiate, sealhulgas paranda rar-faili ja SQL-i taastamise tarkvaratooted. Lisateabe saamiseks külastage www.datanumenCom