Active Cell Link

  • Thread starter Thread starter daphoenix
  • Start date Start date
D

daphoenix

I would like to know if it is possible to have a cell reference the active
cell of the sheet. So if i were to click on a cell, another cell would
reference that active cell....?
 
and this active cell would have be in some given range in the workshhet for
it to work.
 
Well, it is sort of possible... if by "active cell" you mean the most
recently **changed** cell. Let's say you wanted to multiply the contents of
the most recently changed cell by 10 if that most recently changed cell was
in the range C5:E10, you could put this in a cell....

=IF(AND(CELL("row")>4,CELL("row")<11,CELL("col")>2,CELL("col")<6),10*CELL("contents"),"")

and everything you entered something in a cell in the range C5:E10 and then
hit the Enter or Tab key, the contents of the cell you just changed would be
multiplied by 10 and displayed in the cell you put the above formula in.
Simply using the mouse to go to another cell would not work because the cell
you clicked into would be the one the above formula referred to, not the
cell you changed. You could mouse into another cell and hit the F9 key to
force a recalculation in order to make the above formula refer to the cell
you moused into though. Personally, I don't think the above formula is very
practical and I'm willing to bet that five CELL function calls are not very
efficient.

Rick
 
i think i may have worded it wrong...if i were to have a list of names in
column D....and i were to select a name(making it the active cell)...could I
have that name appear in a text box somewhere else in the worksheet?
 
Yes, you can do that too. First, make sure you have the toolbars you need
available. Click View/Toolbars/Visual Basic from Excel's menu bar in order
to display the Visual Basic toolbar. Next, go to the sheet you want to
locate the TextBox on, then click the Toolbox Control icon on the Visual
Basic toolbar (it looks like a hammer and wrench crossing each other);
select the TextBox icon (a rectangle with 'ab' inside) and then draw the
TextBox onto the worksheet. Next, right click the TextBox and select
Properties from the popup menu that is displayed; and type your sheet/cell
reference into the LinkedCell property on the Properties window that was
displayed (for example, Sheet1!A1 for cell A1 on Sheet1). Now, close the
Properties window and click both the Toolbox toolbar icon and Design Mode
icon (its icon looks like a triangle, ruler and pencil), which got turned on
when you displayed the Properties window, on the Visual Basic toolbar (the
two icons should not be highlighted when you are done). Okay, now anything
you type in the cell you referenced in the LinkedCell property will be
displayed in the TextBox AND, conversely, anything you type in the TextBox
will be displayed in the linked cell.

Rick
 
ok that works , thanks, is there anyway we can take that farther like with
visual basic...such as if a cell in Range A1:C10 is clicked then that clicked
cell value will be displayed in the textbox
 
Yes, you can do that also. First, go to the worksheet with the TextBox on
it, click the Design Mode icon on the Visual Basic toolbar, then right click
the TextBox and select Properties from the popup menu that appears... delete
the text (sheet/cell reference) in the LinkedCell property so that the
property has nothing in it anymore (we will control everything through code
instead). Now, go to the worksheet where the cells you want to monitor
(A1:C10 from your example) are located and right click that worksheet's tab;
then copy/paste the following code into the code window that appeared when
you did that...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Worksheets("Sheet2").TextBox1
If Not Intersect(Target, Range("A1:C10")) Is Nothing Then
.Value = Target.Value
Else
.Value = ""
End If
End With
End Sub

Note that I assumed the TextBox was on Sheet2 and named TextBox1 for this
example, adjust that as necessary. Okay, now whenever you click in one of
the cells in A1:C10 on whatever sheet you are monitoring, the TextBox on
Sheet2 will display the selected cell's content (it will be blank
otherwise).

Rick
 
Back
Top