How to Change Value of Cell in one worksheet based on value of listbox in different worksheet RRS feed

  • Question

  • Hello,

    I have a workbook comprised of many different sheets. Each sheet represents a department. Each department features a data validation drop-down that displays different data based on the value of the drop-down. I am trying to write a function that will change all of the drop downs on each sheet to one value based on the selection of one criterion in the list box (thereby forcing all sheets to display the data for the same region). So far, this is what I have:

    Private Sub ListBox1_Click()
        If ListBox1.Value = "All Zones" Then
            ActiveCell.Value = "All Zones"
        End If
    End Sub

    I am getting a Run-time error '9': Subscript out of range

    I am pretty new to VBA, and am wondering why my code is not working??


    Monday, July 11, 2016 8:51 PM