Vba code for userform to search by range of date

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,

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.

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

Private Sub CommandButton1_Click()
'This is the button labelled "In"
    
    'There will be a lot of repeated code, so to prevent that use a common subroutine
    Call InOutResults("In")
    
End Sub

Private Sub CommandButton2_Click()
'This is the button labelled "Out"
    
    'There will be a lot of repeated code, so to prevent that use a common subroutine
    Call InOutResults("Out")
    
End Sub

Private Sub InOutResults(ByVal strAction As String)
'This is the common subroutine
    
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim rngDates As Range
    Dim rngSum As Range
    Dim strFind As String
    Dim lOffset As Long
    Dim dStart As Double
    Dim dEnd As Double
    
    If Me.ComboBox1.ListIndex = -1 Then
        Me.ComboBox1.SetFocus
        MsgBox "Must select a material"
        Exit Sub
    End If
    strFind = Me.ComboBox1.Text
    
    dStart = CDbl(Me.Calendar1.Value)
    dEnd = CDbl(Me.Calendar2.Value)
    
    If dStart = 0 Or dEnd = 0 Then
        MsgBox "Must select both a Start Date and End Date"
        Exit Sub
    End If
    
    Set ws = Sheets("Stocks")
    Set rngFound = ws.Columns("A").Find(strFind, , xlValues, xlPart)
    If rngFound Is Nothing Then
        MsgBox "There is no data for material [" & strFind & "]", , "Invalid Material"
    Else
        Set rngDates = ws.Rows(5)
        Select Case LCase(strAction)
            Case "in":  lOffset = 1
            Case "out": lOffset = 2
        End Select
        Set rngSum = ws.Rows(rngFound.Row + lOffset)
        Me.TextBox1.Text = Evaluate("SumIf(" & rngDates.Address(External:=True) & ","">=" & dStart & """," & rngSum.Address(External:=True) & ")-SumIf(" & rngDates.Address(External:=True) & ","">" & dEnd & """," & rngSum.Address(External:=True) & ")")
        Set rngFound = rngDates.Find(Format(dEnd, ws.Range("C5").NumberFormat), , xlValues, xlWhole)
        If Not rngFound Is Nothing Then Me.TextBox3.Text = ws.Cells(rngSum.Row + 3 - lOffset, rngFound.Column).Text Else Me.TextBox3.Text = 0
    End If
    
    Set ws = Nothing
    Set rngFound = Nothing
    Set rngDates = Nothing
    Set rngSum = Nothing
    
End Sub

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

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?

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.

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)

Private Sub UserForm_Initialize()
    
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Sheets
        If InStr(1, ":Sheet1:Stocks:", ":" & Trim(ws.Name) & ":", vbTextCompare) = 0 Then Me.ComboBox1.AddItem Trim(ws.Name)
    Next ws
    
End Sub

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.

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)

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

Try changing these lines:

    dStart = CDbl(Me.Calendar1.Value)
    dEnd = CDbl(Me.Calendar2.Value)

To include the CDate() method:

    dStart = CDbl(CDate(Me.Calendar1.Value))
    dEnd = CDbl(CDate(Me.Calendar2.Value))

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:

Private Sub InOutResults(ByVal strAction As String)
'This is the common subroutine
    
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim rngDates As Range
    Dim rngSum As Range
    Dim strFind As String
    Dim lOffset As Long
    Dim dStart As Double
    Dim dEnd As Double
    
    If Me.ComboBox1.ListIndex = -1 Then
        Me.ComboBox1.SetFocus
        MsgBox "Must select a material"
        Exit Sub
    End If
    strFind = Me.ComboBox1.Text
    
    dStart = CDbl(CDate(Me.Calendar1.Value))
    dEnd = CDbl(CDate(Me.Calendar2.Value))
    
    If dStart = 0 Or dEnd = 0 Then
        MsgBox "Must select both a Start Date and End Date"
        Exit Sub
    End If
    
    Set ws = Sheets("Stocks")
    Set rngFound = ws.Columns("A").Find(strFind, , xlValues, xlPart)
    If rngFound Is Nothing Then
        MsgBox "There is no data for material [" & strFind & "]", , "Invalid Material"
    Else
        
        'check if end date is being recognized properly
        MsgBox Format(dEnd, ws.Range("C5").NumberFormat)
        
        Set rngDates = ws.Rows(5)
        Select Case LCase(strAction)
            Case "in":  lOffset = 1
            Case "out": lOffset = 2
        End Select
        Set rngSum = ws.Rows(rngFound.Row + lOffset)
        Me.TextBox1.Text = Evaluate("SumIf(" & rngDates.Address(External:=True) & ","">=" & dStart & """," & rngSum.Address(External:=True) & ")-SumIf(" & rngDates.Address(External:=True) & ","">" & dEnd & """," & rngSum.Address(External:=True) & ")")
        Set rngFound = rngDates.Find(Format(dEnd, ws.Range("C5").NumberFormat), , xlValues, xlWhole)
        
        'This if/then statement has been expanded for further debugging
        If Not rngFound Is Nothing Then
            'Check if column and row are where they should be
            MsgBox "Found the end date in column: " & Trim(Mid(Replace(rngFound.Address, "$", String(" ", 99)), 99, 99)) & Chr(10) & _
                   "Looking for Actual Stock values in row: " & rngSum.Row + 3 - lOffset
            Me.TextBox3.Text = ws.Cells(rngSum.Row + 3 - lOffset, rngFound.Column).Text
        Else
            'Check if the date simply isn't being found at all
            MsgBox "end date not found"
            Me.TextBox3.Text = 0
        End If
    End If
    
    Set ws = Nothing
    Set rngFound = Nothing
    Set rngDates = Nothing
    Set rngSum = Nothing
    
End Sub

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.

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

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.

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.