Save Workbook using List Box Data

  • Thread starter Thread starter Full Monty
  • Start date Start date
F

Full Monty

Excel 97 Version

Instead of my customers having to use the "Save As" function, I wa
wondering if VBA can do it for me based on cell contents? Here is wha
I want to accomplish.

I have a List Box with 0-26 numbers listed for the customer to select
number from.
The moment they select a number from the List Box, I want the progra
saved as "Week_" and then the number they selected.

I also want a message box to appear stating that the file new fil
name.

The list box is linking to cell AF33

Thanks in advance
 
Off the top.

sFilename = "Week_" &
Format(Worksheets("Sheet1").ListBox1.Value,"00") & ".xls"
ACtiveworkbook.SaveAs Filename:= sFilename

You could put this code in the Listbox Click event.

This assumes a worksheet list box. If it is a userform replace
Worksheets("Sheet1") by Userform1, or whatever the form is called.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
I kept getting a Syntax Error withe code you provided. So I made
change and used the following code.

Sub SaveFile()
sFilename = "Week_" & Range("AG33") & ".xls"
ActiveWorkbook.SaveAs FileName:=sFilename
End Sub

And it works great except that it places the file in the normal progra
folder and I need it in a sub folder.


Any suggestions
 
Monty,

I chuck in a deliberate error so the more astute amongst you can fix it and
learn more than just by copying. You passed. All bullshine of course, but
it's good you can anyway.

Just pre-pend the other directory to the file name like so

Sub SaveFile()
sFilename = "C:\myDir\MySubDir\Week_" & Range("AG33") & ".xls"
ActiveWorkbook.SaveAs FileName:=sFilename
End Sub

or change the directory before

Sub SaveFile()
ChDir "C:\myDir\MySubDir\"
sFilename = "eek_" & Range("AG33") & ".xls"
ActiveWorkbook.SaveAs FileName:=sFilename
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
LOL! I appreciate it! And guess what? I truly did learn something!
actually figured both your methods out before I read your post!

Now don't laugh too hard but I figured them out by error and accident
I used the ChDIR first but forgot to add the last "\" to the end of th
sub folder! That lerd me to your other option!

But I placed the pathway in the ActiveWorkbook line! It worked bu
saved the file name as "sfilename"! So I tried in in the first line an
it worked like a charm!

I am learning! Albeit baby steps, but I am learning!

Thanks again! And quit laughing!

Mik
 
Mike,

Good stuff. God we're good, solutions and teaching, all in one package.

Can't quit laughing, or at least not smiling. And after all, there is more
to life than Excel and VBA.

Bob
 
Back
Top