Using an Excel VBA msgbox

Question

I have this code running and it works however the MSGBOX pops up each time for each d. I would like for it to just run once if answered yes instead of asking each time. How can I get this done?

For d = 2 To 10
    destination_folder = Trim(aw.Worksheets(1).Range("A" & d).Value)
    Dim strFileName As String
    Dim strFileExists As String
    
        strFileName = destination_folder & monthandyear
        strFileExists = Dir(strFileName)
    
       If strFileExists = "" Then
            If MsgBox("The file doesn't exist would you like to create one for " & monthandyear & "?", vbYesNo) = vbNo Then Exit Sub
        Else
            If MsgBox("The selected file exists", vbOKOnly) = vb Then Exit Sub
        End If
    
    Set FSO = CreateObject("Scripting.filesystemobject")
    FSO.Copyfile (source_folder & source_file), destination_folder & monthandyear, True
    
    Next
    
    End Sub

Warcupine · Accepted Answer · 2021-02-08 19:19:04Z

Store the value of the message box in a variable and check that value. 6 = Yes, 7 = No

For d = 2 To 10
    destination_folder = Trim(aw.Worksheets(1).Range("A" & d).Value)
    Dim strFileName As String
    Dim strFileExists As String
    Dim yesno As Long
    
        strFileName = destination_folder & monthandyear
        strFileExists = Dir(strFileName)
        
        If strFileExists = "" Then
            If Not yesno = 6 Then
                yesno = MsgBox("The file doesn't exist would you like to create one for " & monthandyear & "?", vbYesNo)
                If yesno = 7 Then Exit Sub
            End If
        Else
            If MsgBox("The selected file exists", vbOKOnly) = vb Then Exit Sub
        End If
    
    Set FSO = CreateObject("Scripting.filesystemobject")
    FSO.Copyfile (source_folder & source_file), destination_folder & monthandyear, True
    
    Next

Collectives™ on Stack Overflow

Using an Excel VBA msgbox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related