Skip Code Lines & Go To Another

khenzel · Jul 12th 2007

just wondering how i can get my GoTo function working in this VBA macro.

Code

'Get the relative column number where the criteria should be found
    If vCriteria = vbYes Then GoTo 39

should that not shoot me back to line 39? i get a error "label not defined" as it is. also tried to set a :AddNew flag at line 39 and point the GoTo to AddNew. the same thing happens. any help? this is bugging me as i'm used to this command working... doesnt like it in vba. perhaps i'm rusty?

oh and one last thing... this code is at the end of the program. i'm trying to point it back tot he top to enable the user to add more email recipients. how can that redirect happen if my goto isnt working for me?

Anonymous · Jul 12th 2007

Re: Goto Command?

You need to provide a name label for the GoTo. (But note that this can lead to bad programming structure. Make sure you aren't creating an infinite loop.)

Code

Sub test()
Dim i As Integer
i = 1
MyLine:
MsgBox "There"
i = i + 1
If i < 3 Then GoTo MyLine
End Sub

Fencliff · Jul 12th 2007

Re: Goto Command?

Hi,

If you haven't done so, you need to define the line number you want to refer to in the beginning of the line like this:

Code

If vCriteria = vbYes Then Goto 39


'...code...


39 'Jump here if vCriteria returns vbYes

Or for Line label:

Code

If vCriteria = vbYes Then Goto  myLabel


'...code...


myLabel:
'Jump here if vCriteria returns vbYes

Personally, though, I don't like using GoTo too much, as it makes following the code and controlling your variables really hard in the end of the day. If you can give a little more specific description of what you are trying to do, perhaps I can help.

Rgds,
Jani

khenzel · Jul 12th 2007

Re: Goto Command?

Quote from thomach
You need to provide a name label for the GoTo. (But note that this can lead to bad programming structure. Make sure you aren't creating an infinite loop.)
Code
Sub test()
Dim i As Integer
i = 1
MyLine:
MsgBox "There"
i = i + 1
If i < 3 Then GoTo MyLine
End Sub

tried it. processed it w/o error BUT did not send me back... just pushed on and finished the code. =( why is it not looping me back i wonder? any ideas? here is the full code:

Code

Sub Email()
'
' Email Macro
' Macro recorded 7/11/2007 by KHenzel
'
' Keyboard Shortcut: Ctrl+Shift+E
'


Dim vCriteria As String
Dim vCriteria2 As String
Dim vCriteria3 As String
Dim vCriteria4 As String
Dim vCriteria5 As Boolean
Dim vCriteria6 As Boolean
Dim intNumeric As Integer


intNumeric = 1


' Entry dialogue


MsgBox "This macro will email the Excel spreadsheet currently opened," & Chr(13) & _
"allowing you to customize the file name, recipient, and subject before delivery." _
& Chr(13) & "Press OK to continue.", vbDefaultButton1, "Ferguson Enterprises, Inc."




 'Make a copy of the active  sheet and  save it to
 'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
TempFilePath = Environ$("temp") & "\"


' Get file name info
vCriteria = Application.InputBox(Prompt:="Type in the file name for the report " _
& "you wish to send, or shift+click a cell for quick naming." & Chr(13) & _
"*Date will be auto inserted at the end of the file name*" & Chr(13) _
& Chr(13) & "~~REQUIRED FIELD~~", Title:="File Name")


'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
If vCriteria = vbNullString Then
  MsgBox "No file name specified. Aborting.", vbCritical, "Error"
  Exit Sub
End If


NewRecipient:


' Get Email recipient info
vCriteria2 = Application.InputBox(Prompt:="Enter (L) for Location email list, (I) for" _
 & " Individual email list, or (C) for custom email." & Chr(13) & Chr(13) & _
 "If you wish to enter your own" & " recipient, simply hit OK.", Title:="Email Recipient")
vCriteria2 = UCase(vCriteria2)


'Go no further if they Cancel.
If vCriteria2 = "False" Then Exit Sub




' Custom Email Address
If vCriteria2 = "C" Then
vCriteria3 = InputBox(Prompt:="Please type in the full email address of the recipient.", _
Title:="Custom Email Address")
vCriteria5 = True
End If


' Location selection prompt
If vCriteria2 = "L" Then
vCriteria3 = InputBox(Prompt:="Type in the code for the recipient you" _
& " wish to send the file to:" & Chr(13) & "ALB - ALBUQUERQUE" & Chr(13) & _
"PIT - Pittsburg" & Chr(13) & "COL - Colorado" & Chr(13) & "ELP - EL PASO" _
& Chr(13) & "FRE - FRESNO" & Chr(13) & "SOC - SOCAL" & Chr(13) & "PHO - PHOENIX" _
& Chr(13) & "SAC - SACRAMENTO" & Chr(13) & "SAL - SALINAS" & Chr(13) & _
"SLO - SAN LUIS OBISPO" & Chr(13) & "TUC - TUCSON", Title:="Location Email List")
End If
vCriteria3 = UCase(vCriteria3)


'Go no further if they Cancel.
If vCriteria3 = "False" Then Exit Sub




' Individual selection prompt
If vCriteria2 = "I" Then
vCriteria3 = InputBox(Prompt:="Type in the code for the individual you" _
& " wish to send the file to:", Title:="Individual Email List")
End If
vCriteria3 = UCase(vCriteria3)


'Go no further if they Cancel. Exit Sub




' Get subject title
vCriteria4 = Application.InputBox(Prompt:="Type in the subject for the report " _
& "you wish to send." & Chr(13) & Chr(13) & "If you wish to auto name your" _
& " subject to:" & Chr(13) & "File Attachment: <File Name> <Date>.xls" _
& Chr(13) & "Simply hit OK.", Title:="Email Subject")


If vCriteria4 = "" Then
  vCriteria4 = "File Attachment: " & vCriteria & " " & Format(Now, "mm.dd.yy") & ".xls"
End If


'Go no further if they Cancel.
If vCriteria4 = "False" Then Exit Sub




Filename = vCriteria & " " & Format(Now, "mm.dd.yy") & ".xls"
 'FileName = "Temp.xls"
On Error Resume Next
Kill TempFilePath & Filename
 'Kill "C:\" & FileName
 
On Error GoTo 0
WB.SaveAs TempFilePath & Filename
 'WB.SaveAs FileName:="C:\" & FileName
 
 'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail




' Location emails
If vCriteria3 = "ALB" Then
    .To = ""
End If
If vCriteria3 = "PIT" Then
    .To = ""
End If
If vCriteria3 = "DEN" Then
    .To = ""
End If
If vCriteria3 = "ELP" Then
    .To = ""
End If
If vCriteria3 = "FRE" Then
    .To = ""
End If
If vCriteria3 = "SOC" Then
    .To = ""
End If
If vCriteria3 = "PHO" Then
    .To = ""
End If
If vCriteria3 = "SAC" Then
    .To = ""
End If
If vCriteria3 = "SAL" Then
    .To = ""
End If
If vCriteria3 = "SLO" Then
    .To = ""
End If
If vCriteria3 = "TUC" Then
    .To = ""
End If


'Individual emails
If vCriteria3 = "JG" Then
    .To = ""
End If
If vCriteria3 = "KH" Then
    .To = ""
End If
If vCriteria3 = "TR" Then
    .To = ""
End If
If vCriteria3 = "KB" Then
    .To = ""
End If
If vCriteria3 = "BM" Then
    .To = ""
End If
If vCriteria3 = "RW" Then
    .To = ""
End If


'Custom Email
If vCriteria5 = True Then
    .To = vCriteria3
End If


'If the recipient is left blank then proceed
If vCriteria3 = "" Then
End If
    
'Add more recipients?
    vCriteria6 = MsgBox("Do you wish to add more email recipients?", _
      vbYesNo, "Ferguson Enterprises, Inc.")


       
    'Get the relative column number where the criteria should be found
    If vCriteria6 = vbYes Then
      intNumeric = intNumeric + 1
      If intNumeric > 1 Then GoTo NewRecipient
    End If
     'Uncomment the line below to hard code a subject
    .Subject = vCriteria4
    .Attachments.Add WB.FullName
    .Display
End With


'Finished dialogue
    MsgBox "Your message has been processed and is ready for delivery. Exiting.", _
    vbDefaultButton1, "Ferguson Enterprises, Inc."


 
     'Wait one second before sending keystrokes
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys "%s"
     
     'Delete the temporary file
    WB.ChangeFileAccess Mode:=xlReadOnly
    Kill WB.FullName
    WB.Close savechanges:=False
     
     'Restore screen updating and release Outlook
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
    




End Sub

Display More

if you see my code... basically what i'm trying to do is direct it back for an option to add more entries to the TO: field of my email. as the code runs now, it just shoots me right past the GOTO and pushes me on to the email window. any fixes?

thanks again

khenzel · Jul 12th 2007

Re: Goto Command?

Quote from Fencliff
Hi,

If you haven't done so, you need to define the line number you want to refer to in the beginning of the line like this:
Code
If vCriteria = vbYes Then Goto 39


'...code...


39 'Jump here if vCriteria returns vbYes
Or for Line label:
Code
If vCriteria = vbYes Then Goto  myLabel


'...code...


myLabel:
'Jump here if vCriteria returns vbYes
Personally, though, I don't like using GoTo too much, as it makes following the code and controlling your variables really hard in the end of the day. If you can give a little more specific description of what you are trying to do, perhaps I can help.

Rgds,
Jani
Display More

thanks jani. i think the problem i'm having here is that i'm not trying to GOTO a lower line, but a HIGHER line. not sure if that is typically supposed to work.... perhaps that is why i'm getting label does not exist errors and line errors. surely there is a way to regress my code processing from line 194 to line 39 after a boolean is set to true via a msgbox.

Fencliff · Jul 12th 2007

Re: Goto Command?

I think I figured out your problem. You have declared vCriteria6 as Boolean, although the answer is a actually numeric, vbYes and vbNo are constants that correspond to an integer (or long?). Try declaring the variable as Long instead of Boolean, should work.

Hope this helps,
Jani

khenzel · Jul 12th 2007

Re: Goto Command?

Quote from Fencliff

I think I figured out your problem. You have declared vCriteria6 as Boolean, although the answer is a actually numeric, vbYes and vbNo are constants that correspond to an integer (or long?). Try declaring the variable as Long instead of Boolean, should work.

Hope this helps,
Jani

aha that did it! thanks! funny i didnt think that vbYesNo wouldnt be a boolean. I assumed it would be.

much appreciated! you are a pro ^_~

Skip Code Lines & Go To Another

Participate now!

Share