+ Reply to Thread
Results 1 to 7 of 7

VBA commands for multiple cells?

Hybrid View

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    6

    VBA commands for multiple cells?

    Hello, I'm a complete amateur in VBA so my question may be stupid but I really don't know how to resolve my problem. So, I have an excel table with multiple drop-down lists. However for certain cells, I'd like to have multiple options choice so I've modified to following code to work for me. The problem is that it works only for one cell and I want it to work for multiple cells but not all: For example; I want to have this multiple choice drop down lists in cells D8, D13, D18 and D21. Can you help me with that please )

    Here's the code I'm using.

    HTML Code: 
    Last edited by JLoKyM; 08-28-2013 at 05:29 AM.

  2. #2
    Registered User
    Join Date
    07-02-2013
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: VBA commands for multiple cells?

    So no one knows the answer to my question?

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: VBA commands for multiple cells?

    Hi JLoKyM..

    Please use code tag for Codes.. otherwise it really hard to read.. and you may also get an idea that.. "No One knows the answer.."

    BTW.. try this..

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngDV As Range
    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
            Select Case Target.Address
                Case "$D$8", "$D$13", "$D$18", "$D$21"
                    If Target.Value = "" Then GoTo exitHandler
                        If Target.Offset(1, 0).Value = "" Then
                            Target.Offset(1, 0).Value = Target.Value
                        Else
                            Target.Offset(1, 0).Value = _
                            Target.Offset(1, 0).Value _
                            & ", " & Target.Value
                        End If
            End Select
        End If
    exitHandler:
        Application.EnableEvents = True
    End Sub
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Registered User
    Join Date
    07-02-2013
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: VBA commands for multiple cells?

    Hi, sorry about not using code tag. it's fixed now.
    I've tried your idea but I get the following error: Compile Error: End if without block if.
    And I don't know whether this is important but the first line: Private Sub Worksheet_Change(ByVal Target As Range) becomes in yellow.

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: VBA commands for multiple cells?

    I din't found any error in my code.. and working fine.. for me..
    All IF blocked are closed..

    Right click Sheet name, where dropdown Data Validation exists.. then..
    paste code in the same module..
    Dont use the code in New Module..

    I know you are pasting the code in Sheet module only, as Worksheet_Change event triggered and highlighted with Yellow.. still for precaution..
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-02-2013
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    6

    Red face Re: VBA commands for multiple cells?

    Thank you very very much, it's working perfectly!!

  7. #7
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: VBA commands for multiple cells?

    Thanks for the feedback..

    Please mark the post as solved..

+ 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. Multiple IF & +IF commands
    By simpleton58 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2013, 01:37 PM
  2. Macro Recording Chart Format commands missing all important commands!!!!
    By nounours in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2013, 09:20 PM
  3. Multiple Commands
    By jpxexcelforum in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2011, 04:37 AM
  4. Multiple Commands with Variables
    By JLucoff in forum Excel General
    Replies: 3
    Last Post: 07-26-2011, 04:26 PM
  5. Multiple If Commands
    By markswan20 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-17-2008, 05:54 PM

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