Refresh label on user form with RefEdit

[John Tjia]

I am using a user form with a RefEdit input box so that through the
form, the user can pick a cell in the worksheet; the chosen cell's
address then shows up in the RefEdit box.

The selected cell is typically in column C (it's a driver for the
formulas in that line). Column B of the same line is the title (e.g.:
Incremental Sales Growth).

What I'd like to do is that after selecting the cell in column C (and
still in the userform), the title from the B column of the selected
row should appear as a label above the RefEdit input box. In other
words, I'd like a way to refresh the form so that the user knows the
title of the line he has just selected. By the way, the address
selected is usually in a different sheet, so the RefEdit.Text has a
sheet name: Sheet1!$C$23, for example. I can't figure out what the
code is to refresh the label on the form based on the change of the
RefEdit box.

(I've figured out how to show the title the next time I active the
user form. When I press OK on the form, that writes the selected
address to another sheet, which I parse it, and somehow can get the
contents of the B column. When I activate the form again, I use that
to label the RefEdit box.)

Thanks for any advice!

 

[Jon Peltier]

John -

Put this in the code behind the user form that has your label (Label1)
and RefEdit control (RefEdit1). The On Error prevents it from hanging if
someone clicks in column A.

Private Sub RefEdit1_Change()
On Error Resume Next
Label1.Caption = Range(RefEdit1.Text).Offset(0, -1).Value
On Error GoTo 0
End Sub

Even better, if the label is always taken from column B, use this one.

Private Sub RefEdit1_Change()
Label1.Caption = Range(RefEdit1.Text). _
Offset(0, 2 - Range(RefEdit1.Text).Column).Value
End Sub

- Jon

 

[John Tjia]

This code is a beaut! Thanks very much. I had tried the _Change(),
but couldn't make the leap into getting the selected cell address, let
alone getting the contents of the cell next to it.

John