Hi,
I have 2 linked listboxes where listbox2 is dynamic and gets populated from another sheet depending on what is selected in listbox1.
My problem is that listbox2 freezes when i have screen updating = false (atleast in excel 2003 which I am using now, not in excel 2007 which i used when i created the document)
Is there a way to avoid this?
I attached a small part of the document so my problem can be experienced. The listboxes are on sheet "Snabberäkning".
Would be very grateful for help on this.
Thanks a million!
forum.ozgrid.com/index.php?attachment/35668/
Listboxes won't update properly when screen updating is turned off
-
-
-
Re: Listboxes won't update properly when screen updating is turned off
A strategically placed 'DoEvents' seems to allow a refresh.
-
Re: Listboxes won't update properly when screen updating is turned off
Hey cytop,
Thanks for your post.
My description of the problem was porly made in my last post. The problem is that I can't highlight anything in listbox2 after a value has been chosen in listbox1. "DoEvents" for changes in listbox1 did therefore not eliminate that exact problem I'm afraid. My guess is that I have to change something in "Private Sub ListBox2_Change()". Any other ideas? -
Re: Listboxes won't update properly when screen updating is turned off
It's hard to test since the lookup sheet isn't in your workbook, but I would alter the code behind the listbox sheet to:
Code
Display MorePrivate blnSkipEvents As Boolean Public Sub ListBox1_Change() blnSkipEvents = True If ListBox1 = "Ost och mjölkprodukter" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [OstOMjölk].Value ElseIf ListBox1 = "Fett och olja" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [FettOOlja].Value ElseIf ListBox1 = "Bröd och spannmål" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [BrödOSpannmål].Value ElseIf ListBox1 = "Potatis" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [Potatis].Value ElseIf ListBox1 = "Grönsaker och baljväxter" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [GrönsakerOBaljväxter].Value ElseIf ListBox1 = "Frukt och bär" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [FruktOBär].Value ElseIf ListBox1 = "Kött, fisk, skaldjur, rom och ägg" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [KöttOFisk].Value ElseIf ListBox1 = "Övrigt" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [Övrigt].Value ElseIf ListBox1 = "Soppa, sås och matig sallad" Then ListBox2.Clear Sheets("Snabberäkning").ListBox2.List = [SoppaOsås].Value End If TextBox1 = vbNullString TextBox2 = vbNullString blnSkipEvents = False End Sub Private Sub ListBox2_Change() If blnSkipEvents Then Exit Sub UpDateTB2 End Sub Private Sub TextBox1_Change() If blnSkipEvents Then Exit Sub UpDateTB2 End Sub Private Sub UpDateTB2() Dim KCAL100 As Double Dim Livsmedel As String Dim rngFound As Range Dim wks As Worksheet If TextBox1 = vbNullString Then TextBox2 = vbNullString Exit Sub End If On Error Resume Next Livsmedel = ListBox2 Set wks = ActiveWorkbook.Sheets("Databas, livsmedel") Set rngFound = wks.Range("A1:A2041").Find(What:=Livsmedel, LookAt:=xlWhole) If Not rngFound Is Nothing Then KCAL = rngFound.Offset(0, 3).Value / 100 TextBox2 = Val(TextBox1) * KCAL End If End Sub
-
Re: Listboxes won't update properly when screen updating is turned off
... rory you are one amazing exceler. What do you do for a living?
I am the excel-king at my company right now, I would be a god if I was half as talented as you are. Thanks again -
-
Re: Listboxes won't update properly when screen updating is turned off
Hey rory,
Since I have you on the line. Do you have any idea why excel crashes every single time i touch the scroll wheel while having one of my listboxes selected?
Thanks again! -
Re: Listboxes won't update properly when screen updating is turned off
I'm an analyst.
I don't know why that would happen (other than because ActiveX controls on worksheets are buggy as hell) - is your copy of Windows/Excel fully patched, and are your mouse drivers the most current? -
Re: Listboxes won't update properly when screen updating is turned off
This is at work so I'm not quite sure what is installed or not. I will try it out at home instead.
I feel that I am violating the rules now by asking questions non-related to the topic, but do you know of any AMAZING litterature that would be of help while exploring the VBA jungle the way I am doing now? This trial-and-error methodology which I am using now has it's limits so to speak =) -
Re: Listboxes won't update properly when screen updating is turned off
Depends what you are after, and what level you are at. There's everything from VBA for Dummies (pretty good I'm told) through books like J Walk's Power Programming series or the Wrox Press books (not the 2003 version), up to the "Bible" (P.E.D).
-
Re: Listboxes won't update properly when screen updating is turned off
I know rory has already posted a solution but I started this, so I may as well post it.
Code
Display MoreOption Explicit Public Sub ListBox1_Change() Select Case ListBox1.Value Case "Ost och mjölkprodukter" Sheets("Snabberäkning").ListBox2.List = Range("OstOMjölk").Value Case "Fett och olja" Sheets("Snabberäkning").ListBox2.List = Range("FettOOlja").Value Case "Bröd och spannmål" Sheets("Snabberäkning").ListBox2.List = Range("BrödOSpannmål").Value Case "Potatis" Sheets("Snabberäkning").ListBox2.List = Range("Potatis").Value Case "Grönsaker och baljväxter" Sheets("Snabberäkning").ListBox2.List = Range("GrönsakerOBaljväxter").Value Case "Frukt och bär" Sheets("Snabberäkning").ListBox2.List = Range("FruktOBär").Value Case "Kött, fisk, skaldjur, rom och ägg" Sheets("Snabberäkning").ListBox2.List = Range("KöttOFisk").Value Case "Övrigt" Sheets("Snabberäkning").ListBox2.List = Range("Övrigt").Value Case "Soppa, sås och matig sallad" Sheets("Snabberäkning").ListBox2.List = Range("SoppaOsås").Value End Select TextBox1 = vbNullString TextBox2 = vbNullString End Sub
-
-
Re: Listboxes won't update properly when screen updating is turned off
Hey norie,
Thanks for your post. Your code gives me the following error:
Method 'Range' of 'object '_Worksheet' failed. -
Re: Listboxes won't update properly when screen updating is turned off
Where/when do you get the error?
Try this.Code
Display MoreOption Explicit Public Sub ListBox1_Change() Select Case ListBox1.Value Case "Ost och mjölkprodukter" Sheets("Snabberäkning").ListBox2.ListFillRange = "OstOMjölk" Case "Fett och olja" Sheets("Snabberäkning").ListBox2.ListFillRange = "FettOOlja" Case "Bröd och spannmål" Sheets("Snabberäkning").ListBox2.ListFillRange = "BrödOSpannmål" Case "Potatis" Sheets("Snabberäkning").ListBox2.ListFillRange = "Potatis" Case "Grönsaker och baljväxter" Sheets("Snabberäkning").ListBox2.ListFillRange = "GrönsakerOBaljväxter" Case "Frukt och bär" Sheets("Snabberäkning").ListBox2.ListFillRange = "FruktOBär" Case "Kött, fisk, skaldjur, rom och ägg" Sheets("Snabberäkning").ListBox2.ListFillRange = "KöttOFisk" Case "Övrigt" Sheets("Snabberäkning").ListBox2.ListFillRange = "Övrigt" Case "Soppa, sås och matig sallad" Sheets("Snabberäkning").ListBox2.ListFillRange = "SoppaOsås" End Select TextBox1 = vbNullString TextBox2 = vbNullString End Sub
-
Re: Listboxes won't update properly when screen updating is turned off
The error occured depending on which object in listbox1 I clicked on.
Your new code works, but the original problem remains. I have implemented rorys code so you don't have to keep trying for my sake. But you are welcome to do it if you still want to =)
Cheers! -
Re: Listboxes won't update properly when screen updating is turned off
What was the original problem?
I couldn't actually find one, I also couldn't see a need to turn off screenupdating.
I made some minor changes to the code mind you.
Works without error, but obviously no results because there's no data in the livsmedel worksheet I created.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!