Excel vba UserForm Lists not registering default values?

R

r1024768

Hi

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

List.Clea
With Lis
.addItem "a
.addItem "b
etc
End Wit

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

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

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

However, it registers the default value of the list selection
whichever value I set those lists to is what the list starts off havin
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.Clea
ListBox2.Clea
ListBox3.Clea

With ListBox
.AddItem "a
.AddItem "b
.AddItem "c
End Wit

With ListBox
.AddItem "a
.AddItem "b
.AddItem "c
End Wit

With ListBox
.AddItem "a
.AddItem "b
.AddItem "c
End Wit

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

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

End Su
 
G

GS

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


--------------------
Click to expand...

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
 
G

GS

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
 
R

r1024768

Hi Garry,

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

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

'GS[_2_ said:
;1610884']After rereading your post it appears you are using a V
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.discussio
Click to expand...
 
G

GS

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.
Click to expand...

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
Click to expand...

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.
Click to expand...

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
 
R

r1024768

'GS[_2_ said:
;1610905']> I had thought that excel uses VBA by default...but now tha
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.-
Click to expand...

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-
Click to expand...

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.-
Click to expand...

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
Click to expand...

When I tested that short code it had the same issue as my original, so
figured there was no point in putting all the extra stuff in. The onl
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 variabl
for the listbox values - repeated 2 times for 2 separate labels t
display the description for the 2 list items.
The third list doesn't have a label box, but all 3 list's selection ar
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 th
lists), only 2 of the 3 ever seems to save to the cells. I wanted it t
save all 3, allowing for a default selection if the user doesn't want t
change anything.

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

GS

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

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
 
R

r1024768

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

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

'GS[_2_ said:
;1610921']> I'd just upload the file, but excel files aren't accepted a
one of -
the
file extensions-
Click to expand...

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.discussio
Click to expand...
 
G

GS

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

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
 
G

GS

It doesn't need a program. The program is actually just for the pro
The website is trying to download software to my machine before I can
download the file. Ain't gonna' happen!
Click to expand...

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
 
G

GS

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
 
R

r1024768

Thanks! Ran that a couple of times and it seems to be consistentl
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 basi
idea of programming languages in general, so I have no idea if there'
anything I did that caused the issue)

'GS[_2_ said:
;1611025']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.discussio
Click to expand...
 
G

GS

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

You're welcome! I appreciate the feedback...
So did the issue had something to do with the usage of ListBox.Value
=
x?
Click to expand...

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
 
R

r1024768

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

'GS[_2_ said:
;1611108']> Thanks! Ran that a couple of times and it seems to b
consistently-
working fine.-
Click to expand...

You're welcome! I appreciate the feedback...-
So did the issue had something to do with the usage of ListBox.Valu
Click to expand...
=
x?-
Click to expand...

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.discussio
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Stopping duplications been entered into a listbox 8
>= Greater than or Equal to Not working 2
Listbox Output to Individual Listboxes 0
Alert if value is already in listbox 2
Transferring items between multicolumn listboxes 2
Run time 9 error for some users but not everyone 10
userform listbox cannot get listbox.value to transfer back to main sub 1
User Input to Generate ListBox Range 0

Top