List box macro

[giri_luck]

hi,

I have a macro,

Sub Add_Drop_Down_Menu_Selection()
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=$C$1:$C$39"
.IgnoreBlank = True
.InCellDropdown = True
End With
End Sub

It lists all the contents from C1 to C39 and it runs for a selected sheet.

I need to modify the macro so my list box is in Sheet 1 and the contents C1 to C39 is in Sheet 2.

Thanks

[dwint]

change your code with this one

Formula1:="=Sheet2!$C$1:$C$39"

[Trebor76]

Hi giri_luck,

Welcome to the forum!!

You need to create a named range for data validation to be used on data that is in a different tab from where the data validation resides. To do this, follow these four steps:

1. Select Sheet2 and highlight the desired range (C1:C39 in this case)
2. Press and while holding down the Ctrl tab press F3
3. From the Name Manager dialog click the New button
4. From the New Name dialog, enter an unique name (in the following macro I've used MyList) into the Name field, check the other details and then click OK

Then try the following macro:

Option Explicit
Sub Add_Drop_Down_Menu_Selection()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    With ActiveCell.Validation
        .Delete
        .Add xlValidateList, , , "=" & "MyList" 'This is the name I gave the named range. Change it to whatever you end up calling yours.
        .IgnoreBlank = True
        .InCellDropdown = True
    End With

End Sub

Regards,

Robert