How can I make this work

[Emoncada]

I have this

[VBA]Sub SaveForm()
Dim res As String
ActiveWorkbook.Save
res = MsgBox("Does this Spreadsheet need a Signature?", vbYesNoCancel, "Signature Request")

Select Case res

Case vbYes

FrmSignatureNames.Show


Case vbNo

If Range("AA1").Value = "Available" Then
Call SaveAvailable

Else
If Range("AA2").Value = "Retire" Then
Call SaveRetire

'-------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------
Else
If Range("AA1", "AA2").Value = "" Then
Dim fp As String, strSaveFile As String

FilePath = ""
'fp = "C:\Equipment Tracking\WIP\"
fp = "S:\Equipment Tracking\WIP\"
Call MakeFolders(fp)
Call MakeFolders(Range("K3") & "\")
Call MakeFolders(Range("K5") & "\")


Application.DisplayAlerts = False
strSaveFile = Range("B6").Value & ".xls"
Application.DisplayAlerts = True

ActiveWorkbook.SaveAs FilePath & strSaveFile, xlWorkbookNormal
FilePath = ""

'Kill ("C:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")
Kill ("S:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")
End If

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

ActiveWorkbook.Save
ActiveWorkbook.Close

Case vbCancel

Exit Sub

End Select

End Sub[/VBA]


It's Giving Me a problem with the VbNo case I guess because I have to many If Statements.

Is there a way I can make this work.

[Paul_Hossler]

Not tested but maybe you can try this, and fix anything that still needs fixin'


Paul

[vba]
Sub SaveForm()
Dim res As String
ActiveWorkbook.Save
res = MsgBox("Does this Spreadsheet need a Signature?", vbYesNoCancel, "Signature Request")

Select Case res

Case vbYes

FrmSignatureNames.Show


Case vbNo

If Range("AA1").Value = "Available" Then
Call SaveAvailable

ElseIf Range("AA2").Value = "Retire" Then '<<<<<<<<<<<<<<
Call SaveRetire

'-------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------
ElseIf Range("AA1", "AA2").Value = "" Then '<<<<<<<<<<<<<<
Dim fp As String, strSaveFile As String

FilePath = ""
'fp = "C:\Equipment Tracking\WIP\"
fp = "S:\Equipment Tracking\WIP\"
Call MakeFolders(fp)
Call MakeFolders(Range("K3") & "\")
Call MakeFolders(Range("K5") & "\")


Application.DisplayAlerts = False
strSaveFile = Range("B6").Value & ".xls"
Application.DisplayAlerts = True

ActiveWorkbook.SaveAs FilePath & strSaveFile, xlWorkbookNormal
FilePath = ""

'Kill ("C:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")
Kill ("S:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")
End If

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

ActiveWorkbook.Save
ActiveWorkbook.Close

Case vbCancel

Exit Sub

End Select

End Sub
[/vba]

[Emoncada]

I am having a problem with I believe this part of the code
[VBA]ElseIf Range("AA1", "AA2").Value = "" Then[/VBA]

It works fine with
[VBA]If Range("AA1").Value = "Available" Then
Call SaveAvailable

ElseIf Range("AA2").Value = "Retire" Then '<<<<<<<<<<<<<<
Call SaveRetire
[/VBA]

But when I select No and these two value's are "" it fails. These cells have formula's in them I don't know if that would affect this from working.

[Emoncada]

ok it seems to work if I just eliminate the Range and just leave it with Else.

[Paul_Hossler]

I've never done it that way, but try this to see


[VBA]

ElseIf Range("AA1") = "" and Range(""AA2").Value = "" Then

[/VBA]