Excel VBA Input Box to Enter a Value When a Workbook Opens
Question details
The user needs a VBA input box to collect a value and place it in cell I1 whenever the workbook opens.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A workbook should prompt users for text when it opens and save the response to a specific worksheet cell.
- Observed behavior
- The workbook does not yet display an input prompt or write the entered value to cell I1 when opening.
Confirm the destination worksheet name and ensure the workbook allows macros to run. Save the file in a macro-enabled format such as .xlsm before testing the Workbook_Open event.
Add a Workbook_Open VBA input box that writes to cell I1
Use this solution when the prompt must appear automatically each time the workbook opens.
The Workbook_Open event runs when the workbook opens. Application.InputBox with Type:=2 requests text, and the response is written to the specified worksheet cell unless the user cancels the prompt.
In Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, expand the workbook project, double-click ThisWorkbook, and open its code window.
Paste this code into the ThisWorkbook module: Private Sub Workbook_Open()\n Dim response As Variant\n response = Application.InputBox("Enter a value:", "Workbook Input", Type:=2)\n If response <> False Then ThisWorkbook.Worksheets("Sheet1").Range("I1").Value = response\nEnd Sub
Replace Sheet1 in ThisWorkbook.Worksheets("Sheet1") with the exact name of the worksheet that should receive the value.
Close the VBA editor, save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), close and reopen it, then select Enable Content if Excel displays a macro security warning.
Create a Workbook-Open Input Prompt in WPS Spreadsheets
WPS Spreadsheets can use a VBA Workbook_Open event to prompt for a value and place it in cell I1. Open the macro editor, add the event code to ThisWorkbook, save the file in a macro-enabled format, and enable macros when prompted.
- 1. Open the macro editor: In WPS Spreadsheets, open the workbook and use the Developer tools or press Alt+F11 to open the VBA editor, if VBA support is available in your installation.
- 2. Edit ThisWorkbook: In the project tree, double-click ThisWorkbook and insert the Workbook_Open procedure that calls Application.InputBox.
- 3. Set the destination cell: Change the worksheet name in ThisWorkbook.Worksheets("Sheet1") to the correct sheet name and keep Range("I1") as the destination, or replace it with another cell if needed.
- 4. Save and enable macros: Save the workbook as a macro-enabled .xlsm file, close and reopen it, and allow macros when WPS displays the security prompt.
Frequently Asked Questions
Where should the Workbook_Open VBA code be placed?
Place it in the ThisWorkbook module, not in a standard module or the worksheet module. In Excel, press Alt+F11, expand the workbook project, and double-click ThisWorkbook.
How do I change the worksheet and cell that receive the input?
Replace Sheet1 with the exact worksheet name in ThisWorkbook.Worksheets("Sheet1"), and change Range("I1") to the required destination cell, such as Range("B2").
Why does the input box not appear when I open the workbook?
Check that the file is saved as .xlsm, macros are enabled, and the code is in ThisWorkbook. If Excel shows a security warning, select Enable Content and reopen the file.
What happens if the user clicks Cancel in the input box?
Application.InputBox returns False when the user cancels. The If response <> False condition prevents the macro from changing cell I1.
Can the input box accept numbers instead of text?
Yes. Change Type:=2 to Type:=1 for a numeric input. Keep Type:=2 when the response may contain text, letters, or mixed characters.
