Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 116
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
mental block Lost in excel Excel Worksheet Functions 3 January 14th 07 09:18 PM
Average per block PP Excel Worksheet Functions 4 November 30th 06 08:22 PM
Block Protect RhysPieces Excel Discussion (Misc queries) 3 May 5th 06 06:32 PM
VBA error - End If without Block If Jane Excel Worksheet Functions 2 December 6th 05 07:00 PM
Block entries Boenerge Excel Worksheet Functions 2 May 5th 05 05:20 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"