Many thanks for that..bit of tweaking and it works perfectly... cheers.
sorry to ask, but i now have another question:..
the required info is returned to the respective cell via a vlookup
(example below) however this is only working when ListBox 4 is the
active listbox..it's not returning the relevant value when list box 5 is
active:
[Ranges are just a sample from about 50!]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 33 Or Target.Row <= 44 And Target.Column = 2 Then
Select Case Target.Value
Case "YB"
DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = "'YB
Codes'!YBCodes"
Rng = "'YB Codes'!YBCodes"
Case "TECHNOLOGY"
DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange =
"'NSE Codes'!NSECodes"
Rng = "'NSE Codes'!NSECodes"
Case "Please Select"
DialogSheets("ACCCode").ListBoxes("List Box 4").ListFillRange = ""
Rng = ""
End Select
End If
Sub DialogOK1()
ListIndex = DialogSheets("ACCCode").ListBoxes("List box
4").ListIndex
If Rng = "" Then
Exit Sub
Else
mytext =
Application.WorksheetFunction.VLookup((DialogSheet s("ACCCode").ListBoxes
("List box 4").List(ListIndex)), Range(Rng), 2, False)
ActiveCell.FormulaR1C1 = mytext
End If
ActiveCell.Offset(0, 1).Select
End Sub
what I want it to do is: "if list box 4 is selected, then vlookup
against this range and return relevant value" OR "if list box 5 is
selected, vlookup against that range and return relevant value"
I know it's probably simple, but I'm having difficulty working out how
to write this...and it's now 9.20pm and I'm dying to go
home....(starving!)
again, would really appreciate some guidance if you're able to help
here..
kind regards
Sue
Sydney, Australia / London, UK
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!