Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controll two dates from text box (2000 sr 1)

    admit i have cbox1 and cbox2
    in all i can insert a date (format DD/MM/YYYY))
    In sheet have column N3:..... and O3.... have a list of date always in format DD/MM/YYYY
    similar:
    column N column O
    01/01/2006 23/11/2006
    01/01/2006 23/11/2006
    01/01/2006 23/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006
    01/01/2006 22/11/2006


    how to controll if the date in cbox1 and cbox2 are in range of column N and O of sheet

    Example:

    cbox1=02/02/2006 cbox2=20/11/2006 in this case appear msgbox, because this range of date in in internal of one range in column N and O of sheet

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll two dates from text box (2000 sr 1)

    Please explain more fully what you mean. Should the date range indicated by cbox1 and cbox2 fall completely inside ALL date ranges in the worksheet, or inside at least one date range in the worksheet? Or do you mean something else?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll two dates from text box (2000 sr 1)

    in effect i import a data from other application into sheet and when i import i insert in a form init and finish date for example 01/01/2006 to 28/11/2006 and store this two date in sheet column N and O

    Now if a user insert in cbox2 (init date) 02/02/2006 and in cbox2 (finish date) 20/10/2006 no permit the user to re-importa same data with this range of data...

    in this case showw msgbox "attention 02/02/2006 and 20/10/2006 are already import" ecc...

    hope understand me:-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll two dates from text box (2000 sr 1)

    Please try to explain exactly what you mean. Do you want to check whether the exact date range already occurs, or whether the date range falls within all of the ranges already entered, or whether the date range falls entirely within one of the ranges already entered, or whether the date range overlaps one of the ranges already entered. If you don't tell us exactly what you want, it is impossible to help you.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll two dates from text box (2000 sr 1)

    or whether the date range falls entirely within one of the ranges already entered...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll two dates from text box (2000 sr 1)

    Does this do what you want?

    Dim d1 As Date
    Dim d2 As Date
    Dim r As Long
    Dim n As Long
    d1 = CDate(Me.cbox1)
    d2 = CDate(Me.cbox2)
    n = Range("N65536").End(xlUp).Row
    For r = 3 To n
    If d1 >= Range("N" & r) And d2 <= Range("O" & r) Then
    MsgBox "Date range already used.", vbExclamation
    Exit For
    End If
    Next r

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll two dates from text box (2000 sr 1)

    perfect! tks.

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll two dates from text box (2000 sr 1)

    HUMMM...

    this is s part of my code and i want to chek:


    if date not are in range goto the next line else exit sub... how to change?

    peraph insted to call the macro i can insert code in the middle of code...
    .....
    If Not Me.SOCIETA = "H7469" And Not Me.SOCIETA = "BNLGF" And Not Me.SOCIETA = "BNLIM" Then
    MsgBox ("CODICE STATO NON AMMESSO"), vbInformation
    Me.SOCIETA = ""
    Me.SOCIETA.SetFocus
    MsgBox ("VALORI AMMESSI: H7469 - BNLGF - BNLIM"), vbInformation
    Exit Sub
    End If

    'CONTROLLO DATE
    Call TEST_DATE
    'CONTROLLO DATE


    TIP = Me.TIPO.Text
    LAV = Me.STATO.Text
    PRO = Me.PROD.Text
    SOC = Me.SOCIETA.Text
    DATA_INIZIO = Me.DATA_1
    DATA_FINE = Me.DATA_2
    .....


    here my new code:

    Sub TEST_DATE()

    Dim D1 As Date
    Dim D2 As Date
    Dim R As Long
    Dim N As Long
    D1 = CDate(Me.DATA_1)
    D2 = CDate(Me.DATA_2)
    N = Sheets("H7469_STORICO").Range("N65536").End(xlUp). Row
    For R = 3 To N
    If D1 >= Sheets("H7469_STORICO").Range("N" & R) And D2 <= Sheets("H7469_STORICO").Range("O" & R) Then
    MsgBox ("RANGE DI DATE GIA' RICHIESTO"), vbExclamation
    Exit For
    End If
    Next R
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll two dates from text box (2000 sr 1)

    You can change TEST_DATE to a function that returns True if the dates are within the range, False if not:

    Function TEST_DATE() As Boolean
    Dim D1 As Date
    Dim D2 As Date
    Dim R As Long
    Dim N As Long
    D1 = CDate(Me.DATA_1)
    D2 = CDate(Me.DATA_2)
    N = Sheets("H7469_STORICO").Range("N65536").End(xlUp). Row
    For R = 3 To N
    If D1 >= Sheets("H7469_STORICO").Range("N" & R) And _
    D2 <= Sheets("H7469_STORICO").Range("O" & R) Then
    MsgBox "RANGE DI DATE GIA' RICHIESTO", vbExclamation
    TEST_DATE = True
    Exit For
    End If
    Next R
    End Function

    In your existing code, use the function like this:

    'CONTROLLO DATE
    If TEST_DATE = True Then Exit Sub
    'CONTROLLO DATE

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll two dates from text box (2000 sr 1)

    NOW I LOVE THE FUNCTION STATEMENT...!!!!!!!!!
    Tks a s usual for patience.
    Sal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •