Picture/Table Tools does not appear on the ribbon in Excel

Question

I have a Worksheet that contains a picture and a table. I also have a UserForm that acts as a countdown timer. The UserForm contains the following piece of code:

Option Explicit
Const AllowedTime As Double = 10 ' Total time in minutes

Private Sub UserForm_Activate()

Dim T, E, M, S As Double

T = Timer

Do
    E = CDbl(Time) * 24 * 60 * 60 - T
    M = AllowedTime - 1 - Int(E / 60)
    S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)
    
    TimeLabel.Caption = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")
    
    DoEvents
Loop Until (Timer - T) / 60 >= AllowedTime   

MsgBox "Time Over!"
Unload Me

End Sub

The above code is not mine. I found it on an Excel forum. It just displays a countdown timer of 10 minutes, and when the time runs out, the userform unloads.

The problem is that while the userform is running, when I click the picture or the table in the worksheet, the Picture Tools/Table Tools that normally appear on the ribbon does not appear. I inserted a chart, and the Chart Tools doesn't appear too.

I found out that when I run the userform by commenting out the above timer code, everything works perfectly. I tried opening the Excel file in 3 different PCs, and all of them showed the same problem. Is there anything wrong with the timer code?

user_1140579 · Answer

It happens because of the Do-Loop. It is not restricted to just Picture Tools/Table Tools but also to other menus like PivotTable Tools.

Here is an example to replicate your problem. When the loop is running then the menu will not show. Paste this code in a module.

Option Explicit

Sub Sample()
    Dim loopCount As Long
    
    Do
        loopCount = loopCount + 1
        
        '~~> Wait for 1 second
        Wait 1
        
        If loopCount > 15 Then Exit Sub
    Loop
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

Instead of using a loop, you can use Application.OnTime to show the timer. Here is an example. BTW there are APIs as well for handling timer in VBA.

Caution:

Make a backup of your data or use a new workbook before you work with or test timers.

Userform Code:

Insert a new userform. Let's call it frmTimer. Put one label on it. Let's call it TimeLabel. Now paste this code in the userform.

Option Explicit

Dim nextMoment As Date

Private Sub UserForm_Activate()
    Timer_Event
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Timer_Stop
End Sub

Sub Timer_Event()
    nextMoment = Now + TimeValue("00:00:01")
    Application.OnTime nextMoment, "Module1.OnTimer"
End Sub

Sub Timer_Stop()
    Application.OnTime nextMoment, "Module1.OnTimer", Schedule:=False
End Sub

Public Sub OnTimer()
    TimeLabel.Caption = Time
    Timer_Event
End Sub

Module Code:

Insert a module. Let's call it Module1. (This should be the name by default if this is a new workbook). Paste this code there

Option Explicit

Sub OnTimer()
    frmTimer.OnTimer
End Sub

Sub ShowForm()
    frmTimer.Show vbModeless
End Sub

Now run Sub ShowForm(). You will see that the menu doesn't disappear now.

In Action

Picture/Table Tools does not appear on the ribbon in Excel

1 Answers