Can it be done in excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone have any suggestions on how to do this trick in excel?

Within the cell A1, B1, C1, when I enter a number in one of three cell, such
as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On
the next step, if I enter 6 in cell B1, then 6 will be automatically display
on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be
automatically display on cell A1 and B1.
Does anyone have any suggestions on how to do this trick in excel?
Thanks in advance for any suggestions
Eric
 
In a sheet module enter:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1:C1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("A1:C1").Value = Target.Value
Application.EnableEvents = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Very simple:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:C1")
If Intersect(r, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Value = Target.Value
Application.EnableEvents = True
End Sub


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

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

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
 
Does anyone have any suggestions on how to do this trick in excel?

Within the cell A1, B1, C1, when I enter a number in one of three cell, such
as 3 in cell A1, then 3 will be automatically display on cell B1 and C1. On
the next step, if I enter 6 in cell B1, then 6 will be automatically display
on cell A1 and C1. On the next step, if I enter 8 in cell C1, then 8 will be
automatically display on cell A1 and B1.
Does anyone have any suggestions on how to do this trick in excel?
Thanks in advance for any suggestions
Eric
Click to expand...

Right click on the sheet tab.
Select View Code
Paste the code below into the window that opens:

=======================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Set AOI = Range("A1:C1")
Dim c As Range

If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In AOI
c.Value = Target.Value
Next c
Application.EnableEvents = True
End If

End Sub
====================================
--ron
 
Thank everyone very much for suggestions
Could anyone tell me how to modify Range("A1:C1") into different cells? such
as
A13, A67, A100.
Thank everyone for any suggestions
Eric
 
Thank everyone very much for suggestions
I would like refer to specific cells rather than range.
Could anyone tell me how to modify Range("A1:C1") into different cells? such
as A13, A67, A100.
Thank everyone for any suggestions
Eric
 
Thank everyone very much for suggestions
I would like refer to specific cells rather than range.
Could anyone tell me how to modify Range("A1:C1") into different cells? such
as A13, A67, A100.
Thank everyone for any suggestions
Eric
 
We only need to change a single line of code to use any three disjoint cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A13,A67,A100")
If Intersect(r, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Value = Target.Value
Application.EnableEvents = True
End Sub
 
Thank everyone very much for suggestions
I would like refer to specific cells rather than range.
Could anyone tell me how to modify Range("A1:C1") into different cells? such
as A13, A67, A100.
Thank everyone for any suggestions
Eric
Click to expand...

Merely change the parameters to the Range Property.

In my contribution, it would be:

Set AOI = Range("A13,A67,A100")
--ron
 
Back
Top