A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

event procedure



 
 
Thread Tools Display Modes
  #1  
Old September 2nd, 2004, 06:31 PM
rclark
external usenet poster
 
Posts: n/a
Default 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
Ads
  #2  
Old September 2nd, 2004, 06:59 PM
icestationzbra
external usenet poster
 
Posts: n/a
Default

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  
Old September 3rd, 2004, 02:41 PM
rclark
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.