Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
get these to work together....
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. --- Regards, Norman "jeramie" wrote in message ... 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
get these to work together....
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" wrote: 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. --- Regards, Norman "jeramie" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
get these to work together....
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. --- Regards, Norman "jeramie" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |