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

[Birmangirl]

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
"Birmangirl" <[email protected]> wrote in message
news:[email protected]...
>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

[Birmangirl]

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 Fogleman" wrote:

> 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
> "Birmangirl" <[email protected]> wrote in message
> news:[email protected]...
> >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]

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
Birmangirl <[email protected]> wrote in message
news:[email protected]...
> 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 Fogleman" wrote:
>
> > 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
> > "Birmangirl" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> >
> >
> >