Excel VBA Excel Macro to call drop down Validation List

[wheelert6]

I am trying to write a macro that calls up a named validation (drop down) list based in a particular cell based that cell is a certain value. For example, I have named the list "MailRef" and would like the drop down to appear in cell "B2" if the value of "B4" reads "Letter".

I am using excel 2003 - Any help on this would be much appreciated.

I have so far tried the following code:

Sub CallValidation()
With Sheets("Mail Out")
If Range("D2") = "Offer" Or Range("D2") = "Quote" Then

With Range("B2").Validation
.Delete
.Add Type:=x1ValidateList, Formula1:="=MailRef"

End With

End If
End With

End Sub


Thanks.

 

[alow]

Are you looking to have different lists show up if different things are put? What you could do in this instance, is Instead change the named list's name to "Letter" and have the conditional formatting for B2 be =B4
Then, when you put Letter in B4, it would refer to the named range "Letter" which should bring up your list. I'm doing this from memory, let me know if that isn't what your looking for, or doesn't work the way I think it will.

 

[alow]

Correction, the formula for the data validation of B2 would be =INDIRECT(B4).

 

[wheelert6]

I am looking to have only one list show up (named "MailRef") in B2 under the condition that D4 says "Quote" or "Offer". I apologise for previously saying "Letter". Otherwise I intend cell B2 to remain emply and for values to have to be input manually.

Are you looking to have different lists show up if different things are put? What you could do in this instance, is Instead change the named list's name to "Letter" and have the conditional formatting for B2 be =B4
Then, when you put Letter in B4, it would refer to the named range "Letter" which should bring up your list. I'm doing this from memory, let me know if that isn't what your looking for, or doesn't work the way I think it will.

 

[alow]

I'm not much of a VBA Person, but I think this will do the trick. Open VBA and put it in the Code for the Specific sheet(s) that have the cells you're working with:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then
    If Worksheets("Sheet1").Range("D4").Value = "Quote" Or Worksheets("Sheet1").Range("D4").Value = "Offer" Then
        With Range("$B$2").Validation
            .Delete
            .Add xlValidateList, , , "=MailRef"
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    Else
    Range("$B$2").Validation.Delete
    End If
End If
End Sub

 

[wheelert6]

Thank you very much it works brilliantly. I'll have to let you know if I need any further help around this..

 

[wheelert6]

Actually would you be able to advise how to get this particular piece of code to loop over and over such that it will work for rows 2 to 100 say. e.g. rather than using B2 & D4, it would loop all the way through to B100 and D102.

Let me know if this comes to mind. Thanks.

 

[alow]

That's something that is beyond being off the top of my head, but if I figure something out, I'll let you know. If you do find out, I'd be interested to see the results too.

 

[alow]

I got lucky enough on figuring this out...I think. Let me know if this does it or not:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$D$4:$D$102")) Is Nothing Then Exit Sub
    For Counter = 4 To 102
        Set curCell = Worksheets("Sheet1").Cells(Counter, 4)
         
        
        If curCell.Value = "Quote" Or curCell.Value = "Offer" Then
            With Cells(Counter - 2, 2).Validation
                .Delete
                .Add xlValidateList, , , "=MailRef"
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        Else
        Cells(Counter - 2, 2).Validation.Delete
        End If
    Next Counter
End Sub

 

[wheelert6]

Thanks alot thats great, works perfectly. I'll have to post on here if I have any more queries.

 

[wheelert6]

Hi Again,

Can I ask whether it would still be possible to execute / rewrite this macro if the named list "=MailRef" is a list from another workbook on the same shared drive?

One further query I have is whether the macro can be altered such that no entires can be made in the range D4 - D102 unless at least some value is in B2 - B100?

Hopefully this will be the last issue I have!

Thanks

 

[alow]

Now we are truly out of what I can provide. Working with data in a separate file in Excel can be tricky when updating the data manually. I'm not sure how VBA works with updating linked data in the least. I can only suggest Google, or perhaps a new thread.

 

[wheelert6]

Ok sure. Understood that it is certainly alot tricker.

If you interested I have successfully been able to develop the following code from other sites that copies data from the another workbook and prints it accordingly.

Private Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("R:\Bids\Commercial Drive - Bid Tracker.xls", True, True)
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Mail Out")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("H10", "I20").Formula = wb.Worksheets("Summary of Bids").Range("B10", "C20").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub



I now simply need to be able to adjust the copying range from ("H10", "I10") to the named group "=MailRef". I don't expect too much more but appear to be half way there now.