Excel 2010. Can I use a listbox on a form so as to restrict the input choices?

[PWD]

Good afternoon, I would like to modify a userform and change a textbox to a listbox so a user can only choose from 4 pre-set values instead of being able to enter free text. I can't see anything obvious in any of the properties. Is it possible?

Many thanks,
D€$

 

[PWD]

        UserFormPersonalDetails_Initialize

Which is

Private Sub UserFormPersonalDetails_Initialize()
Dim ListBox1 As ListBox

On Error GoTo ErrorHandle

With UserFormPersonalDetails.ListBox1
   .AddItem "Gold"
   .AddItem "Silver/Tactical Advisor"
   .AddItem "Bronze"
   .AddItem "Loggist"
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Then the OK button code that existed is just slight modified

Private Sub CommandButton1_Click()
Dim wrksht As Worksheet

Set wrksht = Worksheets("Evidence")
wrksht.Unprotect ("cpd")

wrksht.Cells(3, 4).Value = TextBox1.Value ''Name
wrksht.Cells(4, 4).Value = ListBox1.Value ''Command Level
wrksht.Cells(5, 4).Value = TextBox3.Value ''Job Role

wrksht.Protect ("cpd")
Unload Me

End Sub

This appears to work.

Many thanks,
D€$

 

[Andrzejek]

If a user can choose 1, 2, 3, or 4 values - I would use a list (or check boxes), but if a user can choose only one value, I would either use a combo box or option (radio) buttons

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PWD]

Hmm, radio buttons. I also need to ensure that there is something chosen as my proposed solution allows the used not to choose one of the items. I'll revisiit this tomorrow.

Many thanks,
D€$

 

[Andrzejek]

So if a user has to choose one and only one value out of 4, I would use option buttons with one already selected as default. You can do the same with drop-down combo set as a list so user cannot type in it, just select one value.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.