Excel 2000

Dear all

I guess my last post was too extensive and explained well enough.

I came across this code as a solutions to somebody else's problem.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
'
' Code to determine or select message text goes here
'
Message = "This is the message text"
Target.Validation.InputMessage = Message
End If
End Sub

How can I adjust this macro so that the Input message shows the result of a
vlookup formula based on the first three digits of the Active.Cell. The
worsheet formula I have used for the vlookup part is :

=VLOOKUP(INDIRECT(CELL("address")),testrange,2,FAL SE)

Regards

George

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Goto ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Target.Value = Application.VLOOKUP(Target.Value, _
Range("testrange"),2,FALSE)
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Excel 2000

Dear all

I guess my last post was too extensive and explained well enough.

I came across this code as a solutions to somebody else's problem.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
'
' Code to determine or select message text goes here
'
Message = "This is the message text"
Target.Validation.InputMessage = Message
End If
End Sub

How can I adjust this macro so that the Input message shows the result of
a
vlookup formula based on the first three digits of the Active.Cell. The
worsheet formula I have used for the vlookup part is :

=VLOOKUP(INDIRECT(CELL("address")),testrange,2,FAL SE)

Regards

George

Hi

I copied and pasted your code into my VBA project on the relevant sheet. and
nothing happens.

I will repeat what I am looking to do in case I have not been clear.

When I click on a cell (ActiveCell) I would like a box to appear with the
result of a formula (based on a vlookup formula which looks up the value in
the ActiveCell in a table "testrange", column 2). The type of box that I
would like to see is like the one that appears when you use
DataValidationInput Message

Can anybody help me with this code.

Regards

George





"Bob Phillips" wrote in message
...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Goto ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Target.Value = Application.VLOOKUP(Target.Value, _
Range("testrange"),2,FALSE)
End If

ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Excel 2000

Dear all

I guess my last post was too extensive and explained well enough.

I came across this code as a solutions to somebody else's problem.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
'
' Code to determine or select message text goes here
'
Message = "This is the message text"
Target.Validation.InputMessage = Message
End If
End Sub

How can I adjust this macro so that the Input message shows the result
of
a
vlookup formula based on the first three digits of the Active.Cell. The
worsheet formula I have used for the vlookup part is :

=VLOOKUP(INDIRECT(CELL("address")),testrange,2,FAL SE)

Regards

George