VBA listbox auto scroll to selected item

I am looking for a code to auto scroll on a VBA listbox based on Current date value or next available date.

So example if I have on my worksheet with dates :
26/07/2017
27/07/2017
27/07/2017
28/07/2017
29/07/2017

And if the current system date is = 28/07/2017 then it my scroll to the first 28/07/2017 (first record in the listbox) without selecting the row. I can still scroll up for previous dates or go down for future dates record.
If in a example where the system date is Today (28/07/2017) and there is no 28/07/2017 in the excel sheet, then is must display the next available date ( maybe: 29/07/2017)

Below are the names on my UserForm

My ListBox name is: Listbox1
My Sheet name is: Sheet4
The Date column is: D (also know as: .Column(3) )
The Current Code I have on UserForm_Initialize:
Listbox1.List = Sheets("Sheet4").Range("A4:l" & [a65536].End(3).Row).Value

Example Screenshot
UQb0wYL.png

Regards
Shabeersa

Hi Andy

Thank you for your reply

I am new to VBA can do provide code for me to apply

Regards
SHabeersa

Hi Andy

I am putting the code in the Private Sub UserForm_Initialize()

Getting error " Method or Data member not found"
See screenshot
autoscroll error.png


Also why you say "Sheet1.Listbox1.List" when Sheet4 is the main sheet

Please assist

Shabeersa

Originally Posted by Andy Pope

Because I was testing on my own workbook. Just modify the sheet reference to your worksheet name.

Please find the excel sheet below. I changed to to Sheet4 which is the main data sheet

HTML Code: 

Open VBA developer mode to debug

Or login as Guest and Guest

User " Userform1" on the UserForm_Initialize

Regards
ShabeerSA

Originally Posted by Andy Pope

If you have an example workbook use the forum to attach it.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Kindly find attachment
Click here

Originally Posted by Andy Pope

I did not pickup from your image that the listbox was on a userform. I thought it was on the worksheet.

Please Login or Register  to view this content.

Insert the extra code in to your current code in the initialize event

HI Andy

Thank you, However this time no errors but its not auto scrolling to today's date..

See my video showing you
See Video

Ok Thank you Andy so much, its works