Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
I think I am trying to do too much in one line but I am not sure how to write
it correctly. I want the user to be able to click on cell P1 and then a message box ask if they have completed the input - If yes, then save the file name and date, if no then end sub. I am getting the error message above and can't figure it out. Can anyone help? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then ActiveWorkbook.SaveAs Filename:=Range("A1").Value & Format(Worksheets("Daily").Range("ax1").Value, "yyyy-mm-dd") & ".xls" End If End Sub Thanks Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
Hi,
Try removing the End If at the end of the sub. Your If Then statements are only one line of code each, so the End If is not required. Regards - Dave. "Carrie_Loos via OfficeKB.com" wrote: I think I am trying to do too much in one line but I am not sure how to write it correctly. I want the user to be able to click on cell P1 and then a message box ask if they have completed the input - If yes, then save the file name and date, if no then end sub. I am getting the error message above and can't figure it out. Can anyone help? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then ActiveWorkbook.SaveAs Filename:=Range("A1").Value & Format(Worksheets("Daily").Range("ax1").Value, "yyyy-mm-dd") & ".xls" End If End Sub Thanks Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
You know, I started off that way but then the macro just doesn't respond, I
can't even get the cursur to move with striking the enter key. So I thought it was an End If problem but darn it I guess not. Any other suggestions? Dave wrote: Hi, Try removing the End If at the end of the sub. Your If Then statements are only one line of code each, so the End If is not required. Regards - Dave. I think I am trying to do too much in one line but I am not sure how to write it correctly. I want the user to be able to click on cell P1 and then a [quoted text clipped - 15 lines] Thanks Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
I'd try:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = me.Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then me.parent.SaveAs Filename:=me.Range("A1").Value & _ Format(me.parent.Worksheets("Daily") _ .Range("ax1").Value, "yyyy-mm-dd") & ".xls" End if End If End Sub Personally, I avoid those if/then one liners. I like the block if/then/else structures. And you sure you want to check to see if the user hit the No button? me is the worksheet with the code. me.parent is the workbook that owns the worksheet with the code. I like that better than using Activeworkbook. "Carrie_Loos via OfficeKB.com" wrote: You know, I started off that way but then the macro just doesn't respond, I can't even get the cursur to move with striking the enter key. So I thought it was an End If problem but darn it I guess not. Any other suggestions? Dave wrote: Hi, Try removing the End If at the end of the sub. Your If Then statements are only one line of code each, so the End If is not required. Regards - Dave. I think I am trying to do too much in one line but I am not sure how to write it correctly. I want the user to be able to click on cell P1 and then a [quoted text clipped - 15 lines] Thanks Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
Hi
After removing the End If, put a breakpoint in at the first line of code. Then when you change the value of P1, the VB window should appear, and from there you can 'F8' through the code to see what's happening (or not happening). Dave "Carrie_Loos via OfficeKB.com" wrote: You know, I started off that way but then the macro just doesn't respond, I can't even get the cursur to move with striking the enter key. So I thought it was an End If problem but darn it I guess not. Any other suggestions? Dave wrote: Hi, Try removing the End If at the end of the sub. Your If Then statements are only one line of code each, so the End If is not required. Regards - Dave. I think I am trying to do too much in one line but I am not sure how to write it correctly. I want the user to be able to click on cell P1 and then a [quoted text clipped - 15 lines] Thanks Carrie -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
Try this revision.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As String If Target.Address = Range("P1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbYes Then ActiveWorkbook.SaveAs Filename:=Worksheets("Daily").Range("A1").Value & _ Format(Worksheets("Daily").Range("AX1").Value, "yyyy-mm-dd") & ".xls" End If End If End Sub Gord Dibben MS Excel MVP On Tue, 13 May 2008 22:14:22 GMT, "Carrie_Loos via OfficeKB.com" <u34134@uwe wrote: I think I am trying to do too much in one line but I am not sure how to write it correctly. I want the user to be able to click on cell P1 and then a message box ask if they have completed the input - If yes, then save the file name and date, if no then end sub. I am getting the error message above and can't figure it out. Can anyone help? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then ActiveWorkbook.SaveAs Filename:=Range("A1").Value & Format(Worksheets("Daily").Range("ax1").Value, "yyyy-mm-dd") & ".xls" End If End Sub Thanks Carrie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
Thanks - This look like it will work. And to answer your question, the "No"
button is in fact backwards should be Yes. Out of curiosity - why avoid the one liners? Too many issues? or just better housekeeping? Maybe both? Dave Peterson wrote: I'd try: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = me.Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then me.parent.SaveAs Filename:=me.Range("A1").Value & _ Format(me.parent.Worksheets("Daily") _ .Range("ax1").Value, "yyyy-mm-dd") & ".xls" End if End If End Sub Personally, I avoid those if/then one liners. I like the block if/then/else structures. And you sure you want to check to see if the user hit the No button? me is the worksheet with the code. me.parent is the workbook that owns the worksheet with the code. I like that better than using Activeworkbook. You know, I started off that way but then the macro just doesn't respond, I can't even get the cursur to move with striking the enter key. So I thought [quoted text clipped - 15 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
End if without Block If
The issues are all with me (with the one liners).
I find it easy to miss them when I'm reading existing code. I guess it's more of a style choice. "Carrie_Loos via OfficeKB.com" wrote: Thanks - This look like it will work. And to answer your question, the "No" button is in fact backwards should be Yes. Out of curiosity - why avoid the one liners? Too many issues? or just better housekeeping? Maybe both? Dave Peterson wrote: I'd try: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ans As Long If Target.Address = me.Range("p1").Address Then ans = MsgBox("Are you finished inputing Daily Info?", vbYesNo) If ans = vbNo Then me.parent.SaveAs Filename:=me.Range("A1").Value & _ Format(me.parent.Worksheets("Daily") _ .Range("ax1").Value, "yyyy-mm-dd") & ".xls" End if End If End Sub Personally, I avoid those if/then one liners. I like the block if/then/else structures. And you sure you want to check to see if the user hit the No button? me is the worksheet with the code. me.parent is the workbook that owns the worksheet with the code. I like that better than using Activeworkbook. You know, I started off that way but then the macro just doesn't respond, I can't even get the cursur to move with striking the enter key. So I thought [quoted text clipped - 15 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mental block | Excel Worksheet Functions | |||
Average per block | Excel Worksheet Functions | |||
Block Protect | Excel Discussion (Misc queries) | |||
VBA error - End If without Block If | Excel Worksheet Functions | |||
Block entries | Excel Worksheet Functions |