Solved

VBA Time Values

Posted on 2014-01-31
21
503 Views
Last Modified: 2014-02-03
Hello Experts,

Please bare with me as I try to explain what I'm trying to accomplish.

I watched a video on  YouTube, which was showing 'sorta' what I wanted to do. I attempted to take that example and revise it to work for me - but with my lack of VBA knowledge, I ran into a road block.

On an Excel workbook, I want the code to look at columns A & B.

00:00:00 = HH:MM:SS

If the user types in "3", then the code will change the value to:  "00:00:03"

If the user types in "43", then the code will change the value to:  "00:00:43"

If the user types in "443", then the code will change the value to:  "00:04:43"

If the user types in "2443", then the code will change the value to:  "00:24:43"

If the user types in "32443", then the code will change the value to:  "03:24:43"

If the user types in "132443", then the code will change the value to:  "13:24:43"

See the attached workbook I created.  As you can see, I don't know what I'm doing - and to be honest, it's all Greek to me.

If you take a look and start working on a solution, please "comment out" anything I have already - and when you write your code, try to keep it as easy to read as possible.  I struggle when I read through VBA code, so any additional help would be great for me.

P.S.  I know there will be an issue, if the user types in "67" (I do not want to see 00:00:67) - but I figured I'll handle that issue, after the first part of my question is sorted out.

P.S.S. Here is the link to the video I was watching.  His example uses army time, and mine doesn't.  http://youtu.be/ATxaNbTV2d0

Thank you in advance for your help!

~ Geekamo
testing-timevalues.xlsm
0
Comment
Question by:Geekamo
  • 14
  • 7
21 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39825893
I revised your code to accept text and numbers with more than 7 digits,
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range, targ As Range
Dim v As Variant
Set targ = Range("A2:B100")     'Watch these cells for time entries
Set targ = Intersect(targ, Target)

If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        If IsNumeric(cel.Value) Then
            If cel.Value > 0 Then
                If Len(cel.Value) < 7 Then
                    On Error Resume Next
                    v = 0
                    v = TimeValue(Format(cel.Value, "00:00:0#"))
                    On Error GoTo 0
                    If v = 0 Then
                        cel.Select
                        MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                        cel.ClearContents
                    Else
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    End If
                Else
                    cel.NumberFormat = "General"
                End If
            Else
                cel.NumberFormat = "General"
            End If
        End If
    Next
    Application.EnableEvents = True
    
End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825901
@ byundt,

Wow!  First off, I just dropped your code into a workbook and viola - it's doing what I want it to.  Secondly,... boy oh boy is that a lot of code to accomplish this!  lol - I got a lot of reading to do.

I'm going to play with this for a little bit, and I'll let you know if I have any issues that I wasn't expecting.

So far, a big thank you to you!

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825903
@ byundt,

Ok, so is it possible to revise the code so that...

1.)  It doesn't allow for text entry.  These cells should only accept numbers.

2.)  And it doesn't allow for entry of more than 6 numbers.

~ Geekamo
0
 
LVL 80

Expert Comment

by:byundt
ID: 39825904
Tweaks as requested:
Private Sub Worksheet_Change(ByVal target As Range)

Dim cel As Range, targ As Range
Dim v As Variant
Set targ = Range("A2:B100")     'Watch these cells for time entries
Set targ = Intersect(targ, target)

If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        If IsNumeric(cel.Value) Then
            If cel.Value > 0 Then
                If Len(cel.Value) < 7 Then
                    On Error Resume Next
                    v = 0
                    v = TimeValue(Format(cel.Value, "00:00:0#"))
                    On Error GoTo 0
                    If v = 0 Then
                        cel.Select
                        MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                        cel.ClearContents
                    Else
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    End If
                Else
                    cel.Select
                    MsgBox "Too many digits in " & cel.Value
                    cel.ClearContents
                End If
            Else
                cel.Select
                MsgBox cel.Value & " is not a permissible time value"
                cel.ClearContents
            End If
        Else
            cel.Select
            MsgBox cel.Value & " is not a permissible time value"
            cel.ClearContents
        End If
    Next
    Application.EnableEvents = True
    
End If

End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 39825909
Not enough testing. My goof.
Private Sub Worksheet_Change(ByVal target As Range)

Dim cel As Range, targ As Range
Dim v As Variant
Set targ = Range("A2:B100")     'Watch these cells for time entries
Set targ = Intersect(targ, target)

If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        If IsNumeric(cel.Value) Then
            If cel.Value > 0 Then
                If Len(cel.Value) < 7 Then
                    On Error Resume Next
                    v = 0
                    v = TimeValue(Format(cel.Value, "00:00:0#"))
                    On Error GoTo 0
                    If v = 0 Then
                        cel.Select
                        MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                        cel.ClearContents
                    Else
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    End If
                Else
                    cel.Select
                    MsgBox "Too many digits in " & cel.Value
                    cel.ClearContents
                End If
            Else
                If cel.Value < 0 Then
                    cel.Select
                    MsgBox cel.Value & " is not a permissible time value"
                    cel.ClearContents
                End If
            End If
        Else
            cel.Select
            MsgBox cel.Value & " is not a permissible time value"
            cel.ClearContents
        End If
    Next
    Application.EnableEvents = True
    
End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825912
@ byundt,

2 things...

When you enter in values into a few cells, and you want to delete those values - when you select the cells and "Clear Contents" the MsgBox comes up.  Also, if I delete one of the columns then it goes into a never ending loop of the MsgBox.

Now, I haven't put this code into the actual live workbook - I am just testing it in a blank workbook.  But once it is in the live workbook - it's a daily workbook.  So each day, I am entering new values.  And when I come in for a new day, I just click on a Clear macro that selects the range and deletes it's contents.

So I know the code will choke on that.

Any ideas?

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825917
@ byundt,

Not sure if this helps you,...

But once your code is put into the actual workbook - here is the VBA code I have to clear values out.  And I suspect once I run this, your code will choke the workbook again.

Sub ResetTalkTimeCalculator()
If MsgBox("You are about to clear the entire worksheet - " & _
    "Are you sure you wait to proceed?", vbYesNo + vbExclamation, _
    "Reset Worksheet") = vbYes Then
    ActiveSheet.Range("CallLog").ClearContents
    ActiveSheet.Range("B5:D5").ClearContents
    Sheets("Talk Time Calculator").Activate
    Sheets("Talk Time Calculator").Range("Call").Select
    End If
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825918
And "CallLog" is a named range, which refers to 5 columns.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825920
And just to be clear, when I say 5 columns - it's literally just the colums it references.  It's not a range like A1: E100, it's only the COLUMNS I am referencing - so that's like a billion cells LOL :)
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39825926
I added a test for entire columns. Code doesn't get triggered then. I am not able to reproduce your problem with deleting a range of cells, however.
Private Sub Worksheet_Change(ByVal target As Range)

Dim cel As Range, targ As Range
Dim v As Variant
If target.Rows.Count >= Rows.Count Then Exit Sub

Set targ = Range("A2:B100")     'Watch these cells for time entries
Set targ = Intersect(targ, target)

If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        If IsNumeric(cel.Value) Then
            If cel.Value > 0 Then
                If Len(cel.Value) < 7 Then
                    On Error Resume Next
                    v = 0
                    v = TimeValue(Format(cel.Value, "00:00:0#"))
                    On Error GoTo 0
                    If v = 0 Then
                        cel.Select
                        MsgBox Format(cel.Value, "00:00:0#") & " is not a permissible time value!"
                        cel.ClearContents
                    Else
                        cel.NumberFormat = "hh:mm:ss"
                        cel.Value = v
                    End If
                Else
                    cel.Select
                    MsgBox "Too many digits in " & cel.Value
                    cel.ClearContents
                End If
            Else
                If cel.Value < 0 Then
                    cel.Select
                    MsgBox cel.Value & " is not a permissible time value"
                    cel.ClearContents
                End If
            End If
        Else
            cel.Select
            MsgBox cel.Value & " is not a permissible time value"
            cel.ClearContents
        End If
    Next
    Application.EnableEvents = True
    
End If

End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Geekamo
ID: 39825942
@ byundt,

There is only one issue as far as I can tell now...

(Btw, the issue you can't reproduce was fixed when you posted the revised code on post ID: 39825909)

I put the code into my live workbook - but it appears there's an issue because my worksheet (and workbook too, though I don't think that additional protection is an issue).  Is there a line of code I can basically put at the beginning of all your code, "Unlock worksheet" and then at the end of your code I can put "Lock worksheet"?

Here's a screenshot of my settings...  Btw, I don't use a password for any protection.

screenshot
~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825944
Btw, I am aware the formatting is the issue.  And if I allow formatting of cells - the problem is gone.  But ultimately - I don't want the user to be able to format any cells, so that's why I'd prefer VBA to disable and enable the protection.  Unless you see an issue with that approach?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39825948
The Worksheet.Protect method has an option called UserInterfaceOnly. When set to True, that option allows macros to change cell values without unprotecting the worksheet. You should set this option when the workbook is opened, as it is not saved with the workbook.
'This code must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Worksheets("Sheet1").Protect UserInterfaceOnly:= True
End Sub

Open in new window

If you want to make your code more robust, consider using the Code Names for the worksheet. These are visible only in VBA, and can be changed only in VBA (using the (Name) property in the Property pane of the VBA Editor). In other words, the user won't break your code if they rename a worksheet (the tab name). If you changed the code name for Sheet1 to "wsInput" (it's a good practice to change the code name to something meaningful), then the above code becomes:
Private Sub Workbook_Open()
wsInput.Protect UserInterfaceOnly:= True
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825952
Hmm, ok - let's make sure I'm understanding this (I get lost easily...)

1.)  If I use the UserInterfaceOnly code, does that mean that I no longer click on the Worksheet protect button on the ribbon?  The code makes sure it's enabled at all times, and is ONLY protecting the user interface (not the codes themselves from doing things)

2.)  As you can see my worksheet name is Talk Time Calculator.  The first example, has the worksheet name hard coded, but the second does not.  So do I go into the VBA section and name the name field which currently says "sheet1" to something else?  I'm a little confused on that part.

open
0
 
LVL 80

Expert Comment

by:byundt
ID: 39825964
Each time that you protect the worksheet, you need to set UserInterfaceOnly:= True if you want macros to be able to modify the sheet in ways that users are not permitted. It is not necessary to unprotect the worksheet before running. FWIW, I find the name of the property confusing. Had they called it AllowMacrosToModifyWorksheet, then it would be clear what the property does.

If you are confused by code names, then don't bother with them. I  made the suggestion because you were rolling the software out for use by other people, and it's one less thing that the users can break. But I am also comfortable with the stance that a user who benefits from using the workbook should be motivated to avoid things that break it.

In the VBA Editor, the View...Properties menu item will display the Properties pane on the left underneath the Project Explorer. If you select a worksheet, its properties will display in the Properties pane. The one at the top of the list is the (Name) property, which is commonly called the Code Name. It cannot have embedded spaces. After changing the code name, hit Enter to make the change stick.

The Name property, which you will see towards the bottom of the Property pane, is what the user sees on the worksheet tabs. That is the so-called Tab Name. You can refer to a worksheet using either its code name or tab name in VBA code:
Worksheets("Talk Time Calculator").Protect UserInterfaceOnly:= True           'using Tab Name
Sheet1.Protect UserInterfaceOnly:= True           'using Code Name

If you want to use code names in your code, you don't have to change them--but it would be good practice to do so. Changing the code names makes your code more readable.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39825978
@ byundt,

Love all this information - it's shocking how much you know.  I don't think I've ever posted a question here that's stumped you.

But I have reached my limit for the night, I'm heading to bed and I'll be back tomorrow to finish up this post.

Have a great night and thanks again!

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39827199
@ byundt,

Ok, so I've been playing around with the live workbook - and there's something happening but I can't figure out why.

When you open the file, you can see the code you wrote is working perfectly fine.

Follow these steps to reproduce my error...

1.)  Select both input cells (white cells) for Start and End times.

2.)  Remove data validation on both cells. (This way, they will accept any time value now)

You'll need to remove sheet protection to do this.

3.)  Everything is still working to the right side, meaning you can enter the time values and your code will change it to the right time value.

4.)  Now when you put in a start and end time  - and then you put time values in the right side, your code chokes on all the values.

What am I missing here?
Talk-Time-Calculator---orig.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39827201
And if you remove the time values (start and end) then your values/code to the right work again.  Hmm...
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39827205
Uh, I feel so stupid.  I have data validation on the time value cells (to the right side). So the error message is coming from that, not the code you wrote.  I have to figure out what the issue is.  It's been a while since I set this all up, so it's like starting all over again :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 39827215
Better to put those tests for start time and end time in the Worksheet_Change macro rather than in a data validation. Otherwise, you'll end up with a conflict, as you have found.
0
 
LVL 1

Author Closing Comment

by:Geekamo
ID: 39831655
This has been working perfectly.  Thank you!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now