Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 Last edited by r1024768 : April 2nd 13 at 11:58 PM Reason: Added code + update |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. Quote:
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
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 Last edited by r1024768 : April 4th 13 at 06:18 AM |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. Quote:
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
|
|||
|
|||
It doesn't need a program. The program is actually just for the pro members anyway.
Quote:
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
|
|||
|
|||
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) Quote:
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
|
|||
|
|||
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!
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Registering parameter/function help for Excel User Defined Functio | Excel Programming | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Create Outlook Distribution lists (default txt file) with excel | Excel Programming | |||
registering the cell values of excel combo box associated values | Excel Programming |