Save Workbook using List Box Data
 

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

--
Message posted from http://www.ExcelForum.com

Save Workbook using List Box Data
 

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)

"Full Monty " wrote in message
...
Excel 97 Version

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

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

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

The list box is linking to cell AF33

Thanks in advance!


---
Message posted from http://www.ExcelForum.com/

Save Workbook using List Box Data
 

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

--
Message posted from http://www.ExcelForum.com

Save Workbook using List Box Data
 

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)

"Full Monty " wrote in message
...
Bob,
I kept getting a Syntax Error withe code you provided. So I made a
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 program
folder and I need it in a sub folder.


Any suggestions?


---
Message posted from http://www.ExcelForum.com/

Save Workbook using List Box Data
 

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

--
Message posted from http://www.ExcelForum.com

Save Workbook using List Box Data
 

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

"Full Monty " wrote in message
...
Bob,
LOL! I appreciate it! And guess what? I truly did learn something! I
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 the
sub folder! That lerd me to your other option!

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

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

Thanks again! And quit laughing!

Mike


---
Message posted from http://www.ExcelForum.com/