megha
03-11-2014, 01:06 PM
I am using the following code to auto saveas the file using the command button. It is working fine but I am looking to edit the code to use three of my combo boxes' value in my file name. In other words, I have three combo boxes in that same file. I want to have those combo boxes values to appear in file name when I saveAs the file using the autosave comand button along with the date and time which i have already have. How can i do that? Can someone please help? I know i will have to add the name of my combo boxes into my code but I am totally blank as I am new to this, Thank you so much in advance!
Private Sub CommandButton1_Click()
Dim sFileName As String
Dim sPath As String
CommandButton1.Enabled = False
sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
If Len(Dir("\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy"), vbDirectory)) = 0 Then
MkDir "\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy")
End If
sPath = "\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy")
sFileName = sFileName & ".xls"
ActiveWorkbook.SaveAs Filename:=sPath & "\" & sFileName, FileFormat:=xlNormal, ReadOnlyRecommended:=False
ThisWorkbook.Close SaveChanges:=False
End Sub
Private Sub CommandButton1_Click()
Dim sFileName As String
Dim sPath As String
CommandButton1.Enabled = False
sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
If Len(Dir("\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy"), vbDirectory)) = 0 Then
MkDir "\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy")
End If
sPath = "\\sptds.sabert.net/sites/op/Shared (file://sptds.sabert.net/sites/op/Shared) Documents/SOL CHECKLIST - ALL SECTIONS/Section A/Completed_ SOL Checklist\" & Format(DateValue(Now()), "mmm_yyyy")
sFileName = sFileName & ".xls"
ActiveWorkbook.SaveAs Filename:=sPath & "\" & sFileName, FileFormat:=xlNormal, ReadOnlyRecommended:=False
ThisWorkbook.Close SaveChanges:=False
End Sub