VBA for the following - cell range clarification needed

[halesowenmum]

Hi

I have a workbook within which there is a worksheet called Actions. In cells H5:H18 I want to reference worksheet Lists, cells C22:C29.

I have the VBA for the thing I'm trying to do (allow multiple selections from the drop down list) so that's all fine, but my VBA knowledge is very limited such that I don't know how or where exactly to edit the VBA provided to me, shown below to ensure I'm referencing the locations I need:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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 = 3 Then
    If oldVal = "" Then
      'do nothing
    Else

Can someone kindly advise me with what to adjust where?

Thank you.

 

[MickG]

This code will enable you to select multiple data from you validation cells.

In cells H5:H18 I want to reference worksheet Lists, cells C22:C29.

I don't understand this ,but assume your validation cells are in H5:H18 and each validation cells in based on the list in cells "C22:C29"
It not really important as long as you have your validation cells with validation lists
NB:- The way the code is written , any validation cell in your Worksheet will allow Multiple selection.

Paste this code in worksheet Module:- Right click sheet tab , select "View Code" vb window appears.
Paste code in VB window.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] rngDV [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] oldVal [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] newVal [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]If[/COLOR] Target.Count = 1 [COLOR=navy]Then[/COLOR]
[COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] [COLOR=navy]Resume[/COLOR] [COLOR=navy]Next[/COLOR]
[COLOR=navy]Set[/COLOR] rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
[COLOR=navy]If[/COLOR] rngDV [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
    [COLOR=navy]If[/COLOR] Not Intersect(Target, rngDV) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
        Application.EnableEvents = False
            newVal = Target.Value
            Application.Undo
            oldVal = Target.Value
            Target.Value = newVal
                [COLOR=navy]If[/COLOR] Not newVal = "" [COLOR=navy]Then[/COLOR]
                    Target.Value = IIf(oldVal = "", newVal, oldVal & ", " & newVal)
                [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
Application.EnableEvents = True
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[halesowenmum]

That's just what the doctor ordered - many thanks indeed.

 

[MickG]

You're welcome