+ Reply to Thread
Results 1 to 3 of 3

Error 91- Response button No not working

  1. #1
    Registered User
    Join Date
    01-04-2016
    Location
    Calgary
    MS-Off Ver
    2010
    Posts
    1

    Question Error 91- Response button No not working

    Hi All,

    I am trying to complete this code for a form at work for vacation. It works if the employee presses yes to the required fields questions, but I am having trouble if the employee presses no, i get an error. Can someone please let me know what i am doing wrong? What i need is if they press yes, it picks the type of request and fills in the applicable dates associated with the request type. If they press no, it will return them to form to review it.

    Here is the code:

    Sub VacationForm()

    Dim Response As Integer
    Response = MsgBox(prompt:="Have all required fields been filled in?", Buttons:=vbYesNo)
    If Response = vbYes Then
    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" & vbNewLine & _
    "be no VBA code in the file you send. Save the" & vbNewLine & _
    "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
    Exit Sub
    End If
    If Response = vbNo Then
    End If
    End If

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    Dim FName As String
    FName = Range("D6").Text

    Dim LName As String
    LName = Range("K6").Text

    Dim Rtype As String
    Rtype = Range("K12").Text

    Dim edate As String
    edate = Range("D12").Text

    Dim Flexstart As String
    Flexstart = Range("e23").Text

    Dim Flexend As String
    Flexend = Range("k23").Text

    Dim Vacstart As String
    Vacstart = Range("e37").Text

    Dim Vacend As String
    Vacend = Range("k37").Text

    Dim Email As String
    Email = "[email protected]"

    TempFilePath = Environ$("temp") & "\"
    TempFileName = FName & " " & LName & " " & Rtype & " " & edate
    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")

    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    Send = Range("k12").Value
    Result = Flexstart & " to " & Flexend
    If Send = "Vacation Request" Then Result = Vacstart & " to " & Vacend
    ElseIf Send = "Flex Day Request" Then Result = Flexstart & "to" & Flexend
    ElseIf Send = "Flex_Vacation Request" Then Result = Flexstart & "to" & Vacend
    Else
    End If

    With OutMail
    .To = Email
    .Subject = FName & " " & LName & " " & Send & " " & Result
    .Body = "Hello," & vbNewLine & vbNewLine & "Please find the attached" & " " & Rtype & " form for" & " " & FName & " " & LName & " " & vbNewLine & "If you have questions or concerns, please do not hesitate to contact me." & vbNewLine & FName
    .Attachments.Add wb1.FullName
    .Send
    End With
    On Error GoTo 0

    wb2.Close SaveChanges:=False

    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    MsgBox " Thank you for submitting your form"
    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Error 91- Response button No not working

    Can you add code tags when posting code?
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Error 91- Response button No not working

    Welcome to the forum. Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate response time in working hours between two dates
    By ajagibson in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-28-2021, 08:45 AM
  2. VBA Direct Replies To Different People Based on Voting Button Response
    By mjeffery in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-30-2015, 12:39 PM
  3. [error while processing request] wrong response
    By CorinneGermany in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2015, 03:02 AM
  4. [SOLVED] Slow Response Time; error message
    By jaslake in forum Suggestions for Improvement
    Replies: 10
    Last Post: 11-17-2013, 07:07 PM
  5. VBA Response to Error Box?
    By blokeyhighlander in forum PowerPoint Formatting & General
    Replies: 3
    Last Post: 09-17-2013, 05:53 AM
  6. [SOLVED] Correct ELSE command for my error message (IF one response do one thing, or ELSE)
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-10-2012, 10:48 AM
  7. Calculate response time in working hours between two dates
    By ajagibson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-28-2012, 01:21 PM

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