SOLVED!Templates-Can I assign cell ref as Filename Automatic

[ROBINSYN]

I need to have the filename match the Invoice No. in cell N2 each time a new record is saved. As it is now I have to go to File ans select saveas to name each sheet,not a problem for me but others may just save over other files and destroy the purpose of the database.

Please Help Me!
This message was edited by ROBINSYN on 2002-10-27 18:12

 

[SteveC]

Robinsyn,
This is part of VBA that I have tested and used. 99% of it was contributed my many people, mostly from MrExcel (I don't know how to create code myself.). If it doesn't work, I can send you the entire code in case I left something out by mistake. This is the first time that I've been able to give back!! And, ironically, just a few hours ago you helped me!
SteveC
-------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' frm RockyH031202
Dim Path As String ' path of current worksheet
Dim ThisFileNew As String ' new file name including path
Dim Resp As Integer ' user response to overwrite query
Dim fname As String ' to DEL old file (frm NateO031202 MrExcel)
fname = ActiveWorkbook.FullName ' to DEL old file (frm NateO031202 MrExcel)

If Not Intersect(Target(1), Range("N2")) Is Nothing Then
With Application
.EnableEvents = False
.DisplayAlerts = False
End With
On Error Resume Next

' Set cell contents (file name) to upper case --In case you have a letter in invoice #
Target.Value = UCase(Target.Text)

' Get current path (empty if workbook has never been saved)
Path = ThisWorkbook.Path
If Not Path = "" Then Path = Path & ""
ThisFileNew = Path & Target.Text & ".xls"
Resp = vbOK

' Check for existing file of same name and, if present, ask whether to overwrite
' (frm Damon Ostrander 08220 2-22:38 mrexcel)
If Dir(ThisWorkbook.Path & "\" & Target.Text & ".xls") <> "" Then
Resp = MsgBox("This file already exists. Overwrite? ", vbExclamation + vbOKCancel)
End If

' Save the workbook if file does not exist, or if user wants to overwrite it
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Kill fname ' to DEL old file (frm NateO 031202 mrexcel)
Else
Resp = MsgBox("You will need to rename this file manually", vbInformation)
End If

On Error GoTo 0
With Application
.DisplayAlerts = True
.EnableEvents = True
End With
End If
End Sub

 

[ROBINSYN]

Thanks Steve.

I'm having a bit of difficulty too. Just know basic excel. Only started using macros and such couple of weeks ago. Just what I see and learn here. It would be great if you could email the file. I seem to learn and understand better when I can see things in action.

Address: SynDee@sympatico.ca

Greatly Appreciated

 

[SteveC]

Robinsyn,
I thank you for the opportunity to finally help someone. It will be a few minutes since I need to back out some info and stuff.

This Excel "project" that I'm doing to help out a good friend, is my first and only thing I've worked on. All I've learned has been from doing it, getting help here and reading the posts here. I've recieved a little help from the Microsoft Excel boards, but MrExcel is by far the best for me. (I will go to the Microsoft site out of guilt -when I feel I've been asking for help here too frequently.)
Before I started this project, I didn't even know what a formula was, much less how to sum.
No classes or books - the hard way, I guess.
Yeah, I can understand!

I'll work on getting this ready for you now. There must be better code to do all that I'm doing because I asked for help for the pieces of it and then combined it myself (I'm sure it would be scary to these experts!) Well, that was my disclaimer.

SteveC