SaveAs Error

T

tigerdel

New member
Joined
Aug 23, 2012
Messages
40
Reaction score
0
Points
0
Location
Cambridgeshire
Excel Version(s)
Office 365
I have tried to get the below code working but I have obviously done something wrong as although I get no error messages I am not getting the second part to trigger

The first part does what it should do bit the second part doesn't trigger the msgboxes

Any ideas what I am doing wrong??

Code: 
[/COLOR]
[COLOR=#333333]Sub SaveSheet()[/COLOR]
[COLOR=#333333]Range("BB1").Select[/COLOR]
[COLOR=#333333]If Range("BB1").Value = "TRUE" Then 'check cell content and if TRUE trigger msgbox[/COLOR]
[COLOR=#333333]MsgBox ("Your DCW contains Data" & vbNewLine & "Ensure you have saved your file before closing")[/COLOR]
[COLOR=#333333]If MsgBox("To Save your work, Click Yes" & vbNewLine & "To quit without saving, Click No", vbYesNo) = vbYes Then[/COLOR]
[COLOR=#333333]Application.Dialogs(xlDialogSaveAs).Show "C:\Temp\" & Sheets("DCW").Range("E1").Value & " - " & Format(Date, "dd-mmm-yyyy") & ".xlsb"[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Range("BB1").Select[/COLOR]
[COLOR=#333333]If Range("BB1").Value = "FALSE" Then 'If contains FALSE[/COLOR]
[COLOR=#333333]MsgBox ("You can Close this WorkBook without saving")[/COLOR]
[COLOR=#333333]If MsgBox("To Save your work, Click Yes" & vbNewLine & "To quit without saving, Click No", vbYesNo) = vbYes Then[/COLOR]
[COLOR=#333333]Application.Dialogs(xlDialogSaveAs).Show "C:\Temp\" & Sheets("DCW").Range("E1").Value & " - " & Format(Date, "dd-mmm-yyyy") & ".xlsb"[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]MsgBox ("This application will now close")[/COLOR]
[COLOR=#333333]ActiveWorkbook.Close False[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]
[COLOR=#333333]
 
Last edited by a moderator:
Try this

Code: 
Sub SaveSheet()
    If Range("BB1").Value = "TRUE" Then 'check cell content and if TRUE trigger msgbox
        MsgBox ("Your DCW contains Data" & vbNewLine & "Ensure you have saved your file before closing")
        If MsgBox("To Save your work, Click Yes" & vbNewLine & _
                  "To quit without saving, Click No", vbYesNo) = vbYes Then
            Application.Dialogs(xlDialogSaveAs).Show "C:\Temp\" & Sheets("DCW").Range("E1").Value & " - " & _
                                                     Format(Date, "dd-mmm-yyyy") & ".xlsb"
        Else
            MsgBox ("This application will now close")
            ActiveWorkbook.Close False
        End If
    ElseIf Range("BB1").Value = "FALSE" Then 'If contains FALSE
        MsgBox ("You can Close this WorkBook without saving")
        If MsgBox("To Save your work, Click Yes" & vbNewLine & _
                  "To quit without saving, Click No", vbYesNo) = vbYes Then
            Application.Dialogs(xlDialogSaveAs).Show "C:\Temp\" & Sheets("DCW").Range("E1").Value & " - " & _
                                                     Format(Date, "dd-mmm-yyyy") & ".xlsb"
        Else
            MsgBox ("This application will now close")
            ActiveWorkbook.Close False
        End If
    End If
End Sub
 
Absolute star
May be you can also help with post
Copy Sheet to New File but Remove all Buttons
 
You must log in or register to reply here.
Back
Top