Auto_Open() and OnEntry Problem - please help!

Posted by Artem on March 08, 2001 9:46 PM

Hello,

I would be very gratefull if anyone could help me with my problem.

I'm trying to create a macro which would run after a cell entry has been changed. Specifically, after i change a cell I want

the macro to take the previous (old) input of the active cell as string, then do Replace all the occurences of this string in

a current active row with the new input string. For example if I have a row with the following entries from A1 to E1 (i use |

to show separate cells):

Then if I change the first cell to smth. like AAAA, i want the row to be like this:

And I want to be able to do it for multiple rows.

Unfortunately, my two-day VBA experience didn't help me much. The only thing i could write is for the macro to change

predefined entries in a row with a new input, i.e. it would change all occurencies of a certain word in an active row, but

unfortunately i couldn't make that "certain word" to be a old cell entry. Here's what i have so far (this has extensive

copy-pasting from examples off the web).

Sub Auto_Open()

ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange"

End Sub
-------------------------------------------------------------------
Sub DidCellsChange()

Dim KeyCells As String

KeyCells = "A1:A5"
' If the Activecell is one of the key cells, call the
' ReplaceEntireRow macro.

If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then ReplaceEntireRow

End Sub
-------------------------------------------------------------------
Sub ReplaceEntireRow()

Dim NewWord As String
NewWord = ActiveCell.Value
ActiveCell.EntireRow.Replace What:="AAAA", Replacement:=NewWord, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

As you see this will only replace AAAA occurencies in an active row, not the old entry occurencies. So this is my problem.

Thank you very much for your help!

Posted by David Hawley on March 09, 2001 1:26 AM

Hi Artem

You are after the Sheet Change Event for this.
Right click on your sheet name tab and select "View Code". Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim KeyCells As Range
If Target.Cells.Count > 1 Or OldWord = "" Then Exit Sub
Application.EnableEvents = False
Set KeyCells = Range("A1:A5")
NewWord = Target
On Error Resume Next
If Not Application.Intersect(Target, KeyCells) Is Nothing Then
ReplaceEntireRow
rw = Target.Row
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
OldWord = ""
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
OldWord = Target
End Sub

Then in a normal Module Put:

Public NewWord As String
Public OldWord As String
Public rw As Long
Sub ReplaceEntireRow()
Rows(rw).Replace What:=OldWord, Replacement:=NewWord, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

End Sub

The prefered method for running Macro on opening of Excel is:

Private Sub Workbook_Open()
'Do it!
End Sub

This needs to be placed with the Module of "ThisWorkbook". The Auto_Open is really only for backward compatability.

Dave

OzGrid Business Applications

Posted by Artem on March 09, 2001 9:12 AM

Thank you, Dave!!!