PDA

View Full Version : [SOLVED] VBA Auto SAVE - need help!



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

mancubus
03-11-2014, 11:47 PM
hi. try this:
sFileName = sFileName & "_" & ComboBox1.Value & "_" & ComboBox2.Value & "_" & ComboBox3.Value & ".xls"

megha
03-12-2014, 01:59 PM
hi. try this:
sFileName = sFileName & "_" & ComboBox1.Value & "_" & ComboBox2.Value & "_" & ComboBox3.Value & ".xls"

Thank you so much! This is working perfect! One more little change - instead of comboBox3 value i want to include value of cell I42. How do i do that? Basically Cell I42 include two to four digit number - total of the data.

mancubus
03-12-2014, 02:16 PM
you are welcome.


sFileName = sFileName & "_" & ComboBox1.Value & "_" & ComboBox2.Value & "_" & Worksheets("MyWorksheetNameHere").Range("I42").Value & ".xls"

megha
03-12-2014, 02:28 PM
Thank you so much! It is working like a magic. You are the best. Thanks a lot!

mancubus
03-12-2014, 02:44 PM
i am certainly not. :)

but thanks for the compliment.

im glad it helped.