Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Active cell format determination (Excel 2000 (SR3?

    This is probably something relatively simple, but I have looked in a lot of places - books, web sites, etc. - and end up back in the Lounge. [yup] I am developing a spreadsheet to track project issues, action items, etc. and have built a calendar application for the user to insert dates - less errors that way. What I want to do is check to see if the active cell has a date format assigned to it and if it doesn't, then display a message to that effect. This will ensure that dates get put in the right cells. Any insight that anyone can provide, e.g. ActiveCell.Format... is greatly appreciated. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Thanks,

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  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

    Re: Active cell format determination (Excel 2000 (SR3?

    Just use the isdate function in your vb code:

    <pre>If Not (IsDate(rcell.Value)) Then _
    MsgBox rcell.Address & " is not a date"</pre>


    Steve

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Active cell format determination (Excel 2000 (

    Thanks Steve. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> One question, more out of ignorance, will this still work if the cell is blank? The way my Calendar works is that the user will be putting Dates into blank cells that have been preformatted to display a date in a specific way. The user invokes the calendar from a button and then clicks the appropriate date to go into the active cell. I am using the Calendar, off a User form, to ensure that the number of errors is minimum and that the format is correct. It is really the format of the active cell I want to check, not whether the cell contains a date. I do not want them entering dates into cells that are not supposed to contain dates. Sorry, if I was not clear on this. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  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: Active cell format determination (Excel 2000 (

    To have isdate be true, the cell must be formatted as one of the date or date/time functions (not just a time) and have a number >0 and <2958466 in it. Formatted as a date with text will be false, formatted as date and blank is false. The same numbers but not formatted as date will be false.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Active cell format determination (Excel 2000 (

    Thanks Steve. Sorta what I suspected. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> This does not resolve my situation. What I need to do is check the formatting of the active cell, not its contents. If the formatting is a date format, then everything is okay, if it is not, then I want to display a message to that extent. My question is how do I make this happen in VBA. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Thanks.

    Ron M

  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: Active cell format determination (Excel 2000 (

    This checks both the format and the contents. Both have to be correct. A number in the range without a date format is not a date. A date format without a valid number is not a date.

    If you just want the format of the cell, you can use the "NumberFormat" property.

    for example:
    sFormat = ActiveSheet.Range("A1").NumberFormat

    will put in the sFormat variable the format of the cell A1 of the activesheet.

    But you would have to create your own routine to tell if the format is an acceptable date format, since there are many built-in and you can create many custom ones.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Active cell format determination (Excel 2000 (

    Thanks Steve, and I thought this was going to be something simple. Silly me. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> I guess its the last part ("create your own routine") that is a bit beyond my VBA capabilities - at this point in time - learning everyday! <img src=/S/compute.gif border=0 alt=compute width=40 height=20> Maybe I will just go with what I have for now.

    Ron <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Active cell format determination (Excel 2000 (

    Problem is that there are many date formats to test for.

    CBool(InStr(ActiveCell.NumberFormat, "/"))

    should return true for all the slash formatted dates, but it won't return true for dates formtted in "mmmm d, yyyy", and it will return true for fractions formatted "# ?/8".
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    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: Active cell format determination (Excel 2000 (

    Not sure I understand the problem with isdate.

    Is it the blank cell issue? Do you want a blank cell to be considered a date? Can't you wait until the entry is made to the cell? or if you want a particular format for the date (or even several possibilities) those can be checked.

    This might work:

    <pre>Function HasDateFormat(rCell As Range) As Boolean
    Dim v
    v = ""
    On Error Resume Next
    v = DateValue(Format(38000, rCell.Cells(1).NumberFormat))
    On Error GoTo 0
    HasDateFormat = IsDate(v)
    End Function</pre>


    It is different than using just Isdate. It only is concerned with the format. Even if the cell is blank or has text in it as long as it is formatted as time, it will give true. Also unlike isdate, a format that is ONLY time (and no date component) will be true in this UDF, though isdate would find it false.

    If desired you could modify it with other tests.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Active cell format determination (Excel 2000 (

    Steve, the blank cell is not the issue. I am assembling a spreadsheet to track project issues and action items. There are three dates associated with each item - Date Raised, Date Required and Date Completed. I have cobbled together a Calendar application (with some help from other sites) to minimize errors in entering dates. What I do is preformat these three columns with a date format so that the dates will appear as December 18, 2003, etc. , so I have three columns preformatted to contain dates.

    What I am grying to do is prevent a user from entering a date where it does not belong. If a user accidentally selects a non-date cell without realizing it, I want the Calendar application to be able to determine that the selected cell is not formatted to receive a date and send a message to that effect, rather than displaying the calendar. So the blank cell, where a date is to be entered is preformatted to contain a date, all the other cells are formatted to take text. I am not sure there is any other way to do this.

    Hope this helps to clarify things... <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Ron <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  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: Active cell format determination (Excel 2000 (

    What then is the question?

    The last function I just gave should check that cell has a valid date format. It only checks the format of the cell. If the cell does not have a valid date format the function will return false. So just pass the cell info to the function and if false is returned give a message and perhaps quit the return or make them choose a different cell. If the format is a date you can proceed with the rest of the code.

    Or You could also just check to see that the cell has particular preformatted date for the columns

    Or If there is only 3 columns to enter dates, you could just check that the user was in one of the columns of interest and not worry about the format. You could even add the format afterwards.

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Active cell format determination (Excel 2000 (

    Thanks Steve. I shall give it a go. I appreciate all your help with this. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Thanks.

    Ron

Posting Permissions

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