Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 676
  • Last Modified:

Display "Wait" while file is being loaded by VBA

My code opens a file when a particular cell is selected. This causes excel to pause for a few seconds. I would like some code to create a text box, display the message in the centre of the window and then delete it as soon as the file is opened. I do not want to do this with a message box or a modal userform.
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 3
  • 3
  • 3
  • +1
1 Solution
 
andrew_manCommented:
This is a good question, but I seem to create a text box either to use message box or userform.

Do we have another choice?

Can you give us an example?
0
 
Rory ArchibaldCommented:
How about a modeless userform?
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Nope to both.

I would like to have this piece of code which I can simply copy and paste to another place instead of having to create userforms as well. Msgbox needs user intervention.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
andrew_manCommented:
I think msgbox can no user intervention
0
 
Rob HensonFinance AnalystCommented:
How about using the Message/Status Bar at the bottom of the screen?

Alternatively, have a section in the file (separate tab maybe?) with the required message and the script goes to this tab, switches off screen updating while performing what is required and then switches Screen Update back on to go back to where the user initiated the script.

Thanks
Rob H
0
 
andrew_manCommented:
Dear ssaqibh,

The Rob mentioned status bar, the status bar is a choice for your case!

Andrew Man from Hong Kong
0
 
Saqib Husain, SyedEngineerAuthor Commented:
andrew_man, you have to click on OK to get rid of the message box.

Rob, bottom of the screen is too small. I would like large text. Just a text box created and then deleted is what I want.

I could do it myself but I wanted to let someone get the points and I get the product.
0
 
Rob HensonFinance AnalystCommented:
Scrub my second option (separate tab), this would mean incorporating that tab in all files in much the same way as incorporating a UserForm which you don't want to do.

Thanks
Rob
0
 
Rory ArchibaldCommented:
You could use something like this:

   Dim tb                          As TextBox
   With ActiveCell
      Set tb = .parent.TextBoxes.Add(.Left, .Top, 100, 60)
   End With
   With tb
      .Text = "Please wait..."
      .Font.Size = 40
      .AutoSize = True

   End With
   Workbooks.Open "..."
   tb.Delete

Open in new window

0
 
Rob HensonFinance AnalystCommented:
Sub CreateTextBox()
    
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 262.5, 157.5, 384, _
        103).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Please wait while required file opens!"
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 38). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 38).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 36
        .Name = "+mn-lt"
    End With
    Range("A1").Select
    
'    ActiveSheet.DrawingObjects.Select
'    Selection.Delete

Open in new window


Recorded in Excel 2010 and then tweaked.
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Great, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now