On 2002-03-11 15:00, Anonymous wrote:
GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet.
DETAILS: The cell with the new file name is always Y1. The cell is blank initially (although it doesn't have to be).
OTHER INFO: OS= WIN 98 Excel= 2000 I'm new to VBA and macros.
EXAMPLE: If I input "STEVE C" into cell Y1, the name of the file will be STEVE C.xls
(I am not worried about the case in the file name itself. It would, however, be a benifit to have the contents of Y1 be all uppercase automatically - if that is also possible.)
Thank you for your help. This is the first time I've been here.
Steve C
I'll throw my 2 cents in (I started this about an hour ago and got called away)...
Well, you could use Data Validation for the upper-case value in the cell, but since code is required for this, we'll just incorporate it there.
You could put the code in the Worksheet_Change event. What this will do is save each time the cell Y1 is changed (not sure that this is what you want, but it's a good example). To do this, open the VBE window (Alt+F11 or Tools-Macro-Visual Basic Editor). When that opens you should see a "Project" window on the left-hand side. Find your workbook and click on the + so that you "open the folder", and you should see a folder that says "Microsoft Excel Objects". Open that one, and you should see each sheet and "ThisWorkbook". Double-Click on the sheet that you want to use to rename your workbook. This should bring up a blank window on the right side. In the drop-down at the top of this window that says "(General)" (it will be on the left side), select "Worksheet". This will bring up the shell of a function, and should look like this:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Change the drop-down on the right from SelectionChange to Change. You'll get a shell that looks exactly like the first, but the word "Selection" will not be there. Then you could put something like this in for your code:
<pre><font color='#000000'>
<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> Worksheet_Change(ByVal Target <font color='#000080'>As</font> Range)
<font color='#000080'>If</font> Target.Address = "$Y$1" <font color='#000080'>Then</font>
<font color='#000080'>If</font> <font color='#000080'>Not</font> Target.Text = "" <font color='#000080'>Then</font>
Application.EnableEvents = False
Target.Value = UCase(Target.Text) <font color='#008000'>' change to uppercase</font>
<font color='#000080'>If</font> UCase(Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)) = Target.Text <font color='#000080'>Then</font>
<font color='#000080'>Exit</font> <font color='#000080'>Sub</font>
<font color='#000080'>Else</font>
ThisWorkbook.SaveAs ThisWorkbook.Path & "" & Target.Text & ".xls"
<font color='#000080'>End</font> <font color='#000080'>If</font>
Application.EnableEvents = True
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>Sub</font>
</font></pre>
Hope this helps,
Russell