Code Help

G

Grace

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
 
F

Frank Kabel

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
 
P

Patrick Molloy

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
 
G

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
 
G

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.
 
P

Patrick Molloy

you are correct

Else
CellEmpty - False
End If

should read
Else
CellEmpty = False
End If

Also, change the CellEmpty to

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.
 
G

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
 
G

Grace

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
 
D

Dave Peterson

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.)
 
G

Grace

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
 
D

Dave Peterson

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.
 
Top