Excel and launching userforms

  • Thread starter Thread starter Lumpjaw
  • Start date Start date
L

Lumpjaw

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
 
I want the trigger to be after after the data is entered and after
validation if possible.

thx.

-lumpjaw
 
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.
 
Thank You! Works great.

-lumpjaw


Tom Ogilvy said:
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.
Click to expand...
 
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
 
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
 
Back
Top