Forcing the user to make entries in a specified worksheet

K

KG

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?
 
D

Dave Peterson

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
 
K

KG

Thanks! This should work...

Dave Peterson said:
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
Click to expand...
 
Top