VB help

Please can you help with the attached file? I wish to alter the code so that if no data is entered into:

txtOpen
txtOpenTime
txtClosed
txtClosedTime

Then " : " is not entered into the cell upon input (Private Sub cmdAdd_Click())

Also if the date is only entered in txtOpen & txtClosed, then the output should not add the " : " separator.
help2.xls
Kiwi-123Asked:
Who is Participating?
 
Bill PrewCommented:
I thought that was what my adjusted code added, only adding the time if it was filled in.  So I'm not sure exactly what the exact circumstances are that you still need to address?

~bp
0
 
Bill PrewCommented:
This should take care of the issues.

~bp
help2.xls
0
 
Martin LissOlder than dirtCommented:
I'm not sure exactly what you want but you can add code like this

If txtOpen.Value > "" And txtOpenTime.Value > "" Then
    Cells(.Row, "I") = txtOpen.Value & " " & Left(txtOpenTime.Value, 2) & ":" & Right(txtOpenTime.Value, 2)
End If

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Kiwi-123Author Commented:
Is it possible for the code to not recall/filter out the separator, this might fix the problem.

Private Sub UserForm_Initialize()
If value = ":" then substitute

This way the date/time would always autocorrect on the next "add"
0
 
Bill PrewCommented:
Did you try my code?

~bp
0
 
Kiwi-123Author Commented:
I did try the code, but the command button now enters the time as "Now" and really I would like to keep the two separate.

So if the date is just added the value should equal: dd/mm/yyyy

If the date and time is entered: dd/mm/yyyy hh:mm

No entry within textboxes: ""

The main problem I am having is the " : " appears, this means when I come to add the time (Which is usually after the date) the value in the cell is already: dd/mm/yyyy : which means on entry there will be a double separator.
0
 
Bill PrewCommented:
So are you saying all works fine now, except you don't want the current time auto entered if they click the Y or T "buttons"?  That's an easy fix, just remove the lines:

    txtOpenTime.Value = Format(Time, "hh:mm")

and

    txtClosedTime.Value = Format(Time, "hh:mm")

However, the fact that you are formatting the cells in the sheet with the custom format of "mm/dd/yyyy h:mm" means that if there is no time value, it will still display as "0:00".  You will need to remove the date/time formatting if you sometimes want to see the time displayed, and at others do not want to.

~bp

~bp
0
 
Kiwi-123Author Commented:
"fact that you are formatting the cells in the sheet with the custom format of "mm/dd/yyyy h:mm" means that if there is no time value, it will still display as "0:00".  You will need to remove the date/time formatting if you sometimes want to see the time displayed, and at others do not want to."

The user form recalls the values which are present within the cell ".offset" etc.

If I could amend the code in the userform so that it omitted the ":" on each occasion. Then on the "add" command it would only add the ":" if both textboxes were not blank "".

If just the date one was entered it was just input dd/mm/yyyy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.