Excel vba UserForm Lists not registering default values?
 

Hi,

I have 3 lists on my UserForm, all initiated as follows:

List.Clear
With List
.addItem "a"
.addItem "b"
etc.
End With

At the very end, I have:
List1.Value = "a"
List2.Value = "b"
List3.Value = "c"

Msgbox List1.Value & List2.Value & List3.Value

Currently List2.Value returns a blank - I've also had it switch to List1 being blank between loads (no code touched, just saving, exporting to test the form, and exiting before going back in)
EDIT: After running it repeatedly without exiting, I find that it just seems to randomize between which ones show up at all even without exiting...

However, it registers the default value of the list selection - whichever value I set those lists to is what the list starts off having selected. It just doesn't seem to want to show the value in the Msgbox, and subsequently, anything I try to use that initial value in.

Any clue what's going on?

Exact Code:

Excel vba UserForm Lists not registering default values?
 

Hi,

I have 3 lists on my UserForm, all initiated as follows:

List.Clear
With List
.addItem "a"
.addItem "b"
etc.
End With

At the very end, I have:
List1.Value = "a"
List2.Value = "b"
List3.Value = "c"

Msgbox List1.Value & List2.Value & List3.Value

Currently List2.Value returns a blank - I've also had it switch to
List1 being blank between loads (no code touched, just saving,
exporting to test the form, and exiting before going back in)
EDIT: After running it repeatedly without exiting, I find that it
just seems to randomize between which ones show up at all even
without exiting...

However, it registers the default value of the list selection -
whichever value I set those lists to is what the list starts off
having selected. It just doesn't seem to want to show the value in
the Msgbox, and subsequently, anything I try to use that initial
value in.

Any clue what's going on?

Exact Code:

Code:
--------------------


Private Sub UserForm_Initialize()

ListBox1.Clear
ListBox2.Clear
ListBox3.Clear

With ListBox1
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With

With ListBox2
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With

With ListBox3
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With

ListBox1.Value = "b"
ListBox2.Value = "c"
ListBox3.Value = "a"

MsgBox ListBox1.Value & ListBox2.Value & ListBox3.Value

End Sub


--------------------

What are you calling a "userform list"? If this is a ListBox control
then 'Value' is the property that returns the value of its
*BoundColumn* when a list item is selected.

Using your example, I put 3 ListBox controls on a userform and selected
1 item from each (a,b,c), then clicked the userform and got this...

a,b,c

...where the controls were initialized as follows:

Private Sub UserForm_Click()
MsgBox Me.ListBox1.Value & "," _
& Me.ListBox2.Value & "," _
& Me.ListBox3.Value
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.List = Array("a", "b", "c")
Me.ListBox2.List = Array("a", "b", "c")
Me.ListBox3.List = Array("a", "b", "c")
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel vba UserForm Lists not registering default values?
 

After rereading your post it appears you are using a VB control, *NOT*
a VBA control. Please clarify what you're doing!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

I had thought that excel uses VBA by default...but now that I look back at it I'm seeing that "A" is not in the name - I'm not actually sure which I'm using anymore.

For clarification on what I'm trying to do, I have 3 listboxes in my userform, and 3 'description' labels for them - the labels are using vlookup to change based on the selections in the listboxes. Since I'm giving a default selection for all 3 lists on initialize, I wanted those descriptions to show in the labels. I got stuck after it seemed to randomly decide which of the 3 lists to not recognize the value for at init though...hence the rebuilding and short code I pasted.

Excel vba UserForm Lists not registering default values?
 

I had thought that excel uses VBA by default...but now that I look
back
at it I'm seeing that "A" is not in the name - I'm not actually sure
which I'm using anymore.

Excel use Visual Basic for Applications (VBA) as its macro language.
This is different than VB, which is a development platform for making
Windows applications and COM components for Windows applications.

For clarification on what I'm trying to do, I have 3 listboxes in my
userform, and 3 'description' labels for them - the labels are using
vlookup to change based on the selections in the listboxes. Since I'm
giving a default selection for all 3 lists on initialize, I wanted
those
descriptions to show in the labels. I got stuck after it seemed to
randomly decide which of the 3 lists to not recognize the value for
at

A ListBox control in VBA is what I exampled in my initial reply. By
default these are named "ListBox" with their instance number appended
to the name. (Hence ListBox1, ListBox2, ListBox3 in my example)

In Classic VB listbox controls follow the same naming, but minus "Box"
as it's commonly referred to as a "List" control. (Hence in VB my code
would be different because a List control doesn't support 'dumping'
values into its List like my example shows for the 3 ListBox controls
on UserForm1...

Private Sub Form_Click()
MsgBox Me.List1.List(List1.ListIndex) & "," _
& Me.List2.List(List2.ListIndex) & "," _
& Me.List3.List(List3.ListIndex)
End Sub

Private Sub Form_Load()
With Me.List1
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With
With Me.List2
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With
With Me.List3
.AddItem "a"
.AddItem "b"
.AddItem "c"
End With
End Sub


init though...hence the rebuilding and short code I pasted.

It would be better if you show ALL your code...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

When I tested that short code it had the same issue as my original, so I figured there was no point in putting all the extra stuff in. The only additions a

If Not IsError(Application.VLookup(Target, Sheets("Data").Range("I2:J7"), 2, False)) Then
Descript1.Caption = Application.VLookup(Target, Sheets("Data").Range("I2:J7"), 2, False)

Where Descript1 is a label within the form, and Target is the variable for the listbox values - repeated 2 times for 2 separate labels to display the description for the 2 list items.
The third list doesn't have a label box, but all 3 list's selection are saved to cells:

Range("E5") = List1.Value
Range("D6") = List2.Value
Range("D7") = List3.Value

If I hit accept as soon as I load the form (ie. without touching the lists), only 2 of the 3 ever seems to save to the cells. I wanted it to save all 3, allowing for a default selection if the user doesn't want to change anything.

I'd just upload the file, but excel files aren't accepted as one of the file extensions

Excel vba UserForm Lists not registering default values?
 

I'd just upload the file, but excel files aren't accepted as one of
the
file extensions

You can use a public upload website like "excelbanter.com" and post a
link in a reply so we can download your file.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

http://www.sendspace.com/file/f3w86p

This is what I had for testing the condensed code - as I said, I can't even seem to have it consistently screw up - this one can save all 3 value despite not loading 1 of them properly in the description for the userform itself.

Excel vba UserForm Lists not registering default values?
 

http://www.sendspace.com/file/f3w86p

Please use a website that doesn't download software to download your
file!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

It doesn't need a program. The program is actually just for the pro members anyway.

Excel vba UserForm Lists not registering default values?
 

It doesn't need a program. The program is actually just for the pro
members anyway

The website is trying to download software to my machine before I can
download the file. Ain't gonna' happen!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel vba UserForm Lists not registering default values?
 

It doesn't need a program. The program is actually just for the pro
members anyway

The website is trying to download software to my machine before I can
download the file. Ain't gonna' happen!

Ok, got the file this time!

Man.., what a buggy site. Surely you can use something better...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Excel vba UserForm Lists not registering default values?
 

Change the following procedures as shown...

Private Sub CommandButton1_Click()
Dim vDataOut(1 To 3, 1 To 1)
vDataOut(1, 1) = ListBox1.List(ListBox1.ListIndex)
vDataOut(2, 1) = ListBox2.List(ListBox2.ListIndex)
vDataOut(3, 1) = ListBox3.List(ListBox3.ListIndex)
Range("B16").Resize(UBound(vDataOut), 1) = vDataOut
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim sz$
With Me.ListBox1
.List = Array("a", "b", "c"): .ListIndex = 1
sz = sz & .List(.ListIndex) '//value
End With 'ListBox1

With Me.ListBox2
.List = Array("a", "b", "c"): .ListIndex = 2
sz = sz & .List(.ListIndex) '//value
End With 'ListBox2

With Me.ListBox3
.List = Array("a", "b", "c"): .ListIndex = 0
sz = sz & .List(.ListIndex) '//value
End With 'ListBox3
MsgBox sz

Call Descript(ListBox1.List(ListBox1.ListIndex), "Box 1")
Call Descript(ListBox2.List(ListBox2.ListIndex), "Box 2")
End Sub

Private Sub Descript(Selection As String, Target As String)
Dim sMsg$, sz$, rngLookup As Range
If Selection = "" Or Target = "" Then Exit Sub

Select Case Target
Case "Box 1"
sz = Application.VLookup(Selection, Sheets("Data").Range("A2:B4"),
2, False)
If sz = "" Then sMsg = "error reading value for 1": GoTo ErrExit
Descript1.Caption = sz

Case "Box 2"
sz = Application.VLookup(Selection, Sheets("Data").Range("D2:E4"),
2, False)
If sz = "" Then sMsg = "error reading value for 2": GoTo ErrExit
Descript2.Caption = sz
End Select 'Case Target
GoTo NormalExit

ErrExit:
MsgBox sMsg

NormalExit:
Set rngLookup = Nothing
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks! Ran that a couple of times and it seems to be consistently working fine.

So did the issue had something to do with the usage of ListBox.Value = x?
(I basically frankensteined the code together using google and a basic idea of programming languages in general, so I have no idea if there's anything I did that caused the issue)

Excel vba UserForm Lists not registering default values?
 

Thanks! Ran that a couple of times and it seems to be consistently
working fine.

You're welcome! I appreciate the feedback...

So did the issue had something to do with the usage of ListBox.Value
=
x?

Yes, though I'm not sure why! (It didn't work when I tried it!)

<FWIW
I always code getting values in VBA the same as a VB6 list control
since I like my code to be as portable between the two programming
languages as possible. Your explanation for collecting the code using
google explains why I was initially confused as to which genre control
you were using.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ha, sorry about the confusion. I started going into programming but I switched major halfway so I never actually learned VB. I know enough to reverse engineer/read coding, but that's about it. I have no idea whether I copied from vba or vb if it doesn't give me an error!