Vba code for userform to search by range of date

[bazofio]

Hi guys

Before my question i just want to say that this forum and the people here are amazing ;-)

Ok, now to the point...Can anyone give me a hand with a code to this sample file?
I have one file of this every month. In this sample i just have 2 materials, and this controls the stocks. I have design a userform with what i want (if its possible), and it consists in a combobox where i can select the material (in this sample there are LA-48, and Sact, but there are plenty more...maybe 20 but i hope that with your help with a code i can just add the others). Then i have two calendars where i introduce the start date and in the other the end date to use as a range to give me the sum of the inīs or the outīs, if i press one or another of this buttons (In / Out).
The result should appear in the textbox below and in the last textbox should appear the actual stock at the end date selected on the calendar (End). If its blank, or 0,or negative it should appear that way in the textbox.
Thanks in advance

Sample file.xls

[bazofio]

Anyone please

[tigeravatar]

bazofio,

When I try to launch the form, I get error: "Could not load some objects because they are not available on this machine."
Screen shot attached.
As a result, I can't tell you what's wrong with your code. I know that there is no Calendar control in Excel 2007+ so maybe that is the issue.

Then when I took a look to see what code was in the userform, I found that there wasn't any code, so I'm a bit confused on what you need help with.

[bazofio]

Thanks tiger for tour time :-)
I didnt actually make any code. What i have done was just the userform and it was in excel 2000.

[tigeravatar]

Alright, well I think what you're looking for is something like this:

Please Login or Register  to view this content.

EDIT: I feel it is important to note that the above code is untested because of the reasons I mentioned above

[bazofio]

WoW
Ok i just put this code and theres nothing to select in the combobox. I think im doing something wrong here but cant get there.

[tigeravatar]

My code doesn't populate the combobox. How do you want it populated? From the 'Stocks' sheet or from the sheet names? Or do you have a range of cells somewhere that lists all of the materials that could be selected in the combobox?

[bazofio]

The combobox should be populated with the sheet names, in that example there are two of them (LA-48 and Sact), but i have maybe 20 or so. When i have all of them they will be also in the stocks sheet in the rows below the others that are there now from where i hope i get all the inīs and outīs for each material selected in the combobox.
And i forgot to create an Exit button in the userform, damn me lol.

[tigeravatar]

bazofio,

Add this userform_initialize code to the userform. Make sure to update the red part with the sheet names that should not be included in the drop-down. All sheet names should be separated by a colon ":" in that part as shown (including starting and ending with a colon)

Please Login or Register  to view this content.

[bazofio]

Ok now they show up :-)
But when trying to get the results i get a error message: Compile error Method or data member not found.
This: Private Sub InOutResults(ByVal strAction As String) is yellow and this: (Me.Calendar1.Value) is grey.

[tigeravatar]

Are you using calendar controls? Are they called Calendar1, Calendar2, etc? They didn't show up for me so I don't know what they're called or what kind of control you're actually using, so that was just my best guess.

[bazofio]

That was it, i correct and its now working ;-)
What is missing is the actual stock at the selected end date.

[tigeravatar]

The code I provided was based on the example file you provided. I used textboxes to provide myself with start and end date inputs and ran a test and it got back the actual stock at the selected end date properly. Is the setup different on your actual workbook? If it is, are you certain that there is a stock price for that end date? in the sample file, there are only Actual Stock values through 13-May-2013.


EDIT: lol, I apparently really like the word provide(d)

[bazofio]

Lol its a "nice" word hehehe
Ok i have try with various end dates (form day 2 to day 3 for example) and it didnt show up any Actual stock at the end date. Im using the same workbook that i uploaded, i have another one with all materials but im just testing this to adapt after when its working.

Edit: its gives me always zero

[tigeravatar]

Try changing these lines:

Please Login or Register  to view this content.

To include the CDate() method:

Please Login or Register  to view this content.

[bazofio]

That didnt work tiger, its always showing zero.

[tigeravatar]

Here's debug friendly version of the code. I have put in some messageboxes to help you narrow down where the disconnect is. Use the information that the messageboxes provide along with the new comments in the code to find the problem:

Please Login or Register  to view this content.

[bazofio]

I try that code but its worse. Now nothing appears, no results. Neither the inīs and outīs and neither the actual stock at end date. Not even any msgbox.

[tigeravatar]

did you change the calendar1, calendar2 to what they're supposed to be?
And no matter what, it should give you a msgbox, there's one for every possibility, unless the code errors out, in which case you should get an error message.

[bazofio]

Yes i have chenge the calendar1 and 2 to DTPicker1 and 2. It was working ok the inīs and outīs with the other code. With this one i dont get anything, not even an error or a msgbox. Weird


Edit: ok i try again just copy paste like the first time and dont know why it working now. It displays a msgbox with the end date selected and then another saying: end date not found

[bazofio]

This is getting weird. Now i didnt change anything and when trying again its not working. It says: Runtime error 13 mismatch. And it points here:
Me.TextBox1.Text = Evaluate("SumIf(" & rngDates.Address(External:=True) & ","">=" & dStart & """," & rngSum.Address(External:=True) & ")-SumIf(" & rngDates.Address(External:=True) & ","">" & dEnd & """," & rngSum.Address(External:=True) & ")")

[tigeravatar]

Based on the "end date not found" post, it looks like the date you're entering isn't being found within row 5 of sheet "Stocks".

Also, I'm not sure what would cause the error you got, if nothing really changed.

[bazofio]

Now im home from work and here i have excel 2010 which dont have this type of calendar that im using in this userform (excel 2000). That is why it gives you the couldnt find object error message.


Edit: ok i have register the datapicker in excel 2010 from excel 2000 and put in the userform. Now everything is working ok with your last code, with the msgbox that appear is: end date not found.

I have try changing some things in your code but i cant figure it out whats the problem, im stuck in there because i cant find why it can not find the end date when it uses it to calculate correctly the inīs and outīs.....just dont find it to display the stock at end date.

[bazofio]

Any ideas to solve this?