Run VBA based on cell value and possibly back again

[tom_blamire]

hi all,

I am having a major problem where I have come to a halt on finding a solution. This is my problem:

I have a worksheet where there is a drop down (cell c18:g18 - this is a merged cell), if "Cloud" is selected then I need excel to make it run a certain macro whereby the range in c21:c37 become a data validation list from the information stored in range in T1:T6.

Now, if "Cloud" isn't selected and another choice is made, I need the range in C21:c37 to become a free text field.


Is this possible?


Kind regards in advance

Tom

 

[Comfy]

Place this in the sheet module that you want it to run for:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C18:G18")) Is Nothing Then
    If Target.Value = "Cloud" Then
        With Range("C21:C37").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$T$1:$T$6"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    Else
        With Range("C21:C37").Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
        End With
    End If
End If
End Sub

 

[tom_blamire]

Hi Comfy, thank you for that but it doesn't seem to do anything???

 

[Comfy]

Where did you paste the code?

In a VBA module or in the worksheet module?

 

[tom_blamire]

in the Excel object under the sheet name