Solved

Refresh two listboxes when a different listbox changes

Posted on 2015-01-19
7
108 Views
Last Modified: 2015-01-19
I have an Excel userform I use to capture itemized receipts by date. The date options are either the current date (the default) or any past date within the database range (1999-2015).

I have the date dropdowns working exactly the way I want them EXCEPT for the “refresh“. Here’s how it works:

Clicking “Choose date” to enter a receipt for a past date displays Year, Month and Date dropdowns, each of which defaults to the current date elements. (The Year dropdown list contains 1999-2015 and the Month list contains January through December.)

Clicking the month list, the only month that appears is January, for the same reason clicking the Date list shows only 1 through the current date – because you cannot enter a receipt for a future date.  (This works as intended and designed.)

When clicking the Year list and selecting another year, say 2009, the Month and Date defaults remain unchanged (which is okay but I’d prefer them to default to January and 1, respectively). The list, however, contains all months and all days for whichever month is selected (as intended and designed).

Here’s the issue: with the selected year of 2009, if I then select any month after the current month, say September, and any date after the current date, say the 25th, and then I change the Year to the current year (2015), the Month and Date defaults remain unchanged even though their contents change to January-December and 1 to the end date of whichever month is selected. But you don’t see this until you click either the Month or the Date lists. When either if these is select, the Month then changes to January (with nothing else in the list because that’s the only month available for entry in the current year) and the Date changes to 1 (and the list includes 1 to the current January date).

What I am trying to achieve is for the Month default to change to the current month and the Date to the current date as soon as the Year is changed back to the current year without having to click either the Month of Date lists.

So this is what I am trying to achieve:

1.      Whenever the Year is changed to a past year, the Month default should change to January and the Date to 1 (with both lists populated by all months and all dates 1 to the displayed month, as happens now).
2.      Whenever the Year is changed to the current year, the Month default should change to the current month and the Date to the current date.

I’ve been searching for how to “refresh“ listboxes (including Experts Exchange) but haven’t found any that do what I want. Isn’t there a way to just refresh one or two listboxes when another event occurs (without having to reload the userform) – just as you can calculate one or more Excel worksheet cells?

I have attached a stripped d0wn version of the userform and code so you can see the code I have now.
0
Comment
Question by:marrick13
  • 3
  • 2
7 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40558166
Listbox1.Refresh

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40558169
To best evaluate pls post your sample workbook !
gowflow
0
 

Author Comment

by:marrick13
ID: 40558185
Gowflow,

Thanks, but ".refresh" is not an option in VBA 2010 that i know if  - it's not included among the intellisense options and if I enter it I get a runtime error). I thought I attached the demo file before but I guess not. I did this time. Pls have a look at it.
ListBox-Demo.xlsm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 40558198
where in your code do oyu want to refresh the listbox ?

Actually I ran the workbook and ran the macro it does not populate anything in the listbox.
So whats the story ?

Pls get to the point as your post is very long and honestly did not read it.

So try to be brief and to the point.
gowflow
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40558206
just add this to your form code

Private Sub CmbYear_Change()
CmbYear_AfterUpdate
End Sub

I think this will solve the problem
0
 

Author Comment

by:marrick13
ID: 40558234
You're right - that solved it. Thanks much!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sum iF  based on a null cell 11 29
Excel Save As Status Box will not go away 6 19
Excel 2016 formulas 5 30
MS Excel IF AND OR statement 3 28
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now