listbox default value 1

[max1565]

I have a listbox with several choices for the user to choose. Once an item is selected and the okay button is clicked, the selected item will be placed in a certain cell.

My question is:

Is there a way to set a default value for the listbox such that a 0 would be placed in the cell if no choice is made?

thanks

 

[SkipVought]

max1565,

Is 0 one of the values in the listbox, like the FIRST ONE?

Do you have logic that requires that the button be clicked before some event can occur?

Skip,

http://www.TheOfficeExperts.com

 

[VBAjedi]

You could just do a simple "If" test:

x = MyListBox.Value
If Len(x) = 0 Then x = "0"
Range("A1").Value = x

Hope that helps!


VBAjedi

 

[max1565]

skip

0 is the first item in the listbox. the listbox is part of a form that the user fills out. once they are finished with the form, they click okay, and the information is transfered to a worksheet.

thanks

 

[RobBroekhuis]

In your userform_activate (or _initialize) event handler, you can simply assign a default value to your listbox, e.g.

Listbox1="Second"

(this assumes that "Second" is one of the choices assigned to the listbox).


Rob

 

[SkipVought]

VBA,

When does this happen? He's got to have some event to run this. Maybe it's the above mentioned button, but what if he never hits the button.

Of course, it could be run when the form is exited.

Skip,

http://www.TheOfficeExperts.com

 

[kphu]

Hi,

I just tested it out and its pretty simple. Not sure if you know anything about VB but here goes.

Open the form with the field for the listbox your trying to accomplish this task for.

Right click the list box and click on properties. Click the "Event" tab and scroll down and click on "On Exit" select Code Builder.

Write the following code. Please note you'll have to edit some of it as I won't know the name of your fields.

If IsNull(Combo102.Value) Then
Combo121.Value = "0"
End If

Replace the Combo102 with the name of the list box name field.

Replace the Combo121 with the name of the field you wish to have the value 0 for.

Your script should look something like this below.



Private Sub Combo102_Exit(Cancel As Integer)
If IsNull(Combo102.Value) Then
Combo121.Value = "0"
End If
End Sub

Let me know if you have any problems.

Ken

 

[VBAjedi]

Skip,

You're right, my post would have been more clear if I had specified that. The code I suggested would be executed when the user clicks the "Okay" button Max mentioned. Presumably, we do not want to change the worksheet if the user does not Ok the change.

Anyway, there's a bunch of different approaches flying around in this thread, and Max hasn't expressed interest in using mine. It's a pretty simple approach, but if the default value is one of the listbox values, Rob's approach is better (as usual!).


VBAjedi

 

[TheBitDoctor]

I've always prefered to use the ControlSource, RowSource, and ListIndex values. The ControlSource is a cell on the worksheet that is updated whenever the user changes the list selection. The RowSource determines the contents of the list and the ListIndex value provides a pointer into the list. A -1 value indicates that the user never entered the list. You can set the default value by setting the list index to a constant value or you can do a 'match' on the source range to set the ListIndex so the current value is highlighted by default. Just watch out that the match function is 1 based and the ListIndex is 0 based. Setting the BoundColumn property to 0 puts the ListIndex value into the ControlSource cell so it can be used in an 'Offset' formula if needed. If you don't want to use worksheet space you can load the list from an array.

Ron