• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Where do I put the format code?

K

Kristen_cancerkiller

Member
I have created a userform which requires an employee to enter depart and return time from travel. I am currently using the following code to move the field to my database, however, I would like to ensure that the time is formatted as "hh:mm" so it will return responses such as 16:24 in my database field. I know that the format I want is as entered above (hh:mm) but I am unsure where to insert this into the following code. I would greatly appreciate it if someone would be willing and able to help me with this. :)

Code: 
Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25).Value = txtDepartTime 'departure time
 
Depends, but as general practice I tend to set format before the values are passed. This is only needed for long numbers that you want to store as text, but I like to keep it consistent so that I don't forget.

Code: 
With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
    .NumberFormat = "hh:mm"
    .Value = txtDepartTime
End With
 
Hmmm... that doesn't seem to be working... :( I'm getting an error. 'run-time error '438': Object doesn't support this property or method'. I will include more of my code for you to see. Maybe something is arguing with this code? My code worked fine until I realized that I need the time value to be formatted in a specific way when transferred to the second location.

Code: 
Private Sub txtDepartTime_Change()
'When time is entered, time transfers immediately to spreadsheet datafield.

TargetRow = Sheets("Codes").Range("D43").Value + 1

Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 1).Value = txtDepartTime 'departure time

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25).Numerformat = "hh:mm"
 .Value = txtDepartTime 'departure time
 End With
 
End Sub
 
Don't put NumberFormat after Range object in With statement. It should be inside with block like I posted.
 
Duh... I had tried that before but it wouldn't work which is why I changed it up. Well, apparently I just couldn't spell number. Sorry. :) However, I have it coming into my database now in the correct format, however, I need it to be in military time, which is what I thought this would do... is there something different that I need to do?
 
That will depend on what's stored in the variable. If it's string/text you may need some conversion before you put it back in range.
 
The user is told to enter the time as hh:mm am/pm. So they are entering 3:30 pm and when it goes to my TargetRow,25, I need it to go into the database as 15:30. So I assume that I do need to convert it? What else do you need to know and how do I find it out? :)
 
Last edited by a moderator:
I figured it out (well, not only me, had a little advice from a fellow vba enthusiast). :) I had my coding a bit off... my corrected code is below. :) And now it works like a dream! Thank you so much for all your help! I greatly appreciate it!!

Code: 
Private Sub txtDepartTime_AfterUpdate()
'When time is entered, time transfers immediately to spreadsheet datafield.

TargetRow = Sheets("Codes").Range("D43").Value + 1

Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 1).Value = txtDepartTime 'departure time

With Sheets("Travel Expense Voucher").Range("Data_Start").Offset(TargetRow, 25)
 .Value = TimeValue(txtDepartTime)
 .NumberFormat = "hh:mm" 'departure time
 
 End With
 
End Sub
 
You must log in or register to reply here.
Back
Top