Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA calling Browse dialog box - what causes screen problem?

Posted on 2011-02-18
5
Medium Priority
?
469 Views
Last Modified: 2012-05-11
I'm using code from EE (works great in another file) at the Save As event to give the user a browse dialog box but I am naming the file myself through code. The browse dialog box redraws funny on screen .. see picture. I've rebooted my PC and still happens. Any solution? picture of problem
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim FileFormatValue As Long
    Dim rng As Range
    Dim l As Range
    Dim store As String
    Dim ver As String
    Dim fName As String
    Dim theirPath As String
    Dim needPath As Integer

    If UCase$(Right$(ThisWorkbook.name, 3)) = "XLT" Then
        'get out because it's me updating the original template file
        Exit Sub
    End If
    
    'make sure SW Worksheet F27:F46 contains data
    Application.ScreenUpdating = False
    Worksheets("SW Worksheet").Activate
    Set rng = Worksheets("SW Worksheet").Range("F27:F46")
    For Each l In rng
        If IsEmpty(l.Value) Then
            MsgBox "Data is missing in cell F" & l.row & " on the SW Worksheet." _
                & vbCrLf & "Please be sure all cells from F27 through F46 have data."
            Worksheets("SW Worksheet").Range("F" & l.row).Activate
            Cancel = True
            Exit Sub
        End If
    Next l
    
    'refers to and automatically runs the InvalidSWTotal function
    If InvalidSWTotal = True Then
        Cancel = True
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    If SaveAsUI = True Then     'it's a SaveAs
        ActiveWorkbook.Protect Password:="3253", structure:=True
        store = Worksheets("SW Worksheet").Range("H5").Value
        ver = Worksheets("SW Worksheet").Range("N1").Value
        fName = store & " SOW ver " & ver
        theirPath = GetFolderName("Select a folder")
        If theirPath = "" Then
            Do Until theirPath <> ""
                needPath = MsgBox("You need to select a folder.", vbOKCancel)
                If needPath = vbCancel Then
                    Cancel = True
                    Exit Sub
                End If
                theirPath = GetFolderName("Select a folder")
            Loop
        End If

        With Application
            .EnableEvents = False
            .DisplayAlerts = False
            .ScreenUpdating = False
            Me.SaveAs theirPath & "\" & fName & ".xls", FileFormat:=56, CreateBackup:=False
            .EnableEvents = True
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
        Cancel = True
    Else
        'it's Save, NOT SaveAs
        ActiveWorkbook.Protect Password:="3253", structure:=True
        ThisWorkbook.Saved = True
        Exit Sub
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window

Option Explicit

Private Type BROWSEINFO ' used by the function GetFolderName
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetFolderName(Msg As String) As String
' returns the name of the folder selected by the user
Dim bInfo As BROWSEINFO, path As String, r As Long
Dim x As Long, pos As Integer
    bInfo.pidlRoot = 0& ' Root folder = Desktop
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
        ' the dialog title
    Else
        bInfo.lpszTitle = Msg ' the dialog title
    End If
    bInfo.ulFlags = &H1 ' Type of directory to return
    x = SHBrowseForFolder(bInfo) ' display the dialog
    ' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetFolderName = Left(path, pos - 1)
    Else
        GetFolderName = ""
    End If
End Function

Open in new window

0
Comment
Question by:nbozzy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 34929870
Usually I've seen that problem related to video memory problems.  Rebooting can fix it unless you don't have enough memory installed to handle it.

Aside from that, I see that you are using from Declare statements.  I usually try to avoid these because they can get flaky if not used exactly right.  And in this case it seems that you could have used the FileDialog object to handle the same thing (although I didn't check too thoroughly what it's doing.)

Meanwhile, do you get the same results when you run this on another machine (preferrably with more memory)?
0
 

Author Comment

by:nbozzy
ID: 34929914
Unfortunately, all the PCs here have the same amount of memory, so I can't test it on a better machine until I get home. I'll be sure to do that.

I'm not sure what you mean by "you are using from Declare statements." Can you please clarify? Appreciate your help.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34929963
first, does this same problem occur on every machine?  If so, it's probably those Declare statements:


Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


There's probably another thing that is needed for them to "play nice" with windows (I'm just not sure at this point what that is...where did you find that code?)
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 34942263
You've turned off Screenupdating so the screen does not redraw if you move the dialog. Turn it back on before you show it. :)
0
 

Author Comment

by:nbozzy
ID: 34944883
Rorya - you called it. Thanks SOOOOO much!

Rspahitz - the code came from this site -- don't have the reference # offhand.

Thanks, everyone!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question