+ Reply to Thread
Results 1 to 8 of 8

GoTo or Run Code???

Hybrid View

jkauffman GoTo or Run Code??? 04-09-2008, 04:47 PM
shg Please edit your post to add... 04-09-2008, 04:49 PM
jkauffman Hello, Was checking back... 04-21-2008, 03:10 PM
mudraker To call another macro from... 04-21-2008, 05:00 PM
jkauffman Thanks I see how that works... 04-23-2008, 09:43 AM
  1. #1
    Registered User
    Join Date
    04-09-2008
    Posts
    5

    GoTo or Run Code???

    What I am needing is a way to jump from one section of the module to another section. It runs a validation step first then once completed I need it to run the email portion of the code. I have colored "RED" the two sections I need it to go from and to. Any help on this would be appreciated I have searched all over the forum and the net trying to find a way to make this work. This module runs when one button is pushed. Unless theres a way to assign two modules to a button in excel

    Thanks
    Jennifer

    Sub test()
    Dim requiredCells As Variant
    Dim prompts As Variant
    Dim i As Long
    
     requiredCells = Array("a3", "d3", "h3", "k3")
     prompts = Array("Date", "Accounty Manager", "Warehouse From", "Warehouse To")
    
     For i = LBound(requiredCells) To UBound(requiredCells)
         If Range(requiredCells(i)).Value = vbNullString Then
             Range(requiredCells(i)).Value = userInput(prompts(i))
             If Range(requiredCells(i)).Value = vbNullString Then Exit Sub: Rem Cancel pressed
         End If
     Next i
    
    End Sub
    Function userInput(promptString As Variant) As String
    Do
        userInput = Application.InputBox("You must enter a " & promptString, Type:=2)
        If userInput = "False" Then userInput = vbNullString: Exit Function
    Loop Until userInput <> vbNullString
    End Function
    
    Sub Mail_workbook_Outlook_2()
    'Working in 2000-2007
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim OutApp As Object
        Dim OutMail As Object
     
        Set wb1 = ActiveWorkbook
     
        If Val(Application.Version) >= 12 Then
            If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
                MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                       "Save the file first as xlsm and then try the macro again.", vbInformation
                Exit Sub
            End If
        End If
     
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        'Make a copy of the file/Open it/Mail it/Delete it
        'If you want to change the file name then change only TempFileName
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
        FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
     
        wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
        Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
     
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
     
        On Error Resume Next
        With OutMail
            .To = "fizgigjk@yahoo.com"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add wb2.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
        wb2.Close SaveChanges:=False
     
        'Delete the file
        Kill TempFilePath & TempFileName & FileExtStr
     
        Set OutMail = Nothing
        Set OutApp = Nothing
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    End Sub
    Last edited by jkauffman; 04-09-2008 at 05:27 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Please edit your post to add code tags:

    [code]
    code goes here
    [/code]

  3. #3
    Registered User
    Join Date
    04-09-2008
    Posts
    5
    Hello,

    Was checking back to see if anyone had any ideas on this code for me.

    Thanks
    Jennifer

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    To call another macro from the 1st macro is easy

    Sub Macro1()
       Call Macro2
       if range("a1").value = 2 then
            call macro3
       end if
    End Sub
    Macro1 starts to run, calls macro2
    after MAcro2 runs macro1 continues running from the next command after it called macro2

    Note you do not need to use the word Call in front of the macro name
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  5. #5
    Registered User
    Join Date
    04-09-2008
    Posts
    5
    Thanks I see how that works but here is the code where I want to call the other Macro. I can call both my macros from another macro by themselves no problem. But for some reason it wont call the macro I have highlighted when I put the code in like this. Any thoughts???

    Sub Test()
    Dim requiredCells As Variant
    Dim prompts As Variant
    Dim i As Long
    
     requiredCells = Array("a3", "d3", "h3", "k3")
     prompts = Array("Date", "Account Manager", "Warehouse From", "Warehouse To")
    
     For i = LBound(requiredCells) To UBound(requiredCells)
         If Range(requiredCells(i)).Value = vbNullString Then
             Range(requiredCells(i)).Value = userInput(prompts(i))
             If Range(requiredCells(i)).Value = vbNullString Then Call Mail_workbook_Outlook_2
        End If
     Next i
    
    End Sub
    Function userInput(promptString As Variant) As String
    Do
        userInput = Application.InputBox("You must enter a " & promptString, Type:=2)
        If userInput = "False" Then userInput = vbNullString: Exit Function
    Loop Until userInput <> vbNullString
    End Function

  6. #6
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177
    My first guess would be a requirement is not being met? In this section of your code you use the same "If" statement twice.
     For i = LBound(requiredCells) To UBound(requiredCells)
         If Range(requiredCells(i)).Value = vbNullString Then
             Range(requiredCells(i)).Value = userInput(prompts(i))
             If Range(requiredCells(i)).Value = vbNullString Then Call Mail_workbook_Outlook_2
        End If
     Next i
    I would think that the string "If Range(requiredCells(i)).Value" is not evaluating to "vbNullString".

    A second look I see that you reset your "If" satement to "If Range(requiredCells(i)).Value = userInput(prompts(i))", then the second "If" statement would not evaluate to "vbNullString" and the call statement would be ignored.
    Last edited by Rick_Stanich; 04-23-2008 at 10:10 AM.
    Regards

    Rick
    Win10, Office 365

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1