If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
rhonda,
i copied the code given by you and pasted it onto a new blank workbook, and it works fine. can you post a little more detail as to what was different between your 'trial' workbook and 'actual' workbook? if i am not able to help, at least some other person who is able to help would find the details handy. greater the amount of detail you can provide, the better. such as, what other events might be running in your 'actual' sheet? what are the 'protection' statuses of your sheets? and whatever else that you could think of... mac. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Mac,
Thanks so much for your willingness to help me out with this. I am very embarrassed to report that the problem was that we had named the sheet tabs in the "actual" workbook so they were no longer "Sheet1", "Sheet2"... Once we plugged in the tab name, the macro worked perfectly. I really appreciate your response. Have a great weekend! Rhonda "rclark" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
EXCEL 2002: How do I user/simulate a Timer event in Excel form | JohnF | Worksheet Functions | 6 | September 23rd, 2004 04:56 PM |
EXCEL 2002: How do I user/simulate a Timer event in Excel form | JohnF | General Discussion | 1 | August 24th, 2004 05:56 AM |
Need help with Access decision | aualias | General Discussion | 23 | June 21st, 2004 02:04 AM |
Requery Combobox | MJ | Running & Setting Up Queries | 7 | May 25th, 2004 11:01 AM |