adding balloons

[niyrho]

I am using validation to create drop down boxes that refer to a list on another worksheet. Does anyone know how to add a balloon to the drop down box that refers to another column in the corisponding row of the same worksheet?

 

[lenze]

Would a comment do and is VBA an option?

lenze

 

[lenze]

Assumptions
Your drop down is in Sheet1!A1
Your validation list is in Sheet2!A1:A8 and is a Named Range "myList"
Your text for the balloon is in Sheet2!B1:B8

This code has not been tested. Place it in the WorkSheet module for Sheet1 (right click the sheet tab and choose "View Code")

Private Sub Worksheet_Change(ByVal Target as Range)
dim balloon as String
dim myRow as Integer
If Target.Address <> "$A$1" Then Exit Sub
myRow = Application.WorkSheetFunction.Match(Target,myList,0)
balloon = Sheet2.Cells(myRow,2)
Target.ClearComments
Target.AddComments.Text Text:= balloon
End Sub

Adjust code as needed

lenze

 

[jim may]

Get R/T 1004 - at line below - Unable to get the Match property of the WorksheetFunction class. Confused...

myRow = Application.WorkSheetFunction.Match(Target,myList,0)

 

[mattrx731]

I get the same error...

 

[lenze]

As I said, the code was untested and I won't be on a machine with Excel until Monday.
Best guess

myRow = Application.WorkSheetFunction.Match(Target,Sheet2.myList,0)

or

myRow = Application.WorkSheetFunction.Match(Target,Sheet2.Range("$A$1:$A$8"),0)

 

[lenze]

OK: I had a senior moment when I posted the above code. (Something that happens more and more often at my age).
Either of these lines should work.

myRow = Application.WorkSheetFunction.Match(Target,Range("myList"),0)
myRow = Application.WorkSheetFunction.Match(Target,Sheet2.Range("$A$1:$A$8"),0)

lenze

 

[mattrx731]

I get a runtime error '438'
object doesn't support this property or method
at this line

Target.AddComments.Text Text:=balloon

entire code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim balloon As String
Dim myRow As Integer
If Target.Address <> "$A$1" Then Exit Sub
myRow = Application.WorksheetFunction.Match(Target, Sheet2.Range("$a$1:$a$8"), 0)
balloon = Sheet2.Cells(myRow, 2)
Target.ClearComments
Target.AddComments.Text Text:=balloon
End Sub

 

[lenze]

Thanks for the feedback, mattrx
Don't know why, and still can't test till Monday. I'm visiting my daughter and she doesn't have Excel. Try this. Add message boxes after myRow and balloon are defined

myRow = Application.WorksheetFunction.Match(Target, Sheet2.Range("$a$1:$a$8"), 0
MsgBox myRow
balloon = Sheet2.Cells(myRow, 2)
MsgBox balloon

See what shows up.

lenze

 

[mattrx731]

msg boxes worked with following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim balloon As String
Dim myRow As Integer
If Target.Address <> "$A$1" Then Exit Sub
myRow = Application.WorksheetFunction.Match(Target, Sheet2.Range("$A$1:$A$8"), 0)
MsgBox myRow
balloon = Sheet2.Cells(myRow, 2)
MsgBox balloon
Target.ClearComments
Target.AddComments.Text Text:=balloon
End Sub

but I still get error '438'
object doesn't support this property or method
at this line

Target.AddComments.Text Text:=balloon