There is one cell input I have that is disastrous if it is skipped. So, I'd
like my macro to go to that cell, then instruct the operator to choose from
a dropdown list already incorporated into that cell (via data, validation,
list) and then, after he has done that, click continue or Ok, so the macro
moves on. Can someone give me the code for this?

Thanks,
Grace

Hi
try something like the following:
sub foo()
with activesheet.range("A1")
if .value="" then
msgbox "Cell A1 has to be filled - Macro stopped"
exit sub
end if
end with
'now your code
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Grace wrote:
There is one cell input I have that is disastrous if it is skipped.
So, I'd like my macro to go to that cell, then instruct the operator
to choose from a dropdown list already incorporated into that cell
(via data, validation, list) and then, after he has done that, click
continue or Ok, so the macro moves on. Can someone give me the code
for this?

Thanks,
Grace

Dim TestCell as range
Set TestCell = sheet1.Range("G2") ' example

(...) ' your code

If CellEmpty(TestCell) Then Exit Sub

(...) ' your code



---------
Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = True
Target.Select
msgbox "Please Enter a value for the sekectted cell",,"Missing
Value"
Else
CellEmpty - False
End If
End Function



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
There is one cell input I have that is disastrous if it is skipped. So,
I'd
like my macro to go to that cell, then instruct the operator to choose
from
a dropdown list already incorporated into that cell (via data, validation,
list) and then, after he has done that, click continue or Ok, so the macro
moves on. Can someone give me the code for this?

Thanks,
Grace

This looks great, Patrick. But when it compiles, it says "compile error:
Exit function Not allowed in sub or property". What am I doing wrong?

A couple of other clarifications:

When you wrote, CellEmpty - False, should it be " = False"

I was not as clear as I should have been. I'd actually like, as part of the
macro, for the user to choose a value for this cell, as the normal routine,
and then the subroutine would continue on its merry way. Can you add a
little more code that would do that?

Grace

"Patrick Molloy" wrote in message
...
Dim TestCell as range
Set TestCell = sheet1.Range("G2") ' example

(...) ' your code

If CellEmpty(TestCell) Then Exit Sub

(...) ' your code



---------
Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = True
Target.Select
msgbox "Please Enter a value for the sekectted cell",,"Missing
Value"
Else
CellEmpty - False
End If
End Function



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
There is one cell input I have that is disastrous if it is skipped. So,
I'd
like my macro to go to that cell, then instruct the operator to choose
from
a dropdown list already incorporated into that cell (via data,
validation,
list) and then, after he has done that, click continue or Ok, so the
macro
moves on. Can someone give me the code for this?

Thanks,
Grace

Regarding only my very last clarification, I know realize that perhaps, it
already does allow you to enter something in that cell, while the macro is
running. I'm not sure! Also, I'm just not sure, once you do enter it, how
the macro knows to continue.

"Grace" wrote in message
...
This looks great, Patrick. But when it compiles, it says "compile error:
Exit function Not allowed in sub or property". What am I doing wrong?

A couple of other clarifications:

When you wrote, CellEmpty - False, should it be " = False"

I was not as clear as I should have been. I'd actually like, as part of
the
macro, for the user to choose a value for this cell, as the normal
routine,
and then the subroutine would continue on its merry way. Can you add a
little more code that would do that?

Grace

"Patrick Molloy" wrote in message
...
Dim TestCell as range
Set TestCell = sheet1.Range("G2") ' example

(...) ' your code

If CellEmpty(TestCell) Then Exit Sub

(...) ' your code



---------
Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = True
Target.Select
msgbox "Please Enter a value for the sekectted cell",,"Missing
Value"
Else
CellEmpty - False
End If
End Function



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
There is one cell input I have that is disastrous if it is skipped.
So,
I'd
like my macro to go to that cell, then instruct the operator to choose
from
a dropdown list already incorporated into that cell (via data,
validation,
list) and then, after he has done that, click continue or Ok, so the
macro
moves on. Can someone give me the code for this?

Thanks,
Grace

you are correct

Else
CellEmpty - False
End If

should read
Else
CellEmpty = False
End If

Also, change the CellEmpty to

Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = NOT GetValue(Target)
Else
CellEmpty = False
End If
End Function

Function GetValue(Target as Range) as Boolean
Dim Entry as String
Entry = InputBox("Enter a value for selected cell")
If Entry ="" then
GetValue = False
Else
Target.Value = Entry
GetValue = True
End If
End Function


By now you'll see that I'm a great beleiver in procedural code....for a
start it makes it easier to understand and then it's a relatively easy
matter to make adjustments.


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
Regarding only my very last clarification, I know realize that perhaps, it
already does allow you to enter something in that cell, while the macro is
running. I'm not sure! Also, I'm just not sure, once you do enter it,
how
the macro knows to continue.

"Grace" wrote in message
...
This looks great, Patrick. But when it compiles, it says "compile error:
Exit function Not allowed in sub or property". What am I doing wrong?

A couple of other clarifications:

When you wrote, CellEmpty - False, should it be " = False"

I was not as clear as I should have been. I'd actually like, as part of
the
macro, for the user to choose a value for this cell, as the normal
routine,
and then the subroutine would continue on its merry way. Can you add a
little more code that would do that?

Grace

"Patrick Molloy" wrote in message
...
Dim TestCell as range
Set TestCell = sheet1.Range("G2") ' example

(...) ' your code

If CellEmpty(TestCell) Then Exit Sub

(...) ' your code



---------
Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = True
Target.Select
msgbox "Please Enter a value for the sekectted
cell",,"Missing
Value"
Else
CellEmpty - False
End If
End Function



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
There is one cell input I have that is disastrous if it is skipped.
So,
I'd
like my macro to go to that cell, then instruct the operator to
choose
from
a dropdown list already incorporated into that cell (via data,
validation,
list) and then, after he has done that, click continue or Ok, so the
macro
moves on. Can someone give me the code for this?

Thanks,
Grace

Ok, I got the functions copied into my macro, but I don't think you have
told me exactly how I call them from my subroutine.

Also, along those lines, I mentioned that the macro was rejecting the "end
function" statement you gave me earlier and I don't think you have addressed
that.

Thanks for a bit more help, Patrick!

Grace

"Patrick Molloy" wrote in message
...
you are correct

Else
CellEmpty - False
End If

should read
Else
CellEmpty = False
End If

Also, change the CellEmpty to

Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = NOT GetValue(Target)
Else
CellEmpty = False
End If
End Function

Function GetValue(Target as Range) as Boolean
Dim Entry as String
Entry = InputBox("Enter a value for selected cell")
If Entry ="" then
GetValue = False
Else
Target.Value = Entry
GetValue = True
End If
End Function


By now you'll see that I'm a great beleiver in procedural code....for a
start it makes it easier to understand and then it's a relatively easy
matter to make adjustments.


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
Regarding only my very last clarification, I know realize that perhaps,
it
already does allow you to enter something in that cell, while the macro
is
running. I'm not sure! Also, I'm just not sure, once you do enter it,
how
the macro knows to continue.

"Grace" wrote in message
...
This looks great, Patrick. But when it compiles, it says "compile
error:
Exit function Not allowed in sub or property". What am I doing wrong?

A couple of other clarifications:

When you wrote, CellEmpty - False, should it be " = False"

I was not as clear as I should have been. I'd actually like, as part
of
the
macro, for the user to choose a value for this cell, as the normal
routine,
and then the subroutine would continue on its merry way. Can you add a
little more code that would do that?

Grace

"Patrick Molloy" wrote in message
...
Dim TestCell as range
Set TestCell = sheet1.Range("G2") ' example

(...) ' your code

If CellEmpty(TestCell) Then Exit Sub

(...) ' your code



---------
Function CellEmpty(Target as Range) as boolean
If Target.Value = "" then
CellEmpty = True
Target.Select
msgbox "Please Enter a value for the sekectted
cell",,"Missing
Value"
Else
CellEmpty - False
End If
End Function



--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Grace" wrote in message
...
There is one cell input I have that is disastrous if it is skipped.
So,
I'd
like my macro to go to that cell, then instruct the operator to
choose
from
a dropdown list already incorporated into that cell (via data,
validation,
list) and then, after he has done that, click continue or Ok, so
the
macro
moves on. Can someone give me the code for this?

Thanks,
Grace