Opening File - Force User To Select?

[mattygriff]

I have a spreadsheet which takes data from two others which the user selects using the Application.GetOpenFilename dialog box. I'm looking for an effective way of dealing with a user who either clicks Cancel or the Red X on the dialog? If this happens, my sheet gets messed up pretty bad!

I was wondering whether there was a way of "greying out" the Cancel button and Red X - is there?

Thanks in advance.

 

[Bong]

You'd be better off trapping the condition in your code. When the user cancels out of getopenfilename, the return is "False".

_________________
Bob Rashkin

 

[Andrzejek]

Can you have a default Excel file to point to?
This way your user can only change the pre-selected file, but can not 'not point to any file'.

Would that fix your problem?



Have fun.

---- Andy

 

[mattygriff]

OK I'm currently using this code.....

Sub PopulateInitialPBRfromPSC()

    Dim arrPath() As String
    
    PSCFilename = Application.GetOpenFilename _
        ("Excel Files (*.xls),*.xls", , _
        "Please select Professional Services Contract Report...", MultiSelect:=False)
    
    If PSCFilename = False Then
    
        strDialog = "You have not selected a Professional Services Contract Report" & vbCrLf _
            & "Click OK to re-select or Cancel to exit"
        
        cmdNoSelect = MsgBox(strDialog, vbOKCancel)
        
        If cmdNoSelect = vbOK Then
            
            PopulateInitialPBRfromPSC
        
        Else
            
            Application.Quit
        
        End If
    
    End If

When it gets to the Application.Quit line I get a Type mismatch error for some bizarre reason. I assumed that would simply shut down Excel with no further action.

Any thoughts?

 

[mattygriff]

OK, I now have the following code

Sub PopulateInitialPBRfromMER()

    Dim arrPath() As String
    
    MERFilename = Application.GetOpenFilename _
        ("Excel Files (*.xls),*.xls", , _
        "Please select Monthly Engineering Report...", MultiSelect:=False)
    
     If MERFilename = False Then
    
        strDialog = "You have not selected a Monthly Engineering Report" & vbCrLf _
            & "Click OK to re-select or Cancel to exit"
        
        cmdNoSelect = MsgBox(strDialog, vbOKCancel)
        
        If cmdNoSelect = vbOK Then
            
            MERFilename = ""
            PopulateInitialPBRfromMER
        
        Else
            
            MsgBox "This file will now close" & vbCrLf & "Changes have not been saved"
            ThisWorkbook.Close savechanges:=False
        
        End If
    
    End If

All works fine UNLESS you don't initially select a file but then, when you get the "You have not selected..." dialog, you do select one.

For some reason, the Sub still recognises MERFilename as "" which causes no end of problems.

Any thoughts?

 

[mattygriff]

It's OK - got this sorted myself in the end.

I'll post the code if anyone wants it.

 

[fumei]

Post it. Someone may find it useful. It is always a good idea to post solutions that you get on your own.

faq219-2884

Gerry
My paintings and sculpture

 

[mattygriff]

OK Gerry - will post tomorrow when I'm back in the office.

 

[WalkerEvans]

I'm not sure what anyone else has, but the following will disable the "X" (Close) in the upper corner of Message Boxes:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

I hope this helps.



----------------------------------------------------------------------------------
[small]"What was the psychiatric diagnosis of the man who jumped into the river in Paris?"[/small]
"He was in Seine!"

 

[Andrzejek]

Sure WalkerEvans, you will be unable to close the UserForm, but what about the dialog box the OP is talking about?

And where would you put this piece of code?
In DialogBox_QueryClose event?

Have fun.

---- Andy

 

[mattygriff]

OK, due to popular demand, here's my code.

I created a function to handle the GetOpenFilename action like so....

Function PSCFileToOpen()

            PSCFilename = Application.GetOpenFilename _
                ("Excel Files (*.xls),*.xls", , _
            "Please select Professional Services Contract Report...", MultiSelect:=False)
            
            If PSCFilename = False Then
                    strDialog = "You have not selected a Monthly Engineering Report" & vbCrLf _
                    & "Click OK to re-select or Cancel to exit"

                cmdNoSelect = MsgBox(strDialog, vbOKCancel)

                If cmdNoSelect = vbOK Then

                    PSCFileToOpen = PSCFilename
                Else

                    MsgBox "This file will now close" & vbCrLf & "Changes have not been saved"
                    ThisWorkbook.Close savechanges:=False

                End If
                
            Else
            
                PSCFileToOpen = PSCFilename
                
            End If

End Function

Then, in my Sub I used the following code.....

    While PSCFilename = False
    
        PSCFilename = PSCFileToOpen()
    
    Wend

.....which continually calls the Function until a valid filename is returned.

Enjoy!