PDA

View Full Version : How can I make this work



Emoncada
01-23-2008, 02:50 PM
I have this

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


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
01-23-2008, 06:20 PM
Not tested but maybe you can try this, and fix anything that still needs fixin'


Paul


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

Emoncada
01-24-2008, 06:37 AM
I am having a problem with I believe this part of the code
ElseIf Range("AA1", "AA2").Value = "" Then

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

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


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
01-24-2008, 06:38 AM
ok it seems to work if I just eliminate the Range and just leave it with Else.

Paul_Hossler
01-24-2008, 06:01 PM
I've never done it that way, but try this to see




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