Load custom ribbons when multiple books are open (1 Viewer)

zelarra821

Registered User.
Local time
Today, 13:31
Joined
Jan 14, 2019
Messages
851
Hy, guys.

I have these two procedures that I use to load a custom ribbon into the book I'm working with:

Code:
Sub AlCargarRibbon(Ribbon As IRibbonUI)

    If ActiveWorkbook Is Nothing Then Exit Sub
    
    wbName = Workbooks(Left(ActiveSheet.Parent.Name, InStrRev(ActiveSheet.Parent.Name, ".") - 1)).Name

    If GetCustomDocumentProperty("Proteger") Then
            
'        Existe la propiedad y su valor es True, por lo que NO existe libro activo y SÍ dará error si fijo el ribbon y paso el procedimiento para
'        fijar la Hoja0, Hoja1 y wbName.

    Else
        
'        Existe la propiedad y su valor es False, por lo que SÍ existe libro activo y NO dará error si fijo el ribbon y paso el procedimiento para
'        fijar la Hoja0, Hoja1 y wbName.
    
        Set MiRibbon = Ribbon
    
        FijarHojas0Y1
      
    End If
        
End Sub

Sub FijarHojas0Y1()

    Dim NumberSheets As String
    Dim SLen As Long
    Dim strHoja0 As String
    Dim strHoja1 As String

    On Error GoTo errLbl

    NumberSheets = CStr(Workbooks(Left(ActiveSheet.Parent.Name, InStrRev(ActiveSheet.Parent.Name, ".") - 1)).Worksheets.Count)
    
    SLen = Len(NumberSheets)
    
    strHoja0 = "Hoja" & Right(String(SLen, "0") & "0", SLen)
    
    strHoja1 = "Hoja" & Right(String(SLen, "0") & "1", SLen)
        
    If BuscarHoja(strHoja0) Then
    
        Set Hoja0 = GetSheetFromCodeName(Workbooks(Left(ActiveSheet.Parent.Name, InStrRev(ActiveSheet.Parent.Name, ".") - 1)), strHoja0)
    
    Else
    
        Set Hoja0 = Nothing
    
        'Aquí va el código en el que desactivo los botones que hacen referencia a la Hoja0.
    
    End If
    
    If BuscarHoja(strHoja1) Then
    
        Set Hoja1 = GetSheetFromCodeName(Workbooks(Left(ActiveSheet.Parent.Name, InStrRev(ActiveSheet.Parent.Name, ".") - 1)), strHoja1)
    
    Else
    
        Set Hoja1 = Workbooks(Left(ActiveSheet.Parent.Name, InStrRev(ActiveSheet.Parent.Name, ".") - 1)).Sheets(1)
    
    End If

    wbName = Hoja1.Parent.Name
    
errLbl_exit:

    Exit Sub
    
errLbl:
 
    Debug.Print "FijarHojas0Y1. Error número " & Err.Number & ": " & Err.Description

    MsgBox "FijarHojas0Y1. Error número " & Err.Number & ": " & Err.Description, vbInformation, wbName
 
End Sub

1755333836766.png


The problem arises when I have two workbooks open, close one of them, and continue working with the other. When I close the latter, it doesn't update the wbName and gives me an error with the name of the file I closed first.

I need to know how I should make Excel "reload" the ribbon when I move and/or close workbooks.

Here's the add-in file with the workbook and a video (although my English is terrible).

Thank you very much.

 

Attachments

Users who are viewing this thread

Back
Top Bottom