Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Steve,

    by defeniton all cells are already locked.
    What i need is the corrected code for :
    Private Sub cmd_closingdate_notice_Click()
    Dim loc%: loc = lastrow
    Sheets("Data").Unprotect (pw)
    Dim Issuedate, idate: Issuedate = Val(Range("c" & loc).Value)
    Dim closedate, cldate: Range("l1").Value = DTPicker2.Value
    closedate = Val(DTPicker2.Value)
    Dim Windowdate: Windowdate = Val(Date)

    If Issuedate > closedate Or Windowdate < closedate Then
    MsgBox ("Erratic dates !" & vbLf & vbLf & "You are trying to close out a N

  2. #2
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    date window (excel2000)

    Hi,

    I am looking for a way to block input/change on a cell and this cel is in connetion with an issue date
    Editing is only allowed within the time-window issuedate and closingdate

    Therefor you cannot edit or close the cell before its issuedate
    The closing date cannot be later than today (today included)
    You cannot close/edit into the past before the issing date or into the future later as today
    thus change is only possile between issuedate and today(included)

    today i want to get with vba date
    issue date has been put in a column/consequent row say Dx
    closing day is a value derived from DTPickervalue

    I have been struggling with it some time now and get only partial solutions.
    Is there anyone with a foolproof solution?

    Thanks,

    Jan

  3. #3
    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

    Re: date window (excel2000)

    I am not exaclty sure of your setup and desire, so I will be general. If you have specific questions, please post back
    lock the cell of interest and Protect the worksheet
    when you open the workbook
    add a workbook open macro to:
    unlock the cell of interest if the date is in a particular range
    or lock it if it is not in the range

    Steve

  4. #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

    Re: date window (excel2000)

    What goes wrong with the code?
    Do you get runtime error or does it not do what you want it to do?

    If it does not does not do, what you want, what do you want it to do?

    I am confused about how it relates to your original question about blocking input in a cell. This seems to be the code for a button on form and has nothing to do with allowing or preventing the user from editing a cell.

    Could you be a little more specific about your goals?

    Steve

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Steve,
    indeed this is the code connected to a formbutton. If all conditions are met, the indication "Closed" is put in the cell F of that particular row.This is the trigger word for accepting further input on the userform or better it will determin if further input will be written to the sheet or not.

    This code just steers the button on which you say u want to close the inut on this line for ever. No more changes allowed as from this moment. The user isn't allowed to change the data already present as from this moment onwards.

    This means that the date an time of the "locking" is mentioned in cell G of that row = closing date = today or now = date
    The moment of time of the first input has been put in cell C of the row = issue date
    From the issue date there could be changes until a closing date has been appointed. After the putting down of the closing down no more changes ever are allowed (only by the one who know to unlock the sheets are ok).
    By logic the closing date cannot be before the issue date
    but in in not allowed to be later than the present day as closing into the future is not allowed.
    This means that changes (not locking or accepting data or not putting down the keyword CLOSED) is only allowed in a time window of creation date (issue date) and today (window date).
    dtpicker.value has the closing date and connected to it the Keyword CLOSED
    When activating this piece of code this closing date has to meet the conditions of the allowing time window if not the message is being shown. No keyword is applied and further changes will be allowed to the line as the OK buton checks on the keyword CLOSED in cell
    F of the row in question.

    I hope this makes all sense to you.


    Jan

  6. #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

    Re: date window (excel2000)

    I am still confused about your question/ questions and what you need help with.

    Originally you had asked about:
    <hr>I am looking for a way to block input/change on a cell and this cel is in connetion with an issue date
    Editing is only allowed within the time-window issuedate and closingdate<hr>

    You are accomplishing that already (it seems) via protection and forcing all entry via forms, so I am confused as to the problem. If the user wants to change a cell, your forms macro just needs to check if the cell is "locked" (via cell G?) and if it is post a message and not change it. If it is not locked, don't change it. With the protection they can not edit it except via the macro route.

    My original answer assumed, that you would have the worksheet protected and the cell properties would be "locked/unlocked" based on the criteria, but the macro route for entry allows much more control, but can require more coding.

    On the "simplest level" you could have a routine called from the "dbl-click event"
    If the user dbl-clicks a cell the appropriate column or columns of that row are checked for the ability to edit
    if it may not be edited, pop a message that says this
    if it may, call the editing routine a get the new value.

    I do not see how Datepicker works into it, on the allowing/not allowing editing. This is only one value, I assume each "edited cell" has its own "closing date" associated with it and you would just read that date.

    Steve

  7. #7
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Steve,

    all input to the sheet goes over the userform and the inputblocking after the line is being blocked goes for the whle line.
    All cells of the sheet have been blocked and input can only go over the usrform

    I need to know that when I choose a blocking date (today) it is indeed within the timewindow issuedate / today.
    All other days make that the blocking does NOT go in effect.
    The timewindowsettings goes wrong on occacions.

    past --------------x-----------issuedate +++++++++++++++today ----------x----------future
    on x the imput will not be blocked as it is outside the timewindow
    the earliest block is when issuedate = today=closeday
    x as closeday = no blocking
    +++++++++++++++++ timewindow,where a close day can be applicable

    maybe the graph is more clearl

    regards,
    jan

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    I don't have a solution to your problem, but one thing drew my attention. You have lines like this one in your code:

    Dim Issuedate, idate: Issuedate = Val(Range("c" & loc).Value)

    The Dim statement dimensions the variables as Variant, forcing VBA to decide what is in them. Since these variables are to contain dates, why not Declare them as Date?

    Another thing to keep in mind:
    If you declare more than a single variable on a Dim statement, ALL variables need to be followed by their type:

    Dim sString As String, sOtherstring as String

    Otherwise only the one(s) that has(have) a type declaration will be declared as the type entered, the others as Variant,so:

    Dim sString, sOtherstring as String

    This makes sOtherstring a string type, but sString a Variant!

    Personally I like to keep all declarations at the top of a module, before any other code, and one variable per Dim statement.
    Improves readability enormously.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Karel Jan,

    thank for that sound advice. You are right of course and it slipped my mind. The one with the double dim declarations.
    I'll bear that in mind for the future. Normally I put all dims as you say but this still in its first stage I went a it came along.
    Did't now about the date variant have to see that one trough.

    Thanks,
    Jan

  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Jan

    Does the attached wbk help you any?
    It allows data to be entered in the Data list only if an IssueDate has been set and then only if the CloseDate has not been entered.
    Also thru Data Validation, the CloseDate can be no earlier than the IssueDate for that record and no later than today.

    To match your setup this would have to be adapted to data entry thru a UserForm. Or if you wish, adapt your setup to this format.

    Paul

  11. #11
    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

    Re: date window (excel2000)

    Jan,
    What I understand (correct me if I'm mistaken)
    You have a dataset.
    The data set has a column for IssueDate, ClosingDate, and others.
    You want to be able to edit only the cells in the row when the current date (Today) is >= the issuedate and before the closingDate. All other times they can not be changed.

    What I do not understand and am still a little confused about what your question is:
    Are you looking for ideas on how to allow and prevent input into those cells? You have gotten several approaches, from protecting/unprotecting a cell (which I mentioned originally, and Paul has done some coding with), using the dbl-click event, or using Userforms (which is, I think, your current setup). This answers the general question of how you could do it.

    Are you having problems with the logic of the problem?
    Are you having problems with translating the logic of the the problem into macro coding?
    Are you having problems with a macro either not working (code errors) or not working properly (logic)?

    If you need help with these questions you will need to provide more specific info before we can provide any assistance.

    Steve

  12. #12
    Lounger
    Join Date
    Feb 2004
    Location
    Belgium
    Posts
    30
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: date window (excel2000)

    Hi all,

    thanks for all the reply so far. Paul, you solution goes only that far tha it only unlock the last line, while several lines of the sheet are adaptabel by the userform. But still a code to be recyled in another situation.
    What I really need is the right logic translation for the macro because there alway is a flaw.


    If Issuedate >closedate Or Windowdate > closedate Then '(this is the line that causes the trouble)
    MsgBox ("Erratic dates !" & vbLf & vbLf & "You are trying to .....

    Somehow on of the date if doesn't add up to it
    1- by taking the serialnumber of the date issuedate has alway to be smaller of equal to (close date=windowdate) to prevent a closing befor the issuedate
    2- windowdate= closedate has to be equal or less than closedate to prevent a closing into the future
    and somehow with format dd/mm/yy the second one tends to fail
    maybe it has to do with issuedate and window date being dates and dpick.value date & time
    It could be something I am not recognizing or seeing but it is fustrating enough.
    The image gives the layot of the sheet and form.

    I hope you are still with me.

    Thanks,
    Jan

  13. #13
    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

    Re: date window (excel2000)

    Where is the failure?
    Is it "grabbing" the issue date from the wrong location or row?
    Is it "grabbing" the closing date from the wrong location or row?

    Is it converting either of the dates incorrectly? (VB assumes many dates are mm/dd/yy)
    I am not sure when that code is supposed to run. Your userform picture tells me there is a lot of code and the code you posted is only partial. I can't see what any of the variables in your code are. some are not even initialized so I can not run thru it.

    Could you be a little more specific or provide a "small" working example containing the troublesome code. I don't need the whole applicaiton, just a few of the problem codes, even if you have to create a routine to "feed other" info into it.

    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
  •