adding balloons

niyrho

Board Regular
Joined
Jun 19, 2008
Messages
108
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?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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")
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
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

Board Regular
Joined
Feb 10, 2008
Messages
181

ADVERTISEMENT

I get the same error...
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
As I said, the code was untested and I won't be on a machine with Excel until Monday.
Best guess
Code:
myRow = Application.WorkSheetFunction.Match(Target,Sheet2.myList,0)
or
Code:
myRow = Application.WorkSheetFunction.Match(Target,Sheet2.Range("$A$1:$A$8"),0)
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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.
Code:
myRow = Application.WorkSheetFunction.Match(Target,Range("myList"),0)
myRow = Application.WorkSheetFunction.Match(Target,Sheet2.Range("$A$1:$A$8"),0)

lenze
 

mattrx731

Board Regular
Joined
Feb 10, 2008
Messages
181
I get a runtime error '438'
object doesn't support this property or method
at this line
Code:
Target.AddComments.Text Text:=balloon
entire code
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
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

Board Regular
Joined
Feb 10, 2008
Messages
181
msg boxes worked with following:
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)
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
Code:
Target.AddComments.Text Text:=balloon
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top