VBA to create text box

[Russk68]

Hi
I am using a Mac and I want to create a text box that appears and then is deleted after 3 seconds. With this code, the text box is waiting 3 seconds to be inserted and the instantly deleted. It's doing the opposite of what I need. I'm baffled that the time value is being used by code that appears before it.
Thanks in advance!

Sub InsertAndDeleteTextBox()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=100, Height:=30)
    shp.TextFrame.Characters.Text = "Hello"
 
    ' Wait for 3 seconds before deleting the text box
    Application.Wait Now + TimeValue("00:00:03")
 
    'Delete the text box
    shp.Delete
End Sub

 

[NdNoviceHlp]

Hi Rusk68. I'll share some code that Jon Peltier has offered which seems to resolve this mystery. HTH. Dave

Sub InsertAndDeleteTextBox()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=100, Height:=30)
    shp.TextFrame.Characters.Text = "Hello"
    
    Dim T As Double
    T = Timer
    Do Until Timer - T > 1
      DoEvents
    Loop
    
    ' Wait for 3 seconds before deleting the text box
    Application.Wait Now + TimeValue("00:00:03")
  
    'Delete the text box
    shp.Delete
End Sub

 

[Russk68]

Hi
The code you provided is doing the same thing. The shape takes 3 seconds to insert and then is instantly deleted. I commented out the shp.delete to see the results. I just don't understand how preceding code is being affected.

 

[NdNoviceHlp]

Hmmm... my trial was a success but was done with Windows os. Sorry, I don't have any further suggestions. Good luck. Dave

 

[Russk68]

Hmmm... my trial was a success but was done with Windows os. Sorry, I don't have any further suggestions. Good luck. Dave

I have a Windows machine that I will try.
Thanks Dave!

 

[Domenic]

I works for me too on my Windows computer. For your Mac, try replacing...

Do Until Timer - T > 1

with

Do Until Timer - T > 3

Then remove the following lines from your code...

' Wait for 3 seconds before deleting the text box
    Application.Wait Now + TimeValue("00:00:03")

Here's another way of doing the same thing...

Sub InsertAndDeleteTextBox()
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, Left:=100, Top:=100, Width:=100, Height:=30)
    shp.TextFrame.Characters.Text = "Hello"
    
    PauseMacro 3 'seconds
 
    'Delete the text box
    shp.Delete
End Sub

Sub PauseMacro(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
    
    Do
        DoEvents
    Loop Until Timer > endTime
    
End Sub

Hope this helps!

 

[Russk68]

Same result. When I comment out shp.delete it takes 3 seconds for the shape to appear. It takes 10 seconds to appear when I change PauseMacro to 10. I will have access to a windows machine next week. It's just boggling my mind on why the PauseMacro line is telling code above it what to do.
Is there a way to show a video here?

 

[bebo021999]

Because the VBA code executes very quickly, the time elapsed between two lines is almost negligible. Therefore, when the line above requests inserting the shape, the line below requests to "wait" for 3 seconds, and the shape is hardly inserted before it is instructed to pause for 3 seconds.

Hence, DoEvents is necessary within the 3-second interval. (DoEvents is a command that instructs the code to pause execution for 3 seconds) During these 3 seconds, the shape will be inserted into the sheet.

Option Explicit
Sub ShowTextBox()
    Dim WTime As Double, shp As Shape
    WTime = Now ' starting time
   
   'Insert Textbox first
    Set shp = ActiveSheet.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
        Left:=100, Top:=100, Width:=100, Height:=30)
    shp.TextFrame.Characters.Text = "Hello"

    ' Pause the code for 3 seconds, to insert Textbox
    Do While Now <= WTime + TimeValue("00:00:03")
        DoEvents
    Loop

   'after 3s, delete
    shp.Delete
End Sub

 

[Jim Gordon]

Try this. Making Excel do something like select a cell and then DoEvents seems to fix the timing issue.

Sub ShowTextBox()
    Dim WTime As Double, shp As Shape
    WTime = Now ' starting time
   
   'Insert Textbox first
    Set shp = ActiveSheet.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
        Left:=100, Top:=100, Width:=100, Height:=30)
    shp.TextFrame.Characters.Text = "Hello"
    shp.Name = "MyBox"""
    Range("A1").Select
    DoEvents
    
    ' Pause the code for 3 seconds, to insert Textbox
    Application.Wait Now + TimeSerial(0, 0, 3)

   'after 3s, delete
    shp.Delete
End Sub