Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts

    VBA Script for Excel 2010 - re-populating a cell based on values in a different cell

    Hello all,

    I have a user registration form that I have a requirement that I can not fix using formulas.

    The range of the form for user data entry is 300 rows, from row 10 to 310.

    Column T has a drop down that can be one of three options: Yes - product A; Yes - product B; No

    Column V has a drop down that can be either Yes or No.

    The requirement I have is that if the user selects "Yes" on Column V, Column T must change whatever value is there already to be "Yes - Product A" and lock unless Column V is updated to reflect "No".

    I was trying to use some code I found on other sites, but it isn't working. It isn't breaking the other script I have running for other items, but it isn't working as it stands.

    The (proven wrong) code I am working with is:

    If Range("v10:v310").Value = "No" Then
    Range("t10:t310").Value = Target.Value
    Else: Range ("t10:t310")
    .Clear
    .Value = "Yes - Product A"
    .Locked = True
    End If

    Thanks to any help provided!

    Regards,

    tburk

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TBurk,

    Welcome to the Lounge as a New Poster!

    Just to clarify...If the user Selects YES in say V15 then T15 is changed to Yes - Product A.
    If the user attempts to change T15 to another value While T15 = YES then do not allow.
    This logic must be active for Rows 10-310.

    Is this correct?

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TBurk,

    It my assumptions above are correct this code placed in the WorkSheet Module should do the trick.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("V10:V310"), Target)
       If isect Is Nothing Then
    '     MsgBox "Ranges do not intersect"
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         If UCase(Target.Text) = "YES" Then
           Cells(Target.Row(), ["T"]) = "YES - Product A"
         End If
         Application.EnableEvents = True '*** Reset Events ***
       End If
       
       Set isect = Application.Intersect(Range("T10:T310"), Target)
       If isect Is Nothing Then
    '     MsgBox "Ranges do not intersect"
       Else
         '***Prevent following code from refiring Change Event ***
         Application.EnableEvents = False
         If UCase(Cells(Target.Row(), ["V"])) = "YES" Then
           Application.Undo
         End If
         Application.EnableEvents = True '*** Reset Events ***
       End If
    Test File: TBurk.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    tburk (2015-03-23)

  5. #4
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    TBurk,

    Welcome to the Lounge as a New Poster!

    Just to clarify...If the user Selects YES in say V15 then T15 is changed to Yes - Product A.
    If the user attempts to change T15 to another value While T15 = YES then do not allow.
    This logic must be active for Rows 10-310.

    Is this correct?

    HTH
    Hello RG - thank you for responding so quickly.

    Yes, this is the behavior that is requested. When any field in the T column reflects "Yes", the V column must update to reflect "Yes - Product A" and not allow changes until T column either is updated by the user to reflect "No" or be blanked out.

  6. #5
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hello RG,

    I tried the provided example script, but it responds like my other attempts - it doesn't respond, but doesn't produce the locking of column T or setting the value when column V says "Yes".

  7. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Time to attach your file along with a clear explanation and before/after examples.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  8. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Cisco Phone User Request Template.xlsm

    Here is the stripped down version of the file and unprotected the sheet and the code for viewing. The code provided by RG is towards the bottom of the code on the sheet.

    Yes - Product A is equitable to "Yes - Verint Impact 360".

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TBurk,

    The code didn't work because you changed "YES" to "Yes" in 2 places! I know from your sheet that you place Yes using the dropdowns but the code is designed to take yes in any form {Ucase()} so it will accept yes, Yes, YES, yEs, etc. Change them back to YES and the code works. Also note although the code will not "LOCK" the cell physically but it will not allow it to be changed! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    tburk (2015-03-23)

  11. #9
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Well, paint me more of an amateur than I already thought I was when it came to VBA. I'm rather good with formulas and data manipulation, but VBA is a new world.

    Thank you, RG, that does work now to have Column T populate to the required value when column V shows "Yes". However, I am still able to change T back to another value after V shows "Yes". Did I make another alteration in error? Should I make the bolded/underlined Yes back to YES as well?

    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed

    Dim isect As Range

    Set isect = Application.Intersect(Range("V10:V310"), Target)
    If isect Is Nothing Then
    ' MsgBox "Ranges do not intersect"
    Else
    '***Prevent following code from refiring Change Event ***
    Application.EnableEvents = False
    If UCase(Target.Text) = "YES" Then
    Cells(Target.Row(), ["T"]) = "Yes - Verint Impact 360"
    End If
    Application.EnableEvents = True '*** Reset Events ***
    End If

    Set isect = Application.Intersect(Range("T10:T310"), Target)
    If isect Is Nothing Then
    ' MsgBox "Ranges do not intersect"
    Else
    '***Prevent following code from refiring Change Event ***
    Application.EnableEvents = False
    If UCase(Cells(Target.Row(), ["V"])) = "YES" Then
    Application.Undo
    End If
    Application.EnableEvents = True '*** Reset Events ***
    End If

  12. #10
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Nope, that didn't do the deed, I'm afraid.

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    try this attached file.
    I had to protect the sheet to show that when you select a 'Yes' in col [V], it sets the [T] value and locks the [T] cell from changes.
    In my demo, the sheet password is in cell [AD2].
    You can only change this password when the sheet is unlocked.
    (the password could be hidden of course, or set in the vba code)

    In my demo, I didn't 'hard-code' the entry for the [T] cell when [V] is chosen as 'Yes'
    Rather, it takes the required value from the first entry of whatever you have set in the data validation allowed entries.

    zeddy
    Attached Files Attached Files

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TBurk,

    It worked in my tests w/o the dropdown. However, the Application.Undo seems to be ineffective with the dropdown! I stepped through the code and it goes through the Application.Undo statement but just doesn't change the value back like it did with a normal cell. Will investigate further. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TBurk,

    Ok this seems to work:

    Change: Application.Undo
    TO : Cells(Target.Row(), ["T"]) = "Yes - Verint Impact 360"

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. The Following User Says Thank You to RetiredGeek For This Useful Post:

    tburk (2015-03-23)

  17. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Just a note though:
    If a user select a 'Yes' cell in column [V], and then 'pastes' it to a block of cells in column [V], you won't get what you wanted in the corresponding [T] cells.
    Why? Because your change-detection routine ignores when 'multiple' cells have been 'changed', e.g. through a 'paste copy' operation. This could be fixed.

    zeddy

  18. #15
    New Lounger
    Join Date
    Mar 2015
    Posts
    8
    Thanks
    4
    Thanked 0 Times in 0 Posts
    That worked, RG, thank you! Excellent work.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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