Forcing the user to make entries in a specified worksheet

My workbook (which will be available to the user as a template) will be
designed to analyze new investments and will include a "control panel," i.e.
a worksheet where the user will enter, among others, the project name,
whether certain industry segments are involved, certain financial hurdle
rates, whether or not the project involves an acquisition of a going concern,
etc. etc. These data entries will either be used in calculations elsewhere
in the workbook or trigger certain actions (I will probably use checkboxes to
hide or unhide certain worksheets, depending on some of the selections made.)

My question is whether I can force the user to make at least one entry in
the “control panel” before allowing any other actions elsewhere in the
workbook. My thinking is that if I can force them to make at least one entry
in the “control panel,” I can display exhortations asking them to complete it
in full.

Any suggestions on how to accomplish this?

Give those cells that must have a value a nice name.

Select each cell (click on the first and ctrl-click on subsequent).

Then type the name you want (I used: ReqCells) in the namebox--directly to the
left of the formulabar.

Then you could have your code do something like:

option explicit
sub testme()
dim myRng as range
set myrng = worksheets("Control Panel").range("reqcells")

if application.counta(myrng) = 0 then
msgbox "Nothing filled out"
'do what you want
end if

'you could even check to see if all the cells have something in them:
if application.counta(myrng) <> myrng.cells.count then
msgbox "not all filled in"
'do what you want
end if
end sub



KG wrote:
>
> My workbook (which will be available to the user as a template) will be
> designed to analyze new investments and will include a "control panel," i.e.
> a worksheet where the user will enter, among others, the project name,
> whether certain industry segments are involved, certain financial hurdle
> rates, whether or not the project involves an acquisition of a going concern,
> etc. etc. These data entries will either be used in calculations elsewhere
> in the workbook or trigger certain actions (I will probably use checkboxes to
> hide or unhide certain worksheets, depending on some of the selections made.)
>
> My question is whether I can force the user to make at least one entry in
> the “control panel” before allowing any other actions elsewhere in the
> workbook. My thinking is that if I can force them to make at least one entry
> in the “control panel,” I can display exhortations asking them to complete it
> in full.
>
> Any suggestions on how to accomplish this?

--

Dave Peterson

Thanks! This should work...

"Dave Peterson" wrote:

> Give those cells that must have a value a nice name.
>
> Select each cell (click on the first and ctrl-click on subsequent).
>
> Then type the name you want (I used: ReqCells) in the namebox--directly to the
> left of the formulabar.
>
> Then you could have your code do something like:
>
> option explicit
> sub testme()
> dim myRng as range
> set myrng = worksheets("Control Panel").range("reqcells")
>
> if application.counta(myrng) = 0 then
> msgbox "Nothing filled out"
> 'do what you want
> end if
>
> 'you could even check to see if all the cells have something in them:
> if application.counta(myrng) <> myrng.cells.count then
> msgbox "not all filled in"
> 'do what you want
> end if
> end sub
>
>
>
> KG wrote:
> >
> > My workbook (which will be available to the user as a template) will be
> > designed to analyze new investments and will include a "control panel," i.e.
> > a worksheet where the user will enter, among others, the project name,
> > whether certain industry segments are involved, certain financial hurdle
> > rates, whether or not the project involves an acquisition of a going concern,
> > etc. etc. These data entries will either be used in calculations elsewhere
> > in the workbook or trigger certain actions (I will probably use checkboxes to
> > hide or unhide certain worksheets, depending on some of the selections made.)
> >
> > My question is whether I can force the user to make at least one entry in
> > the “control panel” before allowing any other actions elsewhere in the
> > workbook. My thinking is that if I can force them to make at least one entry
> > in the “control panel,” I can display exhortations asking them to complete it
> > in full.
> >
> > Any suggestions on how to accomplish this?
>
> --
>
> Dave Peterson
>