Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

In my open workbook 'Service Providers', I would like to click on J3 and have a Form…

  1. User

    In my open workbook 'Service Providers', I would like to click on J3 and have a Form open. The Form will allow me to enter a 'Start Date' and an 'End Date'. The form will also have a Submit button which when I click on will, open a closed workbook 'Service Providers History' located at; "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx" When the workbook 'Service Provider History' opens, the sheet that has the same name as the active sheet in my workbook 'Service Provider' will be activated. Using the 'Start Date' and the 'End Date' entered in the Form, all cells in I:I of the currently activated sheet in the workbook 'Service Providers History' that have dates equal to and between the Start and End Dates entered in the Form will be copied to a new Note Pad document along with the values of J:J of the same row. Each row pasted will be on a seperate line in the Note Pad document. Once all the values have been pasted the workbook 'Service Provider History' will be closed without saving and without any prompts. Can you write all the necessary VBA codes for me and explain how I will also create the Form.

  2. Assistant

    Sure! Here's the VBA code that you can use to achieve the described functionality:

    1. Open the VBA editor in Excel by pressing Alt + F11.
    2. Insert a new UserForm by clicking on Insert > UserForm.
    3. In the UserForm design view, add two TextBox controls for Start Date and End Date, and one Submit button.
    4. Assign the following names to the controls: TextBox_StartDate, TextBox_EndDate, and Button_Submit.
    5. Double-click on the Submit button to open the code window for the button's click event.

    Here's the code for the UserForm:

    Private Sub Button_Submit_Click()
        Dim startDate As Date
        Dim endDate As Date
        Dim filePath As String
        Dim notePadFilePath As String
        
        ' Validation checks
        If Not IsDate(Me.TextBox_StartDate.Value) Then
            MsgBox "Please enter a valid Start Date.", vbExclamation
            Exit Sub
        End If
        
        If Not IsDate(Me.TextBox_EndDate.Value) Then
            MsgBox "Please enter a valid End Date.", vbExclamation
            Exit Sub
        End If
        
        startDate = CDate(Me.TextBox_StartDate.Value)
        endDate = CDate(Me.TextBox_EndDate.Value)
        
        ' Open the Service Provider History workbook
        filePath = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx"
        Workbooks.Open filePath
        
        ' Activate the corresponding sheet
        Dim currentSheetName As String
        currentSheetName = ThisWorkbook.ActiveSheet.Name
        Sheets(currentSheetName).Activate
        
        ' Copy matching values to Notepad
        Dim i As Long
        Dim notepadContent As String
        
        For i = 1 To Cells(Rows.Count, "I").End(xlUp).Row
            If Cells(i, "I").Value >= startDate And Cells(i, "I").Value <= endDate Then
                notepadContent = notepadContent & Cells(i, "I").Value & " - " & Cells(i, "J").Value & vbCrLf
            End If
        Next i
        
        ' Save notepad content to a file
        notePadFilePath = Environ("USERPROFILE") & "\Desktop\ServiceProviders.txt"
        Open notePadFilePath For Output As #1
        Print #1, notepadContent
        Close #1
        
        ' Close Service Provider History workbook without saving
        Workbooks("Service Providers History.xlsx").Close SaveChanges:=False
        MsgBox "Data has been copied to Notepad successfully.", vbInformation
    End Sub
    

    Now, you can save the changes to the VBA project, close the VBA editor, and go back to the worksheet.

    To initiate the form upon clicking on cell J3, add the following code to the worksheet's code module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Address = "$J$3" Then
            UserForm.Show
        End If
    End Sub
    

    This code will open the UserForm whenever cell J3 is selected. Make sure to change the address to match the cell you want to trigger the form.

    That's it! You have now created a UserForm to enter the Start Date and End Date, and upon clicking Submit, the values will be copied to a Notepad file. The Service Providers History workbook will also be opened, the corresponding sheet will be activated, and the relevant data will be copied. Finally, the Service Providers History workbook will be closed without saving.

  3. User

    After the results are printed to Note Pad, how can I close the form from the VBA

  4. Assistant

    To close the UserForm after the results are printed to Notepad, you can add the following line of code at the end of the Button_Submit_Click() procedure:

    Unload Me
    

    This line unloads (closes) the UserForm. Here's the updated code:

    Private Sub Button_Submit_Click()
        ' Code to retrieve Start Date, End Date, and other logic
        
        ' Print results to Notepad
        ' ...
        
        ' Close the UserForm
        Unload Me
    End Sub
    

    Now, when you click the Submit button, the results will be printed to Notepad, and the UserForm will close automatically.