ActiveCell changes contents of TextBox

[nickm687]

I have on a worksheet a textbox, and i want its contents to change
depending on the highlighted cell.

For example:

when A1 is selected the textbox contents are "msg1"

when A6 is selected the textbox reads "msg2"

Thanks in advance.
Nick

 

[Alan]

This should do what you want but the sub will run EVERY TIME you select
a new cell.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

dim mymsg as string

if ActiveCell.Address="A1" then
mymsg="String 1"
elseif activecell.address="A6" then
mymsg="String 2"
else
exit sub
end if

msgbox mymsg

End Sub

 

[nickm687]

Thank you very much. Works a treat!

This should do what you want but the sub will run EVERY TIME you select
a new cell.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

dim mymsg as string

if ActiveCell.Address="A1" then
mymsg="String 1"
elseif activecell.address="A6" then
mymsg="String 2"
else
exit sub
end if

msgbox mymsg

End Sub

 

[Alan]

Sorry, read the exam question and check your answer!!

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

dim mymsg as string

if ActiveCell.Address="$A$1" then
mymsg="String 1"
elseif activecell.address="$A$6" then
mymsg="String 2"
else
exit sub
end if

ActiveSheet.Shapes("Text Box").Select
Selection.Characters.Text = mymsg

End Sub

 

[Guest]

This would be a good place to use Select Case:

Select Case ActiveCell.Address
Case "$A$1"
mymsg="String 1"
Case "$A$6"
mymsg="String 2"
case else
exit sub
end select

Besides saving time, not having to write the same thing (activecell.address)
to be tested multiple times, it runs faster. The Case tests can include
combinations of multiple values, ranges of values, and inequalities (eg.,
Case 3, 6, 10 to 20 ; Case Is > 8)