We help IT Professionals succeed at work.
Get Started

Keep Screen from Blanking out when doing a SaveAS from VBA

wheat01
wheat01 asked
on
139 Views
Last Modified: 2016-03-10
Excel 2013 64 bit windows 8.1 with powerpivot. as the only add-in. Workbook opens with a user form with command buttons which toggle to various worksheets. User form has an activex command button to save the work book to user selected directory and filename.
Causes users to run around with their hair on fire needlessly.

Command button code as follows:
Private Sub cmdSave_Click()
  Dim Test As Variant
  On Error Resume Next

  lblSaving.Visible = True

  With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True 'kinda need this line
    .StatusBar = "Please wait while file is saved."
    DoEvents 'magic trick
    .DisplayAlerts = False
    .Calculation = xlManual 'sometimes excel calculates values before saving files
    .EnableEvents = False 'to avoid opened workbooks section open/save... to trigger
  End With
  
  Dim filesavename As String
  filesavename = Application.ActiveWorkbook.FullName
  Test = MsgBox("Do you want to save the current views", vbYesNo)
  If Test = vbYes Then
    filesavename = Application.GetSaveAsFilename( _
    FileFilter:="Excel Files (*.xlsb), *.xlsb")
    If Trim(filesavename) <> "" Then
      Test = MsgBox("Save as " & filesavename, vbYesNo + vbQuestion)
    Else
      lblSaving.Visible = False
      With Application
        .CutCopyMode = False
        .StatusBar = False
        .DisplayStatusBar = False
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
      End With
      Exit Sub
    End If
    If Test = vbYes Then
'    ActiveWorkbook.Save
      Application.ScreenUpdating = False
      Application.Visible = False
      UserForm1.Show

  ********* Problem********
      ActiveWorkbook.SaveAs filesavename  ' screen goes blank (white) with the UserForm Title in the window


      DoEvents
      UserForm1.Show
      Application.Visible = True
      Application.ScreenUpdating = True
    Else
      lblSaving.Visible = False
      With Application
        .CutCopyMode = False
        .StatusBar = False
        .DisplayStatusBar = False
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
      End With
      Exit Sub
    
    End If
      
  End If

  lblSaving.Visible = False
  With Application
        .CutCopyMode = False
        .StatusBar = False
        .DisplayStatusBar = False
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlAutomatic
  End With
End Sub

Open in new window

Comment
Watch Question
Group Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE