Pop Up Box

[vandango05]

Hi all.

I've been doing a bit of searching but not yet found it if this is possible.

When a workbook is opened for the first time I wonder if it possible for a message to appear like this?

"Please enter your delivery rate"

I would then need our customer to write a value that would then be stored in a particular cell.

Is anything like this possible?

Thank you for reading

 

[Richard Schollar]

Hi

You could place the following in the ThisWorkbook module of the workbook concerned - it will be activated (assuming the user enables macros) when the workbook is opened:

Private Sub Workbook_Open()
dim retval
retval = Application.InputBox("Please enter a delivery rate", "Rate", Type:=1)
If retval <> False Then
   Sheets("SomeSheet").Range("A1").Value = retval
End If
End Sub

Change the sheet name and the specific cell.

 

[vandango05]

Thank you for your quick reply. It runs through and works great when I test it in the VB window, although it does not run when the file is opened.

Anything I'm doing wrong?

 

[Richard Schollar]

Have you placed the code in the ThisWorkbook module of the workbook and saved it down?

 

[vandango05]

I believe so.

I've tried 2 ways.

Opening the file and pressing ALT+F11

Inserting a New Module and pasting the code

Private Sub Workbook_Open()
Dim retval
retval = Application.InputBox("Please enter a delivery rate", "Rate", Type:=1)
If retval <> False Then
Sheets("Quotation").Range("S1").Value = retval
End If
End Sub

I've also tried right clicking the sheet and selecting view code and pasting the code on the sheet.

I may be doing it totally wrong i'm not very good when it comes to the VB side of Excel i'm afraid!

 

[Richard Schollar]

When you are in the VBE, the ThisWorkbook module will be listed under the workbook name in the top right Project window (under Microsoft Excel Objects sub category). You definitely do not want to place it in a standard module nor in a worksheet module.

Alternatively, place the following in a standard module:

Sub Auto_Open()
Dim retval
retval = Application.InputBox("Please enter a delivery rate", "Rate", Type:=1)
If retval <> False Then
Sheets("Quotation").Range("S1").Value = retval
End If
End Sub

The above should work the same.