XL 2003 - Create Dialog Box to Complete Cells...

[Guest]

I have an expense spreadsheet containing two sheets (car mileage & other
expenses). Ideally, when a user opens the template, I'd like them to see a
dialog box asking for Name (say, cell C4), Payroll Number (cell G4) and Date
(cell N4). When they complete the dialog box and click on OK, the cells are
populated and the corresponding cells on the second sheet are also completed
(which I know are just formulae referring to the cells on the first sheet).
Any help gratefully accepted.

TIA
Amanda

 

[Mike Fogleman]

How about a series of Input boxes whenever sheet1 is activated, but the
boxes only appear if the data is missing? Right-click on the sheet tab,
select view code and paste this there:

Private Sub Worksheet_Activate()
If Range("C4").Value = "" Then
Range("C4").Value = InputBox("Enter your Name")
End If
If Range("G4").Value = "" Then
Range("G4").Value = InputBox("Enter your Payroll Number")
End If
If Range("N4").Value = "" Then
Range("N4").Value = InputBox("Enter Date")
End If
End Sub

Mike F

 

[Guest]

Hi Mike & thanks for your very prompt response. This is practically perfect,
except that I can only currently get the input boxes to appear if I click on
Run in the VB window. My macro security is currently set to low.
I'm sure I'm doing something (probably very simple) wrong or missed
something out - any suggestions?
TIA
Amanda

 

[Mike]

You are right. Opening a workbook does not trigger the WorksheetActivate
event. It would only work as you said, or you went to another sheet and then
back to sheet1. So, lets move the code to the ThisWorkbook code module and
delete what you have now:

Private Sub Workbook_Open()
If Range("C4").Value = "" Then
Range("C4").Value = InputBox("Enter your Name")
End If
If Range("G4").Value = "" Then
Range("G4").Value = InputBox("Enter your Payroll Number")
End If
If Range("N4").Value = "" Then
Range("N4").Value = InputBox("Enter Date")
End If
End Sub


Mike F