I have a list box that displays different regions on a user form. When I load the user form and click on an item in the list box, the selected item is briefly highlighted and then disappears? The items in the list box are linked to a cell and calculations are done based on the selection. The numbers are therefore briefly displayed and then disappear. I am not sure if a property value relating to the list box is causing this?
Thanks in advance
I had the Calculation Options set to : Automatic, except for data tables. When I changed this setting to Automatic, the list box selections worked.
I am still experiencing this problem especially when I open the workbook. Once I navigate to another sheet and return to the problem sheet, the problem disappears? Is there a problem with the following code?
Thanks in advance
Please Login or Register to view this content.
How is the listbox populated?
If posting code please use code tags, see here.
In the Row Source of the Properties section, I have PrincipalList which is a range name in the workbook.
I deleted PrincipalList from Row Source and put the following code in but have a problem with line 5?
Please Login or Register to view this content.
What is the 'problem'?
Also how is PrincipalList defined?
The List box is not populated. PrincipalList is a range name. Is it wrong to use row source?
So do you get an error message?
One thing for sure is you can't use RowSource and List, use one or the other - List is probably preferable.
Run-time error 1004: Invalid number of arguments is the error message?
That's because it should be Application.Transpose(Range(...)).
Even that might not work.
Can you upload an example workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
I have created a sample file that behaves similar to my real data. Sometimes when you open the file, Excel closes and other times it opens fine.
Thanks in advance.
Sorry I can't see, or replicate, any error.
The listboxes appear to populate OK.
The only weird things seems to be that when select something in the listbox on UserForm2 it doesn't get highlighted properly.
Thanks very much for your time & effort Norie. The fact that listbox1 on userform2 only highlights briefly is one of the problems I can't figure out? You have to navigate to another sheet and then return to that sheet in order for a selected item on the list box to remain highlighted and for the formulas to update & Excel crashes when you attempt to select an item on listbox1 from userform2? Sometimes Excel crashes when you open the file and other times it opens & works fine? I would really like to understand why this happens? I hope someone can figure this out?
Thanks in advance.
I couldn't get that to happen, but I can take a guess on why it's happening.
It could be because you are using the same ControlSource for the customer listbox on each form.
Using ControlSource is kinf a bad idea in the first place but to have 2 listboxes with the same range as the ControlSource, well that's kind of twice as bad.
You could easily write a few lines of code to put the selection from the listbox(es) on the worksheet.
This should be all you need for each listbox.
I notice you are also using ControlSource for the month listbox, you could use similar code to put it's selected value on the sheet.Please Login or Register to view this content.
If you do want to change to using code, and I would recommend you do, remember to clear out the ControlSource property of each listbox.
By the way, it might be a good idea to display the month and customer somewhere on the sheet with the table/chart.
Otherwise it's not clear, or it wasn't to me anyway, which month/customer the table and chart are for.
Once again, thanks very much for your assistance. I used code as per your suggestion and removed the ControlSource property and the problem has disappeared! The only other thing I would like to do is if I select a customer on DashboardOne and then go to DashBoardTwo, I would like the same customer I was viewing on DashboardOne to be highlighted rather than scrolling down to find that customer. I thought the following code would work but it doesn't?
Thanks in advance.Please Login or Register to view this content.
Where did you put the code?
I put the code in the list box as follows:
Please Login or Register to view this content.
Sorry, I presume you meant the other code. It is in a module:
Please Login or Register to view this content.
You're trying to get the value from UserForm1 after it's been unloaded.
Try this.
Please Login or Register to view this content.
Thanks Norie. Everything is working fine now. This is the code I used:
Please Login or Register to view this content.
I probably would have used ListIndex myself, but you used Value in your original code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks