Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, ans As Long
Set rng = Intersect(Target, Range("A1:A10"))
On Error GoTo ErrHandler
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
If Not IsEmpty(cell) Then
If IsNumeric(cell) Then
If cell.Value 10 Then
ans = vbNo
ans = MsgBox("Are you male?", vbYesNo)
If ans = vbYes Then
cell.Offset(0, 1).Value = "Male's age: " & cell.Value
End If
End If
End If
End If
Next cell
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"Lumpjaw" wrote in message
...
Hey Greg,

I was just wondering, how would you apply this code to a range of cells?
Say... A1-A10 and have the result in B1-B10. I tried playing around with
it a bit, but I am just not that good with Excel yet. Thanks.

-Lumpjaw


"Tom Ogilvy" wrote in message
...
Right click on the sheet tab and select view code. Put in code like this
in the resulting module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" then
if not isempty(target) then
if isnumeric(target) then
if target.Value 10 then
ans = msgbox( "Are you male?", vbYesNo)
if ans = vbYes then
Target.offset(0,1).Value = "Male's age: " & Target.value
end if
end if
end if
end if
end if
End Sub

I used a msgbox. A userform seems overkill here. Substitute a userform
if you wish.

--
Regards,
Tom Ogilvy


"Lumpjaw" wrote in message
...
I want the trigger to be after after the data is entered and after
validation if possible.

thx.

-lumpjaw


"Tom Ogilvy" wrote in message
...
what is the trigger event.

Do you want to launch the userform each time a cell is selected, each
time there is a calculate, only when A1 is manually edited? When.

See Chip Pearson's page on events
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"Lumpjaw" wrote in message
...
Greetings,

I want to do something that I hope is not too unusual. i want to test
a value in a cell, and if it one thing or another... launch a user
form.
Here is the scenario...

test cell a1, if it is greater than 10, then launch userform1,
userform1 will ask a question, if yes is selected it will write some
text "and" the contents of cell a1 to cell b1. How do you do this?

Thanks.

-lumpjaw