[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB help

Posted on 2012-09-03
8
Medium Priority
?
460 Views
Last Modified: 2012-09-21
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
0
Comment
Question by:Kiwi-123
  • 4
  • 3
8 Comments
 
LVL 59

Assisted Solution

by:Bill Prew
Bill Prew earned 666 total points
ID: 38362020
This should take care of the issues.

~bp
help2.xls
0
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 334 total points
ID: 38362040
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
 

Author Comment

by:Kiwi-123
ID: 38362088
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

Expert Comment

by:Bill Prew
ID: 38362094
Did you try my code?

~bp
0
 

Author Comment

by:Kiwi-123
ID: 38362600
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
 
LVL 59

Expert Comment

by:Bill Prew
ID: 38363231
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
 

Author Comment

by:Kiwi-123
ID: 38366888
"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
 
LVL 59

Accepted Solution

by:
Bill Prew earned 666 total points
ID: 38375837
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

872 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