Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh label on user form with RefEdit
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh label on user form with RefEdit
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ John Tjia wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refresh label on user form with RefEdit
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 Jon Peltier wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate to form a label | Excel Discussion (Misc queries) | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
Hyperlink on a label in a VB Form | Excel Discussion (Misc queries) | |||
COPY LABEL FORM FROM EXCEL TO A LABEL | New Users to Excel | |||
I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming |