Excel Listbox displays default value but does not retain in within Sub (unless you click on selection)

M

Mike SRQ

New Member
Joined
Aug 15, 2019
Messages
1
[COLOR=#BBC0C4 !important][COLOR=#6A737C !important]0[/COLOR]
<button class="js-vote-down-btn grid--cell s-btn s-btn__unset c-pointer" title="This question does not show any research effort; it is unclear or not useful" aria-pressed="false" aria-label="down vote" data-selected-classes="fc-theme-primary" style="margin: 2px; box-sizing: inherit; font: inherit; position: relative; padding: 0px; border-width: initial; border-style: none; border-color: initial; border-radius: 3px; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; outline: none; box-shadow: none;"><svg aria-hidden="true" class="svg-icon m0 iconArrowDownLg" width="36" height="36" viewBox="0 0 36 36"></svg>
</button><button class="js-favorite-btn s-btn s-btn__unset c-pointer py8" aria-pressed="false" aria-label="favorite" data-selected-classes="fc-yellow-600" title="Click to mark as favorite question (click again to undo)" style="margin: 0px; box-sizing: inherit; font: inherit; padding: 0px; position: relative; border-width: initial; border-style: none; border-color: initial; border-radius: 3px; background-image: none; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; outline: none; box-shadow: none;"><svg aria-hidden="true" class="svg-icon iconStar" width="18" height="18" viewBox="0 0 18 18"></svg>

</button>[/COLOR]

I have an issue that is stumping me. I have a large VBA app and I have isolated the offending piece of code here. Basically I have a UserForm with a few ListBoxes (I used 8 ListBoxes here to make the point). Based on some prior user input, each of the listboxes will present some default value. I found some curious behaviour I can duplicate here but can't explain. Only some of the listBoxes seem retain the default value that I set (even though the userform displays the set values) Here is the sequence of steps to try to prove my issue:
Open UserForm4
Run
In Input Box Asking "Pre-Enter some Default Values?", Enter "Y"
You will see that I set the ListBox.Value for each ListBox to a certain value as displayed by the highlighted items in the ListBoxes. If I choose not to chance the default values (by not clicking any item in the listbox),some of the ListBox.Value(s) do not "stick".
This is made obvious when you Click on the Command button (Show ListBoxValues) that displays the ListBox,Values, you will see all the ListBox.Values do not seem to have got their values set as seen from the MsgBox Output. Specifically ListBox3.Value, ListBox6.Value and ListBox8.Value seem to be "" or NULL even though they were set to specific values exactly like the other ListBox.Values were.
Any Ideas why?
I have the code in the .frm file and the .frx file so you can duplicate it.
https://drive.google.com/file/d/1nu9kSSpWCHk5m2-QIthJjNxS_DDEa1kt/view?usp=sharing
https://drive.google.com/file/d/1J-LWNe8IiLe1JJRC-of5bSX3Oer8eP7Y/view?usp=sharing
Here is the code..pretty simple:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
Private Sub UserForm_Initialize()
myValue = inputbox("Pre-Enter some Default Values?(Y/N)")

With ListBox1
.AddItem "Listbox1-A"
.AddItem "Listbox1-B"
.AddItem "Listbox1-C"
End With

With ListBox2
.AddItem "Listbox2-A"
.AddItem "Listbox2-B"
.AddItem "Listbox2-C"
End With

With ListBox3
.AddItem "Listbox3-A"
.AddItem "Listbox3-B"
.AddItem "Listbox3-C"
End With

With ListBox4
.AddItem "Listbox4-A"
.AddItem "Listbox4-B"
.AddItem "Listbox4-C"
End With

With ListBox5
.AddItem "Listbox5-A"
.AddItem "Listbox5-B"
.AddItem "Listbox5-C"
End With

With ListBox6
.AddItem "Listbox6-A"
.AddItem "Listbox6-B"
.AddItem "Listbox6-C"
End With

With ListBox7
.AddItem "Listbox7-A"
.AddItem "Listbox7-B"
.AddItem "Listbox7-C"
End With
With ListBox8
.AddItem "Listbox8-A"
.AddItem "Listbox8-B"
.AddItem "Listbox8-C"
End With

If UCase(myValue) = "Y" Then
ListBox1.Value = "Listbox1-B"
ListBox2.Value = "Listbox2-B"
ListBox3.Value = "Listbox3-B"
ListBox4.Value = "Listbox4-B"
ListBox5.Value = "Listbox5-B"
ListBox6.Value = "Listbox6-B"
ListBox7.Value = "Listbox7-B"
ListBox8.Value = "Listbox8-B"
End If
End Sub


Private Sub CommandButton1_Click()
MsgBox UserForm4.ListBox1.Value & vbCrLf & _
UserForm4.ListBox2.Value & vbCrLf & _
UserForm4.ListBox3.Value & vbCrLf & _
UserForm4.ListBox4.Value & vbCrLf & _
UserForm4.ListBox5.Value & vbCrLf & _
UserForm4.ListBox6.Value & vbCrLf & _
UserForm4.ListBox7.Value & vbCrLf & _
UserForm4.ListBox8.Value & vbCrLf

End
End Sub</code>




****** id="cke_pastebin" style="position: absolute; top: 579px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
Private Sub UserForm_Initialize()
myValue = inputbox("Pre-Enter some Default Values?(Y/N)")

With ListBox1
.AddItem "Listbox1-A"
.AddItem "Listbox1-B"
.AddItem "Listbox1-C"
End With

With ListBox2
.AddItem "Listbox2-A"
.AddItem "Listbox2-B"
.AddItem "Listbox2-C"
End With

With ListBox3
.AddItem "Listbox3-A"
.AddItem "Listbox3-B"
.AddItem "Listbox3-C"
End With

With ListBox4
.AddItem "Listbox4-A"
.AddItem "Listbox4-B"
.AddItem "Listbox4-C"
End With

With ListBox5
.AddItem "Listbox5-A"
.AddItem "Listbox5-B"
.AddItem "Listbox5-C"
End With

With ListBox6
.AddItem "Listbox6-A"
.AddItem "Listbox6-B"
.AddItem "Listbox6-C"
End With

With ListBox7
.AddItem "Listbox7-A"
.AddItem "Listbox7-B"
.AddItem "Listbox7-C"
End With
With ListBox8
.AddItem "Listbox8-A"
.AddItem "Listbox8-B"
.AddItem "Listbox8-C"
End With

If UCase(myValue) = "Y" Then
ListBox1.Value = "Listbox1-B"
ListBox2.Value = "Listbox2-B"
ListBox3.Value = "Listbox3-B"
ListBox4.Value = "Listbox4-B"
ListBox5.Value = "Listbox5-B"
ListBox6.Value = "Listbox6-B"
ListBox7.Value = "Listbox7-B"
ListBox8.Value = "Listbox8-B"
End If
End Sub


Private Sub CommandButton1_Click()
MsgBox UserForm4.ListBox1.Value & vbCrLf & _
UserForm4.ListBox2.Value & vbCrLf & _
UserForm4.ListBox3.Value & vbCrLf & _
UserForm4.ListBox4.Value & vbCrLf & _
UserForm4.ListBox5.Value & vbCrLf & _
UserForm4.ListBox6.Value & vbCrLf & _
UserForm4.ListBox7.Value & vbCrLf & _
UserForm4.ListBox8.Value & vbCrLf

End
End Sub</code>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Curious indeed! I copied your code and had different missing values. Sometimes boxes 2, 4, and 6 were missing. That changed every once in a while, though. 2 would show up, but 7 would be gone.

This functionality was the same even if using ListBox1.ListIndex = 1 instead of setting its Value.

I tried adding DoEvents after each Value setting, but no difference.

Playing with the code, I found this works:
Code: 
If UCase(myValue) = "Y" Then
    ListBox1.Value = "Listbox1-B"
    ListBox2.Value = "Listbox2-B"
    ListBox3.Value = "Listbox3-B"
    ListBox4.Value = "Listbox4-B"
    ListBox5.Value = "Listbox5-B"
    ListBox6.Value = "Listbox6-B"
    ListBox7.Value = "Listbox7-B"
    ListBox8.Value = "Listbox8-B"
    ListBox1.SetFocus
    ListBox2.SetFocus
    ListBox3.SetFocus
    ListBox4.SetFocus
    ListBox5.SetFocus
    ListBox6.SetFocus
    ListBox7.SetFocus
    ListBox8.SetFocus
    CommandButton1.SetFocus
End If

For some reason, setting the focus locks in the value. At the end, I set the focus back to the CommandButton1, but whatever is TabIndex = 0 can be used.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Userform sum of selection of multiple listboxes
Replies
2
Views
745
dailan
D
D
  • Question Question
Referencing Public Constants in Cell Formulas
Replies
1
Views
979
sijpie
sijpie
T
  • Question Question
ComboBox to filter Listbox Data
Replies
8
Views
12K
Tommy Murphy
T
D
  • Question Question
Userform combobox populates ListBox errors on clearing control
2
Replies
12
Views
2K
devofish
D
I
  • Question Question
Compile Error : Expected Function or variable while replicating grouping format
Replies
4
Views
1K
itsme2216
I

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top