event procedure
Hi,
On 8/27 I posted a question regarding how to enter data once and have it go
to a noncorresponding cell on another sheet within the same workbook, moving
the data to another cell if the intended cell already held data. I received
in response a wonderful event procedure (thanks, Erin) that worked
beautifully in a "trial" workbook. When I applied the event procedure to my
actual workbook, however, it wouldn't work. Data entered into the input cell
didn't go anywhere. I thought one of you might be willing to help me
troubleshoot.
I've copied the event procedure below. I want data entered into sheet6,
cell C8 to go to sheet9, cell C22 (or if C22 already has data, to go to D22,
then E22, and so forth....). As I mentioned, the event procedure noted below
works when used on a new workbook. When it didn't work in my actual
workbook, I tried the following: made certain the cells did not contain
other commands (not sure if that is the right word), made certain I had the
correct sheet and cell numbers, made sure the sheets were unprotected and the
cells unlocked, and made certain the font color for C22 wasn't white. What
else should I consider?
I right-clicked on sheet6 tab and selected "View Code", then entered the
following:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim NewCell As Range
If Target Range("C8") Then Exit Sub
Application.EnableEvents = False
Set NewCell = Sheets("Sheet9").Range("C22")
Do Until IsEmpty(NewCell)
Set NewCell = NewCell.Offset(0, 1)
Loop
NewCell.Value = Target.Value
Application.EnableEvents = True
End Sub
Then I resaved the file.
I greatly appreciate any and all thoughts, suggestions, ideas.
--
Rhonda Clark
|