troubleshooting Question

Keep Screen from Blanking out when doing a SaveAS from VBA

Avatar of wheat01
wheat01 asked on
VBAMicrosoft Excel
7 Comments1 Solution146 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Roy Cox
Group Finance Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros