Exit Public Function without returning to Private Function (1 Viewer)

LB79

Registered User.
Local time
Today, 18:10
Joined
Oct 26, 2007
Messages
505
Hello,

I wonder if someone can tell me how to do something in FileDialog?
I previously had many private functions using FileDialog, but have now decided to put the basic code in a public function and call it from the private ones.
This works fine, but if, when FileDialog is open, the user presses Cancel, Im having trouble.
My code is below, how do I set the code to look for Cancel? Can a procedure be ended in the public function without it being returned to the private one (IE Exit Sub seems to end the public, but sends the user back into Private.

Thanks
Code:
[SIZE=3][COLOR=#000080][FONT=Arial]Public Sub Export()[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set FDialog = Application.FileDialog(msoFileDialogSaveAs)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]With FDialog[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].AllowMultiSelect = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].InitialFileName = “MyFile”[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].Title = "Please select a save location"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]If .Show = True Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]For Each varFile In .SelectedItems[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]DestFile = .SelectedItems(1) [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Next[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End If [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End With[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End Sub[/FONT][/COLOR][/SIZE]
 

KenHigg

Registered User
Local time
Today, 14:10
Joined
Jun 9, 2004
Messages
13,327
One way would be to pass a variable back to the calling function that is used as a flag if the user pressed cancel.
 

LB79

Registered User.
Local time
Today, 18:10
Joined
Oct 26, 2007
Messages
505
Thanks Ken - How do I recognise that cancel has been pressed?
 

KenHigg

Registered User
Local time
Today, 14:10
Joined
Jun 9, 2004
Messages
13,327
Are you doing the cancel with a msgbox function?
 

LB79

Registered User.
Local time
Today, 18:10
Joined
Oct 26, 2007
Messages
505
In the Private function the Cancel pressed on the FileDialog prompted the code to end and then a msgbox. But with the code split Im not sure how to recognsed the Cancel.
 

KenHigg

Registered User
Local time
Today, 14:10
Joined
Jun 9, 2004
Messages
13,327
Hum... I don't think I've ever tried to trap that. Let me tinker on it for a few minutes...
 

KenHigg

Registered User
Local time
Today, 14:10
Joined
Jun 9, 2004
Messages
13,327
Try adding something like the following at the top of the module routine:

Code:
On Cancel goto CancelCode

Then at the bottom put something like

Code:
CancelCode:
   CancelFlag = "True"
   Exit Sub

Where CancelFlag is the variable you pass back...

Or something along those lines...

edit: I don't think that's working... But I'm still tinkering.
 
Last edited:

KenHigg

Registered User
Local time
Today, 14:10
Joined
Jun 9, 2004
Messages
13,327
Do you have to have a ref set to get the Set FDialog line to work?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:10
Joined
Sep 12, 2006
Messages
15,614
LB - what do you expect to happen. A function HAS to return control to its originator

so

private function foobah
call (public function) main

... when public function main finishes, control returns here
a function can return a value that you then use to decide on a course of action

------------
the alternative is to call the public function in resposne to an event of some sort, when of course the event handler is over when the function ends.

--------------
I suspect that you maybe just arent handling the filesavedialog correctly, and testing the return value. eg What type of variable is fDialog, to need a set statement? - I think Ken just pointed this out.
 

LB79

Registered User.
Local time
Today, 18:10
Joined
Oct 26, 2007
Messages
505

Hi guys and thanks for your advice. Some of it I understand and some I don’t.
I get the point of Ken’s idea but I cant get that to work either.
In my public function I have at the moment

Code:
[SIZE=3][COLOR=#000080][FONT=Arial]Option Compare Database[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public FileName As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public FilePath As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public DestFile As Variant[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public varFile As Variant[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public fDialog As Office.FileDialog[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public Canx As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public Mth As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public Dte As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public Tme As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]-------[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public Sub ExpFile ()[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]On Cancel GoTo CancelCode[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Mth = Format(Now, "YYYYMM")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dte = Format(Now, "YYYYMMDD")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Tme = Format(Now, "HH-MM-SS")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set fDialog = Application.FileDialog(msoFileDialogSaveAs)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]With fDialog[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].AllowMultiSelect = False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].InitialFileName = FileName[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].Title = "ATMT: Please select a save location"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]If .Show = True Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]For Each varFile In .SelectedItems[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]DestFile = .SelectedItems(1)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Next[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End If[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End With[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Exit Sub[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]CancelCode:[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Canx = "True"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End Sub[/FONT][/COLOR][/SIZE]
And my private vba is
Code:
[/SIZE][/FONT]
[SIZE=3][COLOR=#000080][FONT=Arial]Private Sub LEG_lst1_Click()[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim LEGqry As String[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dte = Format(Now, "YYYYMMDD")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]If Me.LEG_cbo1 = "Outstanding PO" Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]FileName = Me.LEG_lst1.Column(0) & " " & Me.LEG_lst1.Column(1) & " " & "Outstanding PO" & " " & Dte & ".xls"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]LEGqry = "qry_LEGS_OutPO"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Else[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]FileName = Me.LEG_lst1.Column(0) & " " & Me.LEG_lst1.Column(1) & " " & "Transition" & " " & Dte & ".xls"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]LEGqry = "qry_LEGS_Transition"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End If[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Call ExpFile[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]If Canx = "True" Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Exit Sub[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Else[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End If[/FONT][/COLOR][/SIZE]
[FONT=Arial][SIZE=3][COLOR=#000080]
[/COLOR]

I think that it isn’t recognising the On Cancel part of the private function?
 

Users who are viewing this thread

Top Bottom