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

ROBINSYN

Board Regular
Joined
Aug 19, 2002
Messages
188
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

SteveC

Board Regular
Joined
Mar 14, 2002
Messages
118
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

Board Regular
Joined
Aug 19, 2002
Messages
188
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

Board Regular
Joined
Mar 14, 2002
Messages
118
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
 

Forum statistics

Threads
1,143,746
Messages
5,720,619
Members
422,293
Latest member
camillel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top