CODEWITHSUNDEEP
vbams-access
SalkinD
SalkinD

Reputation: 783

Restore default ribbons with vba (Using a custom UI)

Sorry, I know this case has been discussed often, but I didn't find the perfect solution.

In my access database I use a custom ui per USysRibbons, this works great. The default UI is not shown in User mode, but there should be a button that simply restores the default UI. What would be the best solutions for this?

Upvotes: 2

Views: 1835

Answers (2)

HackSlash
HackSlash

Reputation: 5811

The accepted answer is insightful and I'm glad it worked for you. Unfortunately it didn't actually answer your question.

The default state is for the CustomRibbonID property to not exist at all. So to return to default state we need to delete the property.

Public Sub DeleteCurrentDBProperty(ByVal propertyName As String)
    With CurrentDb.Properties
        On Error Resume Next
        .Delete propertyName
        On Error GoTo 0
    End With
End Sub

Public Sub RestoreDefaultRibbon()
    DeleteCurrentDBProperty "CustomRibbonID"
End Sub

Now we need a way to programmatically add the property back. That takes a more robust method.

Public Sub SetCurrentDBProperty(ByVal propertyName As String, ByVal newValue As Variant, Optional ByVal prpType As Long = dbText)
    Dim thisDBs As Database
    Set thisDBs = CurrentDb
    Dim wasFound As Boolean
        
    ' Look for property in collection
    Dim thisProperty As Object ' DAO.Property
    For Each thisProperty In thisDBs.Properties
        If thisProperty.Name = propertyName Then
            ' Check for matching type
            If thisProperty.Type <> prpType Then
                ' Remove so we can add it back in with the correct type.
                thisDBs.Properties.Delete propertyName
                Exit For
            End If

            wasFound = True
            
            ' Skip when no change is required
            If thisProperty.Value = newValue Then
                Exit For
            Else
                ' Update value
                thisProperty.Value = newValue
            End If
        End If
    Next thisProperty
    
    If Not wasFound Then
        ' Add new property
        Set thisProperty = thisDBs.CreateProperty(propertyName, prpType, newValue)
        thisDBs.Properties.Append thisProperty
    End If
End Sub

Then given an example ribbon name of Runtime you could call the property setter like this:

Public Sub SetRuntimeRibbon()
    SetCurrentDBProperty "CustomRibbonID", "Runtime"
End Sub

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

You can hold down the shift key during startup – your custom specified ribbon will thus not show.

Of course, during development and desing, you will hold down the shift key so your startup settings don't run. You then develop for a while, and then to test in "user" mode, you exit, and then re-enter the application without the shift key bypassed. You will likely do this dance all day long as you run/test as user mode, and then flip back in to developer mode.

You cannot really develop or modify things when you run your application with the startup settings and custom ribbon set. And there are SO MANY settings you need and trying to code all these settings is a real waste of time. So just use shift-by-pass them when you want to work.

A really cool trick is to place a compact + repair button on the QAT. So when working, it is ONE mouse click and you simply do NOT hold down the shift key – presto without an exit + re-enter you are now in "user" mode and all your startup settings are now "active" and engaged for you to test your application in "user mode". And this includes your custom ribbons.

Another c+r and HOLDING down the shift key will flip you back into developer mode. This assumes you have the c+r inn your QAT and likely your startup settings will hide this. So in this case to "flip" back to developer mode I simple hit alt-f4 to exit the whole application. At this point the accDB file in windows explore will STILL be highlighted so I now tap the enter key.

So exiting and shifting between "user" and "developer" mode "dance" will occur all day long.

When you finally have things just right? You THEN compile the accDB down to an accDE to lock your code and forms designs. You also can and should disable the shift key by-pass and distribute this compiled application to your users. Compiled applications not only lock up your forms and code from any changes, but such code run MUCH more reliable since un-handled errors will NOT reset global or local vars – you can REST assured that all variables remain intact during the WHOLE time the user is running your application.

The above is really quite much the only practical approach to developing in Access.

Better yet this approach ALSO eliminates the need to have all kinds of code that attempts to hide or show the developer surfaces in Access – in fact you find that many settings are rather elusive or difficult to set in code anyway such as AutoExec macro, or even the application wide custom ribbon. And even more worse is such settings often require a re-start of Access anyway. (once access has stared, then it TOO LATE to modify the settings and if you do, a exit + reenter is required).

All of this huge whacks of code and hassle can simply be eliminated by holding down one little tiny shift key to enter in as a developer.

Upvotes: 3

Related Questions