Twin cells


A

Amnon Wilensky

Hi,
Is it possible to make two cells "Twins"? What I mean is if I'll input in
cell A1 the number 1 it will appear on cell B1, and vice versa?

Using Excell 2003.

Thanks,

Amnon
 
Ad

Advertisements

G

Gary''s Student

To make it work one way, all you need is a formula. To make it work both
wasy, you need and event macro. Put the following in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ab = Range("A1:B1")
Set a = Range("A1")
Set b = Range("B1")
Set t = Target
If Intersect(t, ab) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(t, a) Is Nothing Then
a.Value = b.Value
Else
b.Value = a.Value
End If
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
D

Don Guillett

Right click sheet tab>view code>copy/paste this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("a1").Address Then Range("b1") = Target
If Target.Address = Range("b1").Address Then Range("a1") = Target
Application.EnableEvents = True
End Sub
 
A

Amnon Wilensky

Beautiful,

Thanks,

Amno

Don Guillett said:
Right click sheet tab>view code>copy/paste this.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = Range("a1").Address Then Range("b1") = Target
If Target.Address = Range("b1").Address Then Range("a1") = Target
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
 
S

Shane Devenshire

Hi,

Here is yet another solution:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
Set isect = Application.Intersect(Target, [A1:B1])
If Not isect Is Nothing Then
[A1:B1] = Target
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top