Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

This line of code opens NotePad - Shell "notepad.exe", vbNormalFocus - is there a way to…

  1. User

    This line of code opens NotePad - Shell "notepad.exe", vbNormalFocus - is there a way to set the window size of NotePad when it opens

  2. Assistant

    Yes, you can set the window size of Notepad when it opens using the Shell function by manipulating the command line arguments passed to Notepad. Here's an example code snippet:

    Shell "notepad.exe /A 10 10 800 600", vbNormalFocus
    

    In this code, /A is an argument for Notepad that sets the window position and size. The values 10 10 800 600 represent the left, top, width, and height of the window in pixels, respectively. Adjust these values as per your desired window size.

    Note: Keep in mind that Notepad itself doesn't allow resizing its window, so you can only set the initial size when it opens.

  3. User

    I am getting an error with this ammendment

  4. Assistant

    Apologies for the confusion. My previous response contained an error. The command line argument "/A" do not represent the window position and size in Notepad. Unfortunately, it is not possible to set the window size of Notepad using the Shell function alone.

    Notepad does not provide an option to control its window size programmatically. The window size of Notepad may be controlled using other programming languages or frameworks that allow more fine-grained control over the window, but not by using the Shell function alone.

    If you require more control over the window size, you may consider using other text editors or creating a custom application using a different programming language or framework that provides better control over the window size and appearance.

  5. User

    Th VBA event below works as expected. Is there a way of first opening a form that has a start date and an end date so that the search results are only between the dates. If it is possible , can you please show me how to do it.

    Sub HistoryToNotepad() Dim sourceWorkbook As Workbook Dim destinationWorkbook As Workbook Dim sourceWorksheet As Worksheet Dim destinationWorksheet As Worksheet Dim path As String, fileName As String Dim lastRow As Long Dim i As Long path = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS" fileName = "Service Providers History.xlsx" Set sourceWorkbook = ThisWorkbook Set destinationWorkbook = Workbooks.Open(path & fileName) destinationWorkbook.Windows(1).WindowState = xlMinimized Dim activeSheetName As String activeSheetName = sourceWorkbook.ActiveSheet.Name Set sourceWorksheet = sourceWorkbook.ActiveSheet Set destinationWorksheet = destinationWorkbook.Sheets(activeSheetName) lastRow = destinationWorksheet.Cells(destinationWorksheet.Rows.Count, "J").End(xlUp).Row Shell "notepad.exe", vbNormalFocus Application.Wait Now + TimeValue("00:00:01") For i = 5 To lastRow Dim text As String text = destinationWorksheet.Range("I" & i).Value & " " & destinationWorksheet.Range("L" & i).Value SendKeys text SendKeys vbCrLf Next i SendKeys "^{HOME}" destinationWorkbook.Close False Set destinationWorksheet = Nothing Set destinationWorkbook = Nothing Set sourceWorksheet = Nothing Set sourceWorkbook = Nothing End Sub

  6. Assistant

    To achieve opening a form with start and end dates before executing the search, you can use a UserForm in VBA. Here's an example of how you can modify your code to include the UserForm for date selection:

    1. Add a UserForm to your VBA project by right-clicking on your project name in the VBA editor, selecting Insert, and then choosing UserForm.
    2. In the UserForm designer, add two DatePickers or TextBoxes (one for the start date and one for the end date) and a Button for submitting the dates.
    3. Set the names and properties of these controls as per your preference, or use the default names provided.
    4. Double-click on the Submit Button to open the code editor for the Button's click event.
    5. In the code editor, enter the following code:
    Private Sub btnSubmit_Click()
        Dim startDate As Date
        Dim endDate As Date
        
        ' Validate and retrieve the selected dates
        If IsDate(Me.txtStartDate.Value) And IsDate(Me.txtEndDate.Value) Then
            startDate = CDate(Me.txtStartDate.Value)
            endDate = CDate(Me.txtEndDate.Value)
            
            ' Call your search function or procedure with the selected dates
            SearchWithDates startDate, endDate
            
            ' Close the UserForm
            Me.Hide
        Else
            MsgBox "Invalid date format. Please select valid start and end dates.", vbExclamation
        End If
    End Sub
    
    1. Below your existing Sub HistoryToNotepad() code, add the SearchWithDates subroutine to handle the search with specific dates. For example:
    Sub SearchWithDates(startDate As Date, endDate As Date)
        ' Additional code for searching within the specified date range
        ' Modify your existing code or add new code here to perform the search based on the selected dates
    End Sub
    
    1. Finally, modify your Sub HistoryToNotepad() code to open the form and wait for user input. Here's an example:
    Sub HistoryToNotepad()
        ' Your existing code before opening the form
        
        ' Open the UserForm for date selection
        Dim frmDateSelection As New frmSelectDates
        frmDateSelection.Show vbModal
        
        ' Continue with the rest of your code after the form is closed
        
        ' Your existing code after the form is closed
    End Sub
    

    With these changes, when Sub HistoryToNotepad() is called, it will display the UserForm where users can select the start and end dates. After submitting the dates, the form will be closed, and the selected dates can be used in the SearchWithDates subroutine for performing the search.

  7. User

    Can you please show me how to creat this form and how to run it

  8. Assistant

    Certainly! Here's a step-by-step guide to creating and running the UserForm for date selection in VBA:

    1. Open the Visual Basic Editor in Excel by pressing Alt + F11.
    2. In the Project Explorer window, right-click on your workbook project, select Insert, and choose UserForm.
    3. In the UserForm designer window, drag and drop two DatePicker controls from the toolbox onto the form. You can find the toolbox by clicking View > Toolbox (or press Ctrl + R).
    4. Place two labels near the DatePicker controls to label them as "Start Date" and "End Date".
    5. Add a CommandButton control from the toolbox to serve as the submit button.
    6. Customize the captions, names, and properties of the controls as desired. For example, you can change the captions of the labels to "Start Date" and "End Date" and set the Name properties for the DatePicker controls to dpStartDate and dpEndDate.
    7. Double-click on the submit button to open the code editor for the button's click event.
    8. In the code editor, enter the following code:
    Private Sub CommandButton1_Click()
        Dim startDate As Date
        Dim endDate As Date
        
        ' Validate and retrieve the selected dates
        If IsDate(Me.dpStartDate.Value) And IsDate(Me.dpEndDate.Value) Then
            startDate = CDate(Me.dpStartDate.Value)
            endDate = CDate(Me.dpEndDate.Value)
            
            ' Call your search function or procedure with the selected dates
            SearchWithDates startDate, endDate
            
            ' Close the UserForm
            Unload Me
        Else
            MsgBox "Invalid date format. Please select valid start and end dates.", vbExclamation
        End If
    End Sub
    
    1. Below the CommandButton1_Click() subroutine, add the SearchWithDates subroutine to handle the search with specific dates. You can modify this subroutine as per your search requirements. For example:
    Sub SearchWithDates(startDate As Date, endDate As Date)
        ' Additional code for searching within the specified date range
        ' Modify your existing code or add new code here to perform the search based on the selected dates
    End Sub
    
    1. Close the code editor and return to the UserForm designer window.
    2. To run the UserForm, you can add a button or assign the UserForm to a keyboard shortcut. Here's an example of adding a button to run the form:
    • In the worksheet or workbook where you want to add the button, go to the developer tab and click on the "Insert" button in the "Controls" group.
    • Select the "Button" control.
    • Drag on the worksheet where you want to place the button and release the mouse button.
    • In the "Assign Macro" dialog box, click "New" to create a new macro.
    • In the VBA editor, enter the macro code to open the UserForm. For example, UserForm1.Show.
    • Close the VBA editor.
    1. Save your workbook and click the button you added to run the UserForm.

    The UserForm will be displayed, allowing users to select the start and end dates. After clicking the submit button, the selected dates will be validated, and if they're valid, the SearchWithDates subroutine will be called, and the form will be closed.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.