status not being transferred to sub routine

[SeniorTom]

I have a form that prompts the user to continue or cancel. If they select the wrong file I cancel and want to give a message and restart. I need to check myFileStatus As Boolean but it is empty when I return to Public Sub OKButton_Click(). Your help is appreciated.

Tom

[COLOR=#0000cd]' located in RESPONSE.xlsm - UpdateForm (code) OKButton - click[/COLOR]

Public Sub OKButton_Click()
Dim msg As String
ThisWorkbook.Activate
Call Sheet1.update_trackingFile
Unload UpdateForm
Debug.Print myFileStatus                        ' trying to get status so I can cancel message with an If... then
msg = " Your emails have been sent" & vbNewLine
msg = msg & " confirm in CP sent folder"
MsgBox msg
End Sub



'================================================================================
[COLOR=#0000cd]' This is located in RESPONSE.xlsm -Sheet1(code) General - update_trackingFile[/COLOR]
  
' Right click any cell to start the process of emailing responses to signup requests.
  
        Public mailcount As Long                       ' number of emails sent
        Dim RosterFileName As Variant
        Dim ResponseFileName As Workbook
        Dim mylocalpath As Variant
        Dim MyActivefile As Variant
        Const emailColumn As String = "C"           ' column in response file for e-mail address
        Dim lastRow As Long
[COLOR=#ff0000]        Public myFileStatus As Boolean                ' I'm trying to use this in another subroutine[/COLOR]
        Dim fullpath As Variant
___________________________________________________________________________________________
Public Sub update_trackingFile()
' Routing to take responses from a google form's spreadsheet. It will also email a response to
' the requester's email

' makes sure the Roster file is open
mylocalpath = ThisWorkbook.Path
MyActivefile = ThisWorkbook.FullName
Set ResponseFileName = ThisWorkbook
ResponseFileName.Activate
With Sheet1
' confirm that the roster file is open and the version you want
Call OfferRosterFile
    If myFileStatus = False Then
            Exit Sub
    Else
    End If
' Insert the email address in the response file
Call insertEmail

' send an email to all that have responded
Call Send_emails            '****** send to must be adjusted before release

End With
End Sub
...
...
...

If Not UCase(RosterFileName) Like FilterName Then
        MsgBox "You must Select a Roster file"
         myFileStatus = False
         Exit Sub

 

[RedBeard]

This is a question about scope.


If you only want the variables to last the lifetime of the function, use Dim inside the function or sub to declare the variables.
A global variable is declared outside of the function using the Public keyword. This variable will be available during the life of your running application. In the case of Excel, this means the variables will be available as long as that particular Excel workbook is open.

If you're building a big application and feel a need to use global variables, I would recommend creating a separate module just for your global variables. This should help you keep track of them in one place.

 

[SeniorTom]

I have declared "Public myFileStatus As Boolean" before my first sub. It is valid up to
If myFileStatus = False Then
Exit Sub
Then when I exit sub and return to "Public Sub OKButton_Click()" it is "empty"

 

[RedBeard]

In that case, may I suggest placing it in a module by itself and see if that helps.

 

[dmt32]

Hi,
Your Public Variables need to be placed in a STANDARD module.

Rather than declare them Public, consider passing then as arguments to the called procedure.


Example

'Forms code Page:

Private Sub OKButton_Click()
    Dim msg As String
    Dim myFileStatus As Boolean


    ThisWorkbook.Activate
    Call update_trackingFile(myFileStatus)
    Unload UpdateForm


    Debug.Print myFileStatus   ' I can cancel message
    
    If Not myFileStatus Then
    msg = " Your emails have been sent" & vbNewLine
    msg = msg & " confirm in CP sent folder"
    MsgBox msg
    End If
End Sub

STANDARD module:

Public Sub update_trackingFile(ByRef Cancel As Boolean)


    'your code


    If Not UCase(RosterFileName) Like FilterName Then
            MsgBox "You must Select a Roster file.", 48, "File Selection Required"
             Cancel = True
             Exit Sub
    End If
End Sub

Hope Helpful

Dave

 

[SeniorTom]

Passing the status did not work so I thought I'd try a simple routine.

Here are the screen shots and code. The “myFileStatus” initializes as false and is set to true in “Public Sub OfferRosterFile()”. When I come back from the call it is still true. When I exit sub after “If myFileStatus = True Then” it goes “empty” and is “empty” at ‘Public Sub OKButton_Click()”. I have moved myfileStatus to OKButton and tried public & Dim. Any thoughts would be appreciated.

   ' Right click any cell to start the process of emailing responses to signup requests.
 
        Public mailcount As Long                       ' number of emails sent
        Dim RosterFileName As Variant
        Dim ResponseFileName As Workbook
        Dim mylocalpath As Variant
        Dim MyActivefile As Variant
        Const emailColumn As String = "C"           ' column in response file for e-mail address
        Dim lastRow As Long
       Dim myFileStatus As Boolean
        Dim fullpath As Variant
 
 
Public Sub update_trackingFile()
' Routing to take responses from a google form's spreadsheet. It will also email a response to
' the requester's email
 
' makes sure the Roster file is open
mylocalpath = ThisWorkbook.Path
MyActivefile = ThisWorkbook.FullName
Set ResponseFileName = ThisWorkbook
ResponseFileName.Activate
With Sheet1
' confirm that the roster file is open and the version you want
Call OfferRosterFile
Debug.Print myFileStatus
    If myFileStatus = True Then
            Exit Sub
    Else
    End If
End With
End Sub
Public Sub OfferRosterFile()
'
' Macro1 OfferFile will present a list of roster files to use with this response
'   Got parts from "Jean-François Corbett" @ https://stackoverflow.com/questions/9086309/_
'   how-do-you-get-just-the-filename-rather-than-the-entire-file-path-of-an-open-fil
 
         MsgBox "No file has been selected"
         myFileStatus = True
             Cancel = True
             Exit Sub
 
'         myFileStatus = False
'         Exit Sub
 
End Sub

Public Sub OKButton_Click()
Dim msg As String
Dim myFileStatus As Boolean
ThisWorkbook.Activate
Unload UpdateForm
Call Sheet1.update_trackingFile
Debug.Print myFileStatus        ' try to get status so I can cancel message with an If... then
End Sub

 

[SeniorTom]

OOPs I've screwed up again. Screen shots showed up in the construction of the reply, but not in the reply itself. Sorry !

 

[SeniorTom]

Here are the screen shots:

 

[RedBeard]

But you have yet to try placing it in a module...

 

[SeniorTom]

Hello Red,

I have replied but did something wrong.

If I understand Scoping correctly, Procedure only Variables live and die in the sub and are defined inside the sub. Module only Variables are defined before the first sub in a module and are available to all subs in the module. Replacing Dim with public would make the Variable to all Modules in the workbook. I have placed "MyFileStatus" in "update_TrackingFile", "okButton" and before the first sub in the module bith as Dim and Public. All with the same results. Am I missing something?