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

In my active workbook 'Service Providers', I have a sheet named Notes. Using a button, I…

  1. User

    In my active workbook 'Service Providers', I have a sheet named Notes. Using a button, I would like to open an excel sheet window that is 500 high 800 wide. When I enter values into the cells in this window, it will automatically save the values to my sheet 'Notes' in my active workbook 'Service Providers'. Also when I open this window, the values in sheet 'Notes' will be displayed in the same cells of the open window.

  2. Assistant

    To achieve this, you can follow the below steps:

    1. Add a button to your desired location on the sheet 'Notes'.
    2. Right-click on the button and select 'Assign Macro'.
    3. Create a new macro and use the below code:
    Sub OpenNotesWindow()
    
        Dim NotesWB As Workbook
        Dim NotesWS As Worksheet
        Dim NotesWin As Window
        Dim NotesRng As Range
        
        'Set the active workbook to "Service Providers"
        Set NotesWB = Workbooks("Service Providers.xlsx")
        NotesWB.Activate
        
        'Set the active worksheet to "Notes"
        Set NotesWS = NotesWB.Sheets("Notes")
        NotesWS.Activate
        
        'Define the cell range you want to display in the window
        Set NotesRng = NotesWS.Range("A1:F20")
        
        'Create a new window with the desired size
        Set NotesWin = NotesWB.NewWindow
        NotesWin.Height = 500
        NotesWin.Width = 800
        
        'Activate the new window and display the defined cell range
        NotesWin.Activate
        NotesRng.Copy
        NotesWin.Activate
        NotesWin.Selection.PasteSpecial Paste:=xlPasteValues
        
        'Save any changes made to the window back to the Notes sheet
        Application.DisplayAlerts = False
        ActiveWindow.Close SaveChanges:=True
        Application.DisplayAlerts = True
        
    End Sub
    
    1. Save the macro and close the Visual Basic Editor.
    2. Click on the button to open the new window with the desired size and display the defined cell range from the 'Notes' sheet.
    3. Edit the values in the cells as desired and close the window.
    4. The changed values will be saved back to the 'Notes' sheet in your 'Service Providers' workbook.

    Note: You can adjust the cell range and the workbook/sheet names as per your requirement.

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