In my open workbook 'Service Providers', I would like to click on J3 and have a Form…
- 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.
- Assistant
Sure! Here's the VBA code that you can use to achieve the described functionality:
- Open the VBA editor in Excel by pressing Alt + F11.
- Insert a new UserForm by clicking on Insert > UserForm.
- In the UserForm design view, add two TextBox controls for Start Date and End Date, and one Submit button.
- Assign the following names to the controls: TextBox_StartDate, TextBox_EndDate, and Button_Submit.
- 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 SubNow, 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 SubThis 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.
- User
After the results are printed to Note Pad, how can I close the form from the VBA
- 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 MeThis 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 SubNow, when you click the Submit button, the results will be printed to Notepad, and the UserForm will close automatically.