Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 9th, 2003, 05:13 AM
Registered User
 
Join Date: Aug 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default validation of [HH]:mm

is it possible to validate so that a user can only input in that format eg.37:00 ..i have tried with normal validation and it goes pear after 24 hrs..does htis have to be done in VBA if so how easy is it? im kinda new to excel

 
Old August 9th, 2003, 10:45 PM
Authorized User
 
Join Date: Aug 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

beastwood_uk,

You can certainly grab the string that the User entered and then examine it for validation purposes.

I'm a little confused, though, from your example of 37:00 -- how is it that you go past 24??

CarlR

 
Old August 10th, 2003, 05:33 AM
Registered User
 
Join Date: Aug 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

its basically so people can input the hours they work eg from 12 hours through to 37 hours a week..but i want to fix it so they can only enter in ##:## format i can use normal validation but it does not accept anything past 24:00 :(

 
Old August 10th, 2003, 08:37 AM
Authorized User
 
Join Date: Aug 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

beastwood_uk,

I would suggest that rather than trying to validate it as time (which it is NOT since you're looking for cumulative hours worked), that you simply parse the string and examine it numerically.

Assuming that the string is coming from a textbox (such as Textbox1) I would do something like the following. [NOTE: In the code below, I say Goto ERRORMSG to indicate that this is where the validation has failed and the User needs to be made aware of that.]

' ************************************************** ******************
' ************************************************** ******************
Dim strTemp as String
Dim strHours as String
Dim strMins as String
Dim intHours as Integer
Dim intMins as Integer

strTemp = Textbox1.Text

If Len(strTemp) < 3 Then Goto ERRORMSG 'you at least need h:m

ColonPos = InStr(strTemp, ":") 'position of colon within string
If ColonPos = 0 Then Goto ERRORMSG

strHours = Left(strTemp, ColonPos - 1) ' to extract hours into var

strMins = Right(strTemp, Len(strTemp) - ColonPos) ' get the minutes

If Len(strHours) = 0 or IsNumeric(strHours) = False Then Goto ERRORMSG

If Len(strMins) = 0 or IsNumeric(strMins) = False Then Goto ERRORMSG

intHours = Val(strHours) 'place Hours into an integer var
intMins = Val(strMins) ' same for mins

If intHours > 37 then Goto ERRORMSG

If intMins > 60 then Goto ERRORMSG
' ************************************************** ******************
' ************************************************** ******************

That should pretty much take care of it.

For more info recommend http://www.vba-programmer.com

CarlR










Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert seconds to HH:MM:SS bph SQL Server 2000 11 July 31st, 2009 01:53 PM
converting HH:MM to HH.MM nasirmunir PHP How-To 5 July 11th, 2008 01:33 PM
Convert time in seconds to hh:mm:ss 41509331 C++ Programming 0 March 18th, 2008 04:43 AM
Converting number to hh:mm dlyles Beginning VB 6 8 September 19th, 2006 12:27 PM
Convert Seconds to HH:MM:SS jpillonel Crystal Reports 0 December 17th, 2003 09:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.