Whan a letter is entered it changes to a symbol that is the value ofa different cell

[Michael Lanier]

Is there a way to enter a letter in a letter in a cell and once the
Enter key is struck, the letter will change to become a symbol that is
located in another cell? More specifically:

1) I have multiple ranges where I usually enter an "x". The ranges
are A1:C10 and A20:C30.

2) There is a symbol in Z1.

When I enter the "x" in multiples places within my ranges, I want the
"x" (or any other letter that might be entered) to instantly change to
the symbol when I stike the Enter key. Is this possible? Thanks for
any help you may offer.

Michael

 

[Luke M]

Right click on sheet tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:C10")) Is Nothing And _
Intersect(Target, Range("A20:C30")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Target.Value = Range("Z1").Value
Application.EnableEvents = True
End Sub

 

[Bernie Deitrick]

Michael,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:C10,A20:C30")) Is Nothing Then Exit Sub
If Target.Value <> "" Then
Application.EnableEvents = False
Range("Z1").Copy Target
Application.EnableEvents = True
End If
End Sub

 

[Michael Lanier]

Thanks Luke. Unfortunately the following debugs:

If Intersect(Target, Range("A1:C10")) Is Nothing And _
Intersect(Target, Range("A20:C30")) Is Nothing Then Exit Sub

No explanation is given and I'm purplexed.

Michael

 

[Michael Lanier]

Bernie,

Thanks for the macro. However, the following debugs:

If Target.Cells.Count > 1 Then

I'm uncertain as to how to proceed.

Michael

 

[Bernie Deitrick]

Michael,

It sounds like you put the code into the wrong place. Right-click the sheet tab, select "View Code",
and paste the code into the window that appears.

The code needs to go into the worksheet codemodule, not a standard codemodule. The left-hand
dropdown above the code window should say "Worksheet" If it says "General" you pasted the code
into a standard codemodule.

HTH,
Bernie
MS Excel MVP

 

[Michael Lanier]

Thanks for your help Bernie.

You were right. I misplaced my entry. (My status as a novice is
showing.) I tried to reconstruct a bit and enter the macro in
ThisWorkbook but again with no success. Perhaps you can quickly see
the problem. The range is a little different from my original sample
entry, as is the cell with the symbol. If you have a solution, it
would be much appreciated. Thanks again.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As
Range)
'MAKE 'X' CHANGE TO 'CHECK MARK' IN EXTBID & INTBID
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Worksheets("ExtBid").Range
("R2:T4,R7:T27,R42:T47")) Is Nothing Then Exit Sub
If Target.Value <> "" Then
Application.EnableEvents = False
Worksheets("ExtBid").Range("AA4").Copy Target
Application.EnableEvents = True
End If
End Sub

 

[Gord Dibben]

I too am perplexed.

Works for me.

Try this version.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:C10, A20:C30"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
Target.Value = Range("Z1").Value
End If
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Bernie Deitrick]

Michael,

For the Thisworkbook code, you need to check the Sheet name:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'MAKE 'X' CHANGE TO 'CHECK MARK' IN EXTBID & INTBID
If Target.Cells.Count > 1 Then Exit Sub
If sh.Name = "ExtBid" Or sh.Name = "IntBid" Then
If Intersect(Target, sh.Range("R2:T4,R7:T27,R42:T47")) Is Nothing Then Exit Sub
If Target.Value <> "" Then
Application.EnableEvents = False
Worksheets("ExtBid").Range("AA4").Copy Target
Application.EnableEvents = True
End If
End If
End Sub

If IntBid has a different symbol, then use

sh.Range("AA4").Copy Target

in place of


Worksheets("ExtBid").Range("AA4").Copy Target

 

[Michael Lanier]

I can find no logical reason why none of the above macros work, but
for some reason, everything I try debugs. I can only conclue that
some other macro is in conflict although there is nothing that really
gives any such indication.

Thank each of you for your efforts. They are much appreciated.

Michael

 

[Michael Lanier]

FANTASTIC!

Bernie, your changes to my macro work. Thanks so very much. I do
have one last (I hope) question. When the macro executes, it
overrides my conditional formatting for each applicable cell. The
formatting is different throughout the range. Is there a way to avoid
changing the conditional formatting? Thanks.

Michael

 

[Bernie Deitrick]

Change

Worksheets("ExtBid").Range("AA4").Copy Target

to

Target.Value = Worksheets("ExtBid").Range("AA4").Value

I thought that the symbol might require a special font...

If it does, then use you may need to add

Target.Font.Name = "whatever"

Record a macro when applying the font to get the name.


HTH,
Bernie
MS Excel MVP

 

[Michael Lanier]

Thanks Bernie. I couldn't ask for anything better. You are much
appreaciated.

Michael