Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Enter dates between specified dates & set Print area in Excel

    Anyone ,

    Please help me on Excel 2003 VBA.......

    I have two dates in Excel sheet, suppose in cell B1 contains "15-1-2013" and C1 contains "25-10-2013". I have to enter Dates from A1 to A20 cells and these dates must be in between the dates in B1 and C1 cells. Every time I make date entries in the range (A1:A20), and if it is above or below B1 and C1 then it must give a message / lock the sheet / change the color of the last entered date on this range.

    And also is it possible to set the print area on excel VBA ? Suppose I want to print from cells(3,3) to cells(9,6)

    Please help me to solve this...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    1) Use data validation. Select A1:A20. choose the data-validation, set it to date. Minimum =$B$1, maximum =$C$1

    If you want to highlight, you can conditional formatting. Select A1:A20 and enter the formula for conditional formatting of :
    =OR(A3<$B$1,A3>$C$1)
    format color as desired

    2) You can set the printarea with a line like (using the cells nomenclature you suggest)
    ActiveSheet.PageSetup.PrintArea = Range(Cells(3, 3), Cells(9, 6)).Address

    or even something like:
    ActiveSheet.PageSetup.PrintArea = "$C$3:$F$9"

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    anilkumar (2014-01-07)

  4. #3
    New Lounger
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Mr. Steve for your fast reply...

    My second problem about print area is solved........

    About 1st problem - I want it also on Excel VBA codes

    I don't know how to access last entered data from cell. Suppose I entered A1,A2 dates and entering a date on A3 not in between the dates then
    immediately after entering it should give message / change color of last date entered cell. I know it can done through For..Next loop. I think if there is any
    option to find the last entered cell data / cell address then it can check without For...Next loop. And also how to search on particular range (A1:A20) with
    simple codes on VBA...

    Once again thanks Mr. Steve..............for help......

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is some example code.
    BE AWARE: Running this type of code will "disable" UNDOing in that worksheet, even for changes outside the ones being examined.

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rCell As Range
      Dim rInt As Range
      Dim rTest As Range
      Dim dMin As Double
      Dim dMax As Double
      
      'change as desire
      dMin = Range("B1")
      dMax = Range("C1")
      Set rTest = Range("A1:A20")
      
      On Error GoTo ErrHandler
      Set rInt = Intersect(Target, rTest)
      If Not rInt Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rInt
          Select Case rCell.Value
            Case Is < dMin 'Value is < min
              rCell.Interior.Color = vbRed
              MsgBox rCell.Address & ": " & _
                Format(rCell, "mmm d, yyyy") & " is less than " & _
                Format(dMin, "mmm d, yyyy")
            Case Is > dMax 'value is > Max
              rCell.Interior.Color = vbRed
              MsgBox rCell.Address & ": " & _
                Format(rCell, "mmm d, yyyy") & " is greater than " & _
                Format(dMax, "mmm d, yyyy")
            Case Else 'Value is OK reset color
              rCell.Interior.Pattern = xlNone
          End Select
        Next
      End If
    
    'cleanup
    ExitHandler:
        Application.EnableEvents = True
        Exit Sub
    
    'in case of error    
    ErrHandler:
        MsgBox Err.Description
        Resume ExitHandler
    End Sub
    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    anilkumar (2014-01-07)

  7. #5
    New Lounger
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Once again thanks to Mr.Steve for fast reply...

    My problem solved..............

    I have one more question...What is the single line VBA code for access last entered data......

    Thank you............Mr. Steve.....

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You need to run it from the Worksheet change object. The variable named Target is a range that has been changed. It can be a single cell, or if you copy into the worksheet it could be a range of cells. The following will give a message box with the addresses of the cells that have been changed.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      MsgBox Target.Address & " was Changed"
    End Sub
    Note when using the Worksheet_Change procedure: You will want to prevent any code in this procedure from calling itself (changes in code to the sheet will also call the procedure which can put you into an infinite loop). Add the line:
    Application.EnableEvents = False
    before having the code make any changes and after the procedure has ended, re-enable events
    Application.EnableEvents = True
    [The code I entered in the previous posts has this. Disabling events will persist unless reenabled until Excel quits and restarts, so it is good to have it in a error procedure to ensure that it gets re-enabled even after the code "crashes"]

    Steve

  9. #7
    New Lounger
    Join Date
    Jan 2014
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for the valuable information Mr Steve............THANKS............

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are very welcome. Post back if you need any more details on what the code is doing and why lines are in there.

    Steve

Posting Permissions

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