Using VBA to read from a List Box

[ThomasB]

Dear All

I have a list box with 3 options on a single protected worksheet (call it Worksheet 1)

the items in the list are in the following order call them a,b and C

When I select option a I would like to cause cells in the range

A1:E10 to become un protected and cells R1:Q10 to become protected
(both of the ranges are set as locked when the worksheet is unprotected)

when I select option b or c I would like the oposite to happen
ie R1:Q10 to become unprotected and cell A1:E10 to become protected:

does anyone:

a) Know if one can read from a listbox using VBA?
b) Prehaps have any VBA code for doing this?

Best Wishes

Thomas

 

[residnt]

From what I understand you want to read in the value that the user selected from the list box, correct? To do this is quite simple. All you need to an if statment to complete the tasks. So it would look something like this

if listbox.value = "a" then

<do stuff>

End if

Residnt

 

[HalfAce]

does anyone:
a) Know if one can read from a listbox using VBA?
b) Prehaps have any VBA code for doing this?

Hello ThomasB,
Assuming you're using a ListBox from the Controls Toolbox toolbar (and if you're not I
would suggest doing so), your code might look something like this:

Private Sub ListBox1_Click()
  With ActiveSheet
    Select Case .ListBox1.Value
      Case "a"
          .Unprotect
          .Range("A1:E10").Locked = False
          .Range("Q1:R10").Locked = True
          .Protect
      Case "b", "c"
          .Unprotect
          .Range("A1:E10").Locked = True
          .Range("Q1:R10").Locked = False
          .Protect
    End Select
  End With
End Sub

Hope it helps.