+ Reply to Thread
Results 1 to 7 of 7

Worksheet event change with offset for different worksheet

  1. #1
    Registered User
    Join Date
    11-19-2011
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    11

    Worksheet event change with offset for different worksheet

    Hi!

    I have a problem where I would like to enter a value in Sheet1 in range A2:A20.
    Each value should be displayed with the Worksheet change event in range C2:C20 in Sheet2

    So far I have this which only creates value in C2:C20 in Sheet1, where as I want it in Sheet2. Any idea on how I can do that?

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub

    On Error GoTo ErrHandler
    Application.EnableEvents = False
    If Target.Column = 1 Then
    If IsNumeric(Target.Value) Then
    Target.Offset(0, 3).Value = Target.Value
    Else
    Target.Offset(0, 1).Clear
    End If
    End If
    ErrHandler:
    Application.EnableEvents = True
    End Sub
    Last edited by scooby99; 12-11-2011 at 07:43 AM.

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Worksheet event change with offset for different worksheet

    I do not understand your code but according your description such code should be ok
    Please Login or Register  to view this content.
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    11-19-2011
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Worksheet event change with offset for different worksheet

    That worked great, thanks!

    Would it be possible to enter a Isnumeric argument, so that when I delete values it doesnt have an effect?

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Worksheet event change with offset for different worksheet

    Hi Tom1977,

    Your code will not paste the Range from Sheet1 Column A to Sheet2 Column C. Instead it will paste it to Column A on Sheet2.

  5. #5
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Worksheet event change with offset for different worksheet

    Please Login or Register  to view this content.
    Winon You right it should be like this

    Please Login or Register  to view this content.
    scooby99

    try this

    Please Login or Register  to view this content.
    Last edited by tom1977; 12-11-2011 at 07:13 AM.

  6. #6
    Registered User
    Join Date
    11-19-2011
    Location
    Stockholm
    MS-Off Ver
    Excel 2007
    Posts
    11

    Talking Re: Worksheet event change with offset for different worksheet

    Thanks tom1977!

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Thumbs up Re: Worksheet event change with offset for different worksheet

    Hi tom1977,

    Thank you for your feedback! Together we shall stand, divided the Code will fail.
    Must admit that I have attempted to alter your Code, with my limited VBA knowledge and all, as follws to make it work
    Please Login or Register  to view this content.
    Must confess tough that your Code still beats the pants off mine!!!
    Well done.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1