+ Reply to Thread
Results 1 to 10 of 10

problem in running worksheet_change code

Hybrid View

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    problem in running worksheet_change code

    The code runs wrongfully i want that if in the target column any cell having text "rejected" than auto insert row below that with formula wherever formula is there.

    Please rectify the code and send me as soon as possible..


    Private Sub Worksheet_Change(ByVal Target As Range)
    Const Prod_PWD = "123"
    Dim i As Long
    
    If Target.Column = 20 And Target.Row > 5 And Not Updating Then
    If MsgBox("Add new row?", vbYesNo, "Change Sheet") = vbYes Then
    Updating = True
    ActiveSheet.Unprotect Password:=Prod_PWD
    
    Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
    Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column)
    Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
    For i = 1 To 12
    Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
    Next i
    CopyCells 17
    CopyCells 19
    CopyCells 25
    CopyCells 26
    CopyCells 27
    CopyCells 28
    CopyCells 29
    CopyCells 30
    CopyCells 32
    Cells(ActiveCell.Row, 20).Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=Prod_PWD
    Updating = False
    End If
    End If
    End Sub
    
    Sub CopyCells(ColNum As Long)
    Cells(ActiveCell.Row - 1, ColNum).Select
    Selection.Copy
    Cells(ActiveCell.Row + 1, ColNum).Select
    ActiveSheet.Paste
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: problem in running code

    please attach a sample file and explain better your goal
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: problem in running code

    hi patel45

    please find the attach file for your ready reference
    Attached Files Attached Files
    Last edited by vipulhumein; 04-12-2013 at 03:31 AM. Reason: wrong file attached

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: problem in running worksheet_change code

    Your code does not search "rejected", I'm confused, what's your goal ? what's the goal of your code ?

  5. #5
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: problem in running worksheet_change code

    hello patel45
    yah my code does not search "rejected" i know that but i want that if in target.column 20(i.e column T) if "rejected" will be entered then a row below that should be inserted below that....

    so please rectify the same as mentioned above...
    Thanks Patel in Advance

  6. #6
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: problem in running worksheet_change code

    I'm sorry, I did understand nothing

  7. #7
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: problem in running worksheet_change code

    see in column T there is dropdown for "accepted and rejected" . i want that if anyone select from dropdown "rejected" then an automatic row should be inserted below that with same formula..in my sheet i have written vba for auto row insert but i want that if in column T "rejected" is selected from dropdown the auto row insert code should run else it should not run...

  8. #8
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: problem in running worksheet_change code

    hello patel please help me out of this its very necessary

  9. #9
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: problem in running worksheet_change code

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 20 And Target.Row > 5 Then
      If Target = "Rejected" Then
         Target.EntireRow.Copy
         Target.Offset(1).EntireRow.Insert
         Application.CutCopyMode = False
         
      End If
    End If
    End Sub

  10. #10
    Registered User
    Join Date
    03-28-2013
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: problem in running worksheet_change code

    thank u very much patel45
    it works

+ 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