Using VBA to read from a List Box

ThomasB

Active Member
Joined
May 2, 2007
Messages
314
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

residnt

Board Regular
Joined
Nov 19, 2002
Messages
168
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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:
Code:
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.
 

Forum statistics

Threads
1,181,658
Messages
5,931,271
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top