get these to work together....

[Guest]

I got some help earlier and i have this working:

Public Sub Macro1()
With ActiveWorkbook
.SaveAs Filename:="(filepath)" & "\" &
Worksheets("sheet1").Range("A3").Text & Worksheets("sheet1").Range("A5").Text
& ".xls"

End With
End Sub


but i need it to run ONLY if this is true:

If Target.Address = "A3" & Target.Address = "A5" And Target.Value = Not Null?

I hope that is right. Also if A3 (name) or A5 (date) are null, can i get it
to prompt for those values?

 

[Norman Jones]

Hi Jeramie,

Try:

'=============>>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim rCell As Range
Dim sPath As String

sPath = _
"C:\Documents and Settings\NDJ\My Documents" '<<== CHANGE

If Right(sPath, 1) <> "\" Then
sPath = sPath & "\"
End If

Set rng = Me.Range("A3,A5")

If Not Intersect(Target, rng) Is Nothing Then
If Application.CountA(rng) < rng.Count Then
For Each rCell In rng.Cells
With rCell
If IsEmpty(.Value) Then
.Value = InputBox("enter a value for cell " _
& .Address(0, 0))
End If
End With
Next rCell
End If

On Error GoTo XIT
ThisWorkbook.SaveAs Filename:=sPath & Me.Range("A3").Text _
& Me.Range("A5").Text & ".xls"
End If

Exit Sub

XIT:

MsgBox "The File could not be saved - " & _
"Check that the cell values are valid"

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Guest]

works perfectly! thanks!!

One more minor thing..... the form has a text box in it (inserted where i
cut out some cells). when I get to the last line in the box, how do I get it
to make a "page 2", where the text continues at the top of the text box on
the second page?

 

[Norman Jones]

Hi Jeramie,

As this addendum has no obvious connection with the original question, and
in order to maximise your prospects of a useful response, I would suggest
that you initiate a new thread.