CODEWITHSUNDEEP
vbaexcel
Brianna Cates
Brianna Cates

Reputation: 347

Input Box on workbook open

I am trying to come up with some vba code to open an input box automatically as soon as the workbook is opened and have the user enter a date and then have the date placed in the A1 cell. I have written the code below but the input box is not pulling up at all it just opens the workbook and moves on.. not sure what is happening. Any and all help is appreciated.

Thanks! 

Option Explicit
Private Sub workbook_open()
Dim cellvalue As Variant
Dim ws As Worksheet

Set ws = Worksheets("Workbench Report")

ReShowInputBox: cellvalue = Application.InputBox("Please Enter Todays Date (dd/mm/yyyy)")

If cellvalue = False Then Exit Sub
If IsDate(cellvalue) And CDate(cellvalue) < Date Then
ws.Range("A1").Value = DateValue(cellvalue)
Else: MsgBox ("Invalid Date!")
GoTo ReShowInputBox
End If

End Sub

Upvotes: 1

Views: 6864

Answers (2)

Davy C
Davy C

Reputation: 649

Your code triggers upon the Workbook opening for me. Try these steps.

  1. Open up Excel and Save As, changing the extension to .XSLM
  2. Open up the VBA Editor (ALT + F11)
  3. In the left-hand window, locate your macro file (the one you just created and named - it's in brackets after "VBA Project"), drilldown to "This Workbook" and double-click it.
  4. Paste your code into the right-hand window
  5. Save the file and re-open.

See attached diagram.

By the way, "cellValue = false" should probably be cellValue = "" since InputBox is returning a string and not a boolean value.

enter image description here

Upvotes: 3

mrbungle
mrbungle

Reputation: 1931

For Workbook_Open events the script needs to reside in the private module (ThisWorkbook)

From Ozgrid:

the Workbook_Open event is a procedure of the Workbook Object and as such, the Workbook_Open procedure MUST reside in the private module of the Workbook Object (ThisWorkbook).

Upvotes: 1

Related Questions