VBA - show button 5 seconds after image appears

[Arie Bos]

I have a combobox from which the user can make his choise.
with each choise, an image pops up.
After 5 seconds, I would like to have a button popping up to make that image invisible.
I tried

Application.Wait (Now + TimeValue("00:00:05"))

, but that halts the entire macro, and then image and button show up together.
Is there a way to seperate these two?

 

[Gokhan Aycan]

Application.OnTime Now+TimeValue("00:00:00"), "modulename.subname"

Then in the above sub, wait for 5 seconds and also use "DoEvents" before wait. I hope it makes sense.

In VBA we can't normally have 2 concurrent subs running but with DoEvents and Application.OnTime (perhaps .Run as well) you can fake it.

Passing arguments: Passing arguments to OnTime and OnAction macros

 

[Arie Bos]

Hi Gokhan,
Thank you for this advise, but it is a bit above my level how to implement what you write. could you please be a bit more detailed?

Arie

 

[Gokhan Aycan]

In order to give more detail, I need more detail from you as well.

Are the controls on a sheet (ActiveX or From control) or a userform?
Can the user do other stuff while the image is visible? What if they select another option from the combobox if it's possible in your current code?
Some screenshots and code that already exists would help a lot.

The answer may change a bit according to what you want to achieve exactly.

 

[Arie Bos]

I should have mentioned this. Everything is on a user form.
What happens is that the user makes a selection from a combobox. for each item a picture pops up () using the image from the toolbox). The user can continue to fill in other details in textboxes, but I want a button to pop up independently from what the user is doing or not, that has [image1.visible = False] command to let the picture disappear, and also itself. This last one is also not easy, as it is not allowed to have te button disappear while it is still active.

 

[Gokhan Aycan]

Ok, I have worked out something. The userform is as below (all controls have default names).




The userform module code:

Option Explicit

Private Sub ComboBox1_Change()

    Image1.BackColor = 1000000 * ComboBox1.ListIndex
    Image1.Visible = True
    ComboBox1.Enabled = False
    CommandButton1.Visible = False
    
    Application.OnTime Now + TimeValue("00:00:05"), "Module1.test_timer"

End Sub

Private Sub CommandButton1_Click()

    Image1.Visible = False
    CommandButton1.Visible = False
    
End Sub

Private Sub UserForm_Initialize()

    Me.Top = Application.Top + 150
    Me.Left = Application.Left + 30

    ComboBox1.AddItem "test1"
    ComboBox1.AddItem "test2"
    ComboBox1.AddItem "test3"
    
    CommandButton1.Visible = False
    Image1.Visible = False
    
End Sub

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

    If Not CommandButton1.Visible Then Cancel = True

End Sub

And since we can't app.ontime to a sub on a userform module, insert a new module or use an existing one (in this case Module1), the code that will make commandbutton visible.

Option Explicit
Option Private Module

Public Sub test_timer()

    'DoEvents
    UserForm1.CommandButton1.Visible = True
    UserForm1.ComboBox1.Enabled = True
    
End Sub

Notes:
Since changing combobox while still waiting for the test_timer sub would mess things up, I disable it until 5 seconds pass, and enable it again inside test_timer.
Userform will not close until 5 seconds pass and commandbutton appear in order to not mess anything up. This is done in QueryClose event of userform.
Me.Top and Me.Left are for convenience so userform always opens inside worksheet area regardless of which display excel window is on. If you want something similar, set userform's StartUpPosition to 0-Manual in VBA Editor properties pane. Otherwise delete those lines.
DoEvents was not necessary (at least in my test case), so I commented it out.
I just changed Image1.BackColor instead of attaching pictures as a means of testing.
In the Module1 code at the top, Option Private Module means, public subs in that module will not show when you open Macros window (ALT + F8) but you can still call them like "Call Module1.sub_name".

 

[Arie Bos]

Wow!. Thank you for this work Gokhan! If I knew it was that much work I would not have asked. I will try to implement this tonight.

Many Thanks Again!

 

[Gokhan Aycan]

It was easy enough when the requirements were known. 30 minutes at max.

 

[Arie Bos]

Hi Gokhan,
I tested it and implemented it in my workbook. It worked great!. And so kind of you to also write the explanation notes, that is a great help for beginners like myself. This way one really learns. Thank you again.

 

[Gokhan Aycan]

I just noticed something while looking for something irrelevant to this post.

When the form is first shown, you can't click X to exit. You have to first select an item from the combobox and wait 5 seconds. Depending on how you want it to behave, this may be a bug.

If so, you can modify events as such:

ListIndex = -1 means nothing has yet been selected

If ComboBox1.ListIndex = -1 Then Exit Sub - After a change if ComboBox is set to nothing, exit sub before running the rest of the code

Private Sub ComboBox1_Change()

    If ComboBox1.ListIndex = -1 Then Exit Sub

    Image1.BackColor = 1000000 * ComboBox1.ListIndex
    Image1.Visible = True
    ComboBox1.Enabled = False
    CommandButton1.Visible = False
    
    Application.OnTime Now + TimeValue("00:00:05"), "Module1.test_timer"

End Sub

Check CommandButton visibility only if ComboBox has something selected.

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

    If Not ComboBox1.ListIndex = -1 Then
        If Not CommandButton1.Visible Then Cancel = True
    End If
    
End Sub

Reset ComboBox to nothing after a command button click.

Private Sub CommandButton1_Click()

    Image1.Visible = False
    CommandButton1.Visible = False
    ComboBox1.ListIndex = -1
    
End Sub