+ Reply to Thread
Results 1 to 7 of 7

How to adjust VBA code to create rule for a specific cell, rather than a whole column

Hybrid View

jlax87 How to adjust VBA code to... 10-19-2015, 09:17 PM
skywriter Re: How to adjust VBA code to... 10-19-2015, 09:18 PM
jlax87 Re: How to adjust VBA code to... 10-19-2015, 09:25 PM
jlax87 Re: How to adjust VBA code to... 10-19-2015, 10:24 PM
TMS Re: How to adjust VBA code to... 10-19-2015, 09:37 PM
skywriter Re: How to adjust VBA code to... 10-19-2015, 09:42 PM
TMS Re: How to adjust VBA code to... 10-20-2015, 12:51 AM
  1. #1
    Registered User
    Join Date
    10-19-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Lightbulb How to adjust VBA code to create rule for a specific cell, rather than a whole column

    Hi there,

    I am currently using a VBA code on numerous tabs of my spread sheet (that I sourced from online), which allows the Excel user to select multiple options from a drop-down list (in a specific column/s), so that each value they select is then separated by a comma. I am a teacher and we needed to enable users to select multiple student names from a data validation list, to receive particular awards. I now want to know how to adjust the code so that instead of making this applicable to any number of columns with these drop-down lists, I can select a single cell and its drop-down list. The reason for this need is because the other cells in the column only require one option to be selected, but this specific cell requires FOUR options. I have pasted the current code I use below, which applies to the whole column D (4). Could anyone please suggest how I change it to only apply to cell D9? Thank you!

    Option Explicit
    ' Developed by Contextures Inc.
    ' www.contextures.com
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim lUsed As Long
    If Target.Count > 1 Then GoTo exitHandler
    
    On Error Resume Next
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo exitHandler
    
    If rngDV Is Nothing Then GoTo exitHandler
    
    If Intersect(Target, rngDV) Is Nothing Then
       'do nothing
    Else
      Application.EnableEvents = False
      newVal = Target.Value
      Application.Undo
      oldVal = Target.Value
      Target.Value = newVal
      If Target.Column = 4 Then
        If oldVal = "" Then
          'do nothing
          Else
          If newVal = "" Then
          'do nothing
          Else
            lUsed = InStr(1, oldVal, newVal)
            If lUsed > 0 Then
                If Right(oldVal, Len(newVal)) = newVal Then
                    Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
                Else
                    Target.Value = Replace(oldVal, newVal & ", ", "")
                End If
            Else
                Target.Value = oldVal _
                  & ", " & newVal
            End If
            
          End If
        End If
      End If
    End If
    
    exitHandler:
      Application.EnableEvents = True
    End Sub
    Last edited by jlax87; 10-19-2015 at 09:25 PM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2505
    Posts
    2,792

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  3. #3
    Registered User
    Join Date
    10-19-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    Cheers, didn't know. It is fixed now.

  4. #4
    Registered User
    Join Date
    10-19-2015
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    Thanks so much!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,452

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    If Target.Column = 4 Then
    becomes
    If Target.Address = "$D$9" Then
    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2505
    Posts
    2,792

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    Thanks for the code tags.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,452

    Re: How to adjust VBA code to create rule for a specific cell, rather than a whole column

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adjust column width when specific cell value changes.
    By JO505 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2015, 03:14 PM
  2. [SOLVED] Adjust Code to Sort by specific Cell characters found with cell
    By boldcode in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-15-2014, 02:34 PM
  3. create a button in excel with macros or VBA code in a specific cell targets.
    By Giri.hb in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-18-2014, 10:21 AM
  4. [SOLVED] Auto height adjust to a specific column only
    By alfgrey in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2014, 08:32 AM
  5. creating rule to adjust fields..
    By Symbiot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-01-2009, 04:28 PM
  6. If Cell is Empty create rule to Display the current Date
    By Sidewinder in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-05-2007, 04:12 PM
  7. Using Code to Adjust Specific Column Width
    By Nigel Bennett in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2005, 03:06 AM

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