help with macros

fooldiddy

New Member
Joined
Sep 1, 2007
Messages
4
how would i go about creating a macro that would cause certain text, that when inputted into cells, will change to other text?

i.e. joe shmo types his name "joe shmo" but in the cell it reads "poop"
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
That would certainly weird out the user, wouldn't it?

This code on the appropriate worksheet(s) would accomplish that, assuming they entered it within a range named foo
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Intersect(Target, Range("foo")) Is Nothing Then Exit Sub
  If Target.Value = "joe shmo" Then Target.Value = "poop"
End Sub
 

fooldiddy

New Member
Joined
Sep 1, 2007
Messages
4
so for the range.....instead of "foo" I just enter what columns or cells i want to be affected?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Why in the world would you want it to say that?

But anyway, You'll want to add a bit to that:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("A1:A10"), Target)
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c = "joe shmo" Then c = "poop"
    Next
Application.EnableEvents = True
End Sub
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Thanks for the commentary and expanding the code beyond the requested scope. Nice code.

O.P., I apologize for not flipping off events as astutely demonstrated by hotpepper. While it doesn't change the outcome (well, speeds it up a few milliseconds), it certainly does belong there. Thank you, hotpepper.
 

fooldiddy

New Member
Joined
Sep 1, 2007
Messages
4

ADVERTISEMENT

its not working for some reason.

Maybe i should add that I haven't ever worked with macros before.

I entered the code into the macros; went back to the sheet and tried the characters. Nothing?

I changed a snippet in the code because i want the "1" s in certain columns to show up as "poop"
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
It needs to go into the worksheet module for the sheet where you want this to happen on.

Copy the code.
Right click the sheet tab for the sheet where you want this to happen on.
Click on View Code.
Paste into white area.
Hit Alt-q
 

fooldiddy

New Member
Joined
Sep 1, 2007
Messages
4
how could i do this for multiple cells and columns. like a universal code that will affect the entire spreadsheet
 

Watch MrExcel Video

Forum statistics

Threads
1,122,858
Messages
5,598,487
Members
414,243
Latest member
Shockpulsar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top