Code Help
 

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

Code Help
 

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

Code Help
 

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

Code Help
 

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

Code Help
 

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

Code Help
 

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

Code Help
 

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

Code Help
 

I suspect Patrick has not been available to help me out on this one so, as
unfair as it might be to ask someone to review my last post in this thread,
because it involves some of his suggested coding, can I still ask PLEASE for
help from anyone?

I am really close to being done!

Thanks,
Grace

"Grace" wrote in message
...
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

Code Help
 

Patrick's functions are not supposed to be inside the

Sub yyyy()
end sub

lines.

They're kind of stand-alone functions. You can put them at the bottom of your
module.

Option Explicit
sub aaaa()
.....
end sub

sub bbbb()
....
end sub

function Patrick1(...)
......
end function

(not sure what function of Patrick's you're using, though.)






Grace wrote:

I suspect Patrick has not been available to help me out on this one so, as
unfair as it might be to ask someone to review my last post in this thread,
because it involves some of his suggested coding, can I still ask PLEASE for
help from anyone?

I am really close to being done!

Thanks,
Grace

"Grace" wrote in message
...
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













--

Dave Peterson

Code Help
 

Ok, I think I figured it out. It seems to work with a

Call GetValue(testCell)

But this is NOT really what I asked Patrick for. His function returns a
dialog box that allows me to enter a value for a cell which is blank when
you start the macro. Here it is:

Entry = InputBox("Enter a value for selected cell")

However, the cell I want to populate already had a dropdown 'handle' (I used
data, validation, list to do this) that allows the user to choose from tens
of choices, each spelled correctly, as they need to be. With Patrick's
procedure, I can't see that list and so, could easily choose type in a bad
choice or misspell it.

Instead of it merely giving me that message box, can someone tell me the
replacement code to insert in the function so as to have the function allow
me to actually go to the worksheet cell and use its dropdown to make a
choice and then, after I've chosen something, have the function (and macro)
continue on their merry ways?

Anyone?

Thx,
Grace

"Dave Peterson" wrote in message
...
Patrick's functions are not supposed to be inside the

Sub yyyy()
end sub

lines.

They're kind of stand-alone functions. You can put them at the bottom of
your
module.

Option Explicit
sub aaaa()
....
end sub

sub bbbb()
...
end sub

function Patrick1(...)
.....
end function

(not sure what function of Patrick's you're using, though.)






Grace wrote:

I suspect Patrick has not been available to help me out on this one so,
as
unfair as it might be to ask someone to review my last post in this
thread,
because it involves some of his suggested coding, can I still ask PLEASE
for
help from anyone?

I am really close to being done!

Thanks,
Grace

"Grace" wrote in message
...
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













--

Dave Peterson

Code Help
 

The bad news is that you can say "release control of the macro to the user until
that cell is filled, then come back and continue."

Well, if you use xl2k+, you could design a userform that is shown non-modally
(allows you to change things on the worksheet when the userform is displayed.)

Maybe you could just stop the macro and yell at them.

Then restart the macro when the user is ready.

Grace wrote:

Ok, I think I figured it out. It seems to work with a

Call GetValue(testCell)

But this is NOT really what I asked Patrick for. His function returns a
dialog box that allows me to enter a value for a cell which is blank when
you start the macro. Here it is:

Entry = InputBox("Enter a value for selected cell")

However, the cell I want to populate already had a dropdown 'handle' (I used
data, validation, list to do this) that allows the user to choose from tens
of choices, each spelled correctly, as they need to be. With Patrick's
procedure, I can't see that list and so, could easily choose type in a bad
choice or misspell it.

Instead of it merely giving me that message box, can someone tell me the
replacement code to insert in the function so as to have the function allow
me to actually go to the worksheet cell and use its dropdown to make a
choice and then, after I've chosen something, have the function (and macro)
continue on their merry ways?

Anyone?

Thx,
Grace

"Dave Peterson" wrote in message
...
Patrick's functions are not supposed to be inside the

Sub yyyy()
end sub

lines.

They're kind of stand-alone functions. You can put them at the bottom of
your
module.

Option Explicit
sub aaaa()
....
end sub

sub bbbb()
...
end sub

function Patrick1(...)
.....
end function

(not sure what function of Patrick's you're using, though.)






Grace wrote:

I suspect Patrick has not been available to help me out on this one so,
as
unfair as it might be to ask someone to review my last post in this
thread,
because it involves some of his suggested coding, can I still ask PLEASE
for
help from anyone?

I am really close to being done!

Thanks,
Grace

"Grace" wrote in message
...
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













--

Dave Peterson


--

Dave Peterson