Solved

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

Posted on 2011-02-18
5
460 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
  • 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 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now