Macro to Exit Sub if NO is selected

[howard]

I have the following macro below


If I run this macro and select "No" then macro ends

 Sub Message()

Dim response
response = MsgBox("Have you changed the date in Cell G5?", vbYesNo)
If response = vbNo Then
Exit Sub
Else

End If
End Sub

However, if I include this just after the start another macro, then it does not exit when "No" is selected"

It would be appreciated if someone could kindly amend my code, so that when Macro "selectSourceFolder" is selected and message boxcomes yup and "No" is selected" then themacro will exit

 Sub selectSourceFolder()                     
Message


ThisWorkbook.Activate                           'start in THIS workbook
[sourceFolder].Select                           'put cellpointer in tidy location

'**********************************************
'FETCH INPUT FOLDER..
'**********************************************
zFolder = [sourceFolder]                        'fetch value from named cell
If zFolder = "" Then                            'no default Folder defined yet (or deleted)
zFolder = ThisWorkbook.Path                     'use folder location of THIS file
End If                                          'end of test for no default Folder

If Right(zFolder, 1) <> "\" Then                'check for required last \ in path
zFolder = zFolder & "\"                         'add required last \ if missing
End If                                          'end of test fro required last \ char

[sourceFolder] = zFolder                        'refresh initial default folder

On Error Resume Next                            'set error trap for next line..
ChDir zFolder                                   'try and set default folder to cell entry
On Error GoTo 0                                 'reset error trap
'**********************************************
'FETCH FILE FILTER TYPE..
'**********************************************
zFilesLikeThis = [fileMatchCell]                'fetch from named cell; e.g. "*.xls";"RZ*.*"
If zFilesLikeThis = "" Then                     'no file type filter defined
zFilesLikeThis = "*.xls*"                       'define file filter as any Excel file
[fileMatchCell] = zFilesLikeThis                'define default file filter
End If

'**********************************************
'DISPLAY FILE SELECTION BOX..
'**********************************************
With Application.FileDialog(msoFileDialogFolderPicker)          'use shortcut
saywhat = "Select the source folder for files to be moved.."    'define browser text
.Title = saywhat                                'show heading message for THIS dialog box
.AllowMultiSelect = False                       'allow only one file to be selected
.InitialFileName = zFolder                      'set default source folder
zItem = .Show                                   'display the file selection dialog

.InitialFileName = ""                           'clear and reset search folder\file filter

If zItem = 0 Then Exit Sub                      'User cancelled; 0=no sample file chosen

zFolder = .SelectedItems(1)                     'selected folder
End With                                        'end of shortcut

If Right(zFolder, 1) <> "\" Then                'check for required last \ in path
zFolder = zFolder & "\"                         'add required last \ if missing
End If                                          'end of test fro required last \ char

[sourceFolder] = zFolder                        'update named cell with folder path

End Sub

 

[HaHoBe]

Hi howard,

Exit Sub will only stop the procedure it is located in, try

Sub Message()

Dim response
response = MsgBox("Have you changed the date in Cell G5?", vbYesNo)
If response = vbNo Then
  End
End If
End Sub

But you could monitor any change for a cell automaticly without interaction with the user.

Ciao,
Holger

 

[howard]

Thanks for the help Holger

Plerase advise how one could monitor any change for a cell automatically without interaction with the user ?

 

[HaHoBe]

Hi howard,

since I don't know which events are used in the workbook one way might be to store the value of the cell in question in either a global variable or in a hidden name when opening the workbook and compare the value from there to the actual to decide whether to take action or not.

Sample may look like this. Code for ThisWorkbook:

Private Sub Workbook_Open()
  Dim varReturn As Variant
  varReturn = fncCheckContents("MySheet", "G5")
End Sub

Code in a standard module:

Sub HowardCheckG5()
  Dim objVersName       As Name
  Dim strValue          As String
  Dim blnEnd            As Boolean
  Dim varReturn         As Variant
  
  Const cstrVersion     As String = "CellG5"
  Const cstrEqual       As String = "="
  
  On Error Resume Next
  Set objVersName = ThisWorkbook.Names(cstrVersion)
  strValue = Replace(Names(cstrVersion).RefersTo, cstrEqual, Empty)
  Err.Clear
  On Error GoTo 0
  Set objVersName = Nothing
  blnEnd = False
  If strValue = CStr(Worksheets("MySheet").Range("G5").Value) Then
    blnEnd = True
  End If
  If blnEnd Then
    End
  Else
    MsgBox "on with the show...", , " "
  End If
  '... more code
  
  'send the new value to the name
  varReturn = fncCheckContents("MySheet", "G5")
End Sub
Function fncCheckContents(ShName As String, CellAddress As String) As Variant
  Dim objVersName       As Name
  
  Const cstrVersion     As String = "CellG5"
  Const cstrEqual       As String = "="
  
  On Error Resume Next
  Set objVersName = ThisWorkbook.Names(cstrVersion)
  If Err.Number > 0 Then
    Names.Add cstrVersion, Worksheets(ShName).Range(CellAddress).Value
  Else
    Names(cstrVersion).RefersTo = cstrEqual & Worksheets(ShName).Range(CellAddress).Value
  End If
  fncCheckContents = Names(cstrVersion).RefersTo
  Err.Clear
  On Error GoTo 0
  Set objVersName = Nothing
End Function

And you would need to take care of the contents of the name if any code alters the value of Cell G5 on the given sheet.

Ciao,
Holger

 

[howard]

Many Thanbks Holger

 

[HaHoBe]

Hi howard,

if no events for the sheets are used it's a lot easier: fill a public variable on opening, compare the actual value to the one being stored and change afterwards for the new value.

Holger

 

[howard]

Many thanks for your valuable input