?
Solved

VB command button help

Posted on 2012-08-25
37
Medium Priority
?
745 Views
Last Modified: 2012-08-25
Please can you help with the attached. When useform 1 opens it brings back the values held in the columns upon that line.

The problem I get is on line 7, the values come back as "25/08/2012 :" in both:-

txtTransDate
txtTransTime

Really what should come back is:-

txtTransDate "25/08/2012"
txtTransTime "" blank as no data been entered yet.







Private Sub UserForm_Initialize()


    Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart


        TextBox1.Value = Format(.Value, "dd/mm/yyyy")
        txtTransDate.Value = Format(Cells(rngStart.Row, "I").Value, "dd/mm/yyyy")

        txtTransTime.Value = Format(Cells(rngStart.Row, "I").Value, "hh:mm")

        txtClosedDate.Value = Format(Cells(rngStart.Row, "W"), "dd/mm/yyyy")
        txtClosedTime.Value = Format(Cells(rngStart.Row, "W").Value, "hh:mm")
Help-23-08-2012.xls
0
Comment
Question by:Kiwi-123
  • 15
  • 12
  • 10
37 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332648
The first line in the Initialize event says

Set rngStart = Cells(ActiveCell.Row, "B")

which means that rngStart is being set to the cell that is selected. So in order for your code to work correctly you must physically select row 5 or 6 before you press the 'Userform' button.
0
 

Author Comment

by:Kiwi-123
ID: 38332711
Hi MartinLiss

Yes, there is no problem in physically selecting the actual line, the trouble is that when I try and 'recall' the values on that line the date & time function becomes jumbled up.

I need the value to split back up into dd/mm/yy = txtTransDate & hh:mm = txtTransTime

However when hh:mm is blank (I7) the return value should equal ""
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332720
I'm sorry but I don't understand what you want. Can you please describe step by step what you do, and what you want the results (naming the specific textbox(es) to be.
0
Independent Software Vendors: 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 35

Expert Comment

by:Norie
ID: 38332748
The problem is the : after the date.

This turns the value in the cell into text and the formatting doesn't work because of that.

If you remove the : then you will get the date in txtTransDate and txtClosedDate and 00:00 in txtTransTime and txtClosedTime.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332792
How does the ":" get there?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332793
Martin

Good question.
0
 

Author Comment

by:Kiwi-123
ID: 38332797
Open the file and see for yourself.

Select line 7 and hit the command button, your notice that dd/mm/yy : is returned in both txtTransDate & txtTranstime

This is wrong! txtTranstime is just for the time (HH:MM)

It should be txtTransDate = dd/mm/yyy  

Then txtTranstime is "" because none has been entered

If you look at the reference cell ( I7) this equals "25/08/2012 :"
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332800
deleted
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332809
Kiwi123

I've given the reason why this is happening - it's the colon : after the date(s).

Remove the colon the everything works.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332811
Yes I see that it's "25/08/2012 :" but what put that value there? I ask because it's probably easier to prevent that value getting there then working with it once it's there.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332817
Try this:

Private Sub UserForm_Initialize()
    Dim dte As Date

    Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart


        TextBox1.Value = Format(.Value, "dd/mm/yyyy")
        If Right(Cells(rngStart.Row, "I").Value, 1) = ":" Then
            dte = Left(Cells(rngStart.Row, "I").Value, 10)
        Else
            dte = Cells(rngStart.Row, "I").Value
        End If
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")

        txtTransTime.Value = Format(dte, "hh:mm")

        txtClosedDate.Value = Format(Cells(rngStart.Row, "W"), "dd/mm/yyyy")
        txtClosedTime.Value = Format(Cells(rngStart.Row, "W").Value, "hh:mm")

        TextBox3.Value = Format(.Offset(, 20).Value, "dd/mm/yyyy")

        TextBox17.Value = Format(.Offset(, 2).Value, "")
        TextBox16.Value = Format(.Offset(, 1).Value, "")
        TextBox5.Text = .Offset(, 5).Value
        TextBox15.Value = Format(.Offset(, 16).Value, "")
        TextBox6.Value = Format(.Offset(, 3).Value, "")
        TextBox8.Value = Format(.Offset(, 19).Value, "")
        TextBox18.Value = Format(.Offset(, 6).Value, "")

    End With
End Sub

Open in new window

0
 
LVL 35

Expert Comment

by:Norie
ID: 38332828
This code will check if there is a date in the cell.
        If IsDate(Cells(rngStart.Row, "I").Value) Then
            txtTransDate.Value = Format(Cells(rngStart.Row, "I").Value, "dd/mm/yyyy")
            txtTransTime.Value = Format(Cells(rngStart.Row, "I").Value, "hh:mm")
        End If

        If IsDate(Cells(rngStart.Row, "W").Value) Then
            txtClosedDate.Value = Format(Cells(rngStart.Row, "W"), "dd/mm/yyyy")
            txtClosedTime.Value = Format(Cells(rngStart.Row, "W").Value, "hh:mm")
        End If

Open in new window

0
 

Author Comment

by:Kiwi-123
ID: 38332866
Tried both codes but none seem to work
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332871
I'm sorry, I forgot about the Close date.

Private Sub UserForm_Initialize()
    Dim dte As Date

    Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart


        TextBox1.Value = Format(.Value, "dd/mm/yyyy")
        If Right(Cells(rngStart.Row, "I").Value, 1) = ":" Then
            dte = Left(Cells(rngStart.Row, "I").Value, 10)
        Else
            dte = Cells(rngStart.Row, "I").Value
        End If
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")

        txtTransTime.Value = Format(dte, "hh:mm")

        If Right(Cells(rngStart.Row, "W").Value, 1) = ":" Then
            dte = Left(Cells(rngStart.Row, "W").Value, 10)
        Else
            dte = Cells(rngStart.Row, "W").Value
        End If
        txtClosedDate.Value = Format(dte, "dd/mm/yyyy")
        txtClosedTime.Value = Format(dte, "hh:mm")

        TextBox3.Value = Format(.Offset(, 20).Value, "dd/mm/yyyy")

        TextBox17.Value = Format(.Offset(, 2).Value, "")
        TextBox16.Value = Format(.Offset(, 1).Value, "")
        TextBox5.Text = .Offset(, 5).Value
        TextBox15.Value = Format(.Offset(, 16).Value, "")
        TextBox6.Value = Format(.Offset(, 3).Value, "")
        TextBox8.Value = Format(.Offset(, 19).Value, "")
        TextBox18.Value = Format(.Offset(, 6).Value, "")

    End With
End Sub

Open in new window

0
 
LVL 35

Expert Comment

by:Norie
ID: 38332873
Kiwi123

There is nothing wrong with the original code.

If there were dates in the relevant columns it would have worked, but since there aren't it won't.

Could you please tell us how the : got there?

That's what's causing the problem and if it wasn't present there wouldn't be a problem - try removing it yourself.

As for the code, how did it not work?

The code I posted checks if there's a date and if there is put's the appropriate values in the textboxes.

If the value isn't a date it put's nothing in the textboxes.

The code could be changed to get rid of the colon, but what if next time it's not a colon?
0
 

Author Comment

by:Kiwi-123
ID: 38332879
On a blank/new line the date comes back as "30/12/1899" when they should both be ""
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332897
Strange because when I try it on a blank line they are both "".
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332898
Is this good enough?


Private Sub UserForm_Initialize()
    Dim dte As Date

    Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart

        If TextBox1.Value = "" Then
            Exit Sub
        End If
        
        TextBox1.Value = Format(.Value, "dd/mm/yyyy")
        If Right(Cells(rngStart.Row, "I").Value, 1) = ":" Then
            dte = Left(Cells(rngStart.Row, "I").Value, 10)
        Else
            dte = Cells(rngStart.Row, "I").Value
        End If
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")
        txtTransDate.Value = Format(dte, "dd/mm/yyyy")

        txtTransTime.Value = Format(dte, "hh:mm")

        If Right(Cells(rngStart.Row, "W").Value, 1) = ":" Then
            dte = Left(Cells(rngStart.Row, "W").Value, 10)
        Else
            dte = Cells(rngStart.Row, "W").Value
        End If
        txtClosedDate.Value = Format(dte, "dd/mm/yyyy")
        txtClosedTime.Value = Format(dte, "hh:mm")

        TextBox3.Value = Format(.Offset(, 20).Value, "dd/mm/yyyy")

        TextBox17.Value = Format(.Offset(, 2).Value, "")
        TextBox16.Value = Format(.Offset(, 1).Value, "")
        TextBox5.Text = .Offset(, 5).Value
        TextBox15.Value = Format(.Offset(, 16).Value, "")
        TextBox6.Value = Format(.Offset(, 3).Value, "")
        TextBox8.Value = Format(.Offset(, 19).Value, "")
        TextBox18.Value = Format(.Offset(, 6).Value, "")

    End With
End Sub

Open in new window

0
 

Author Comment

by:Kiwi-123
ID: 38332915
Very sorry, but the latest code doesn't bring back any values?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332922
Kiwi123

The only way I can get 30/12/1899 is by putting 0 in one of the cells.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332926
You need to be specific.

When you choose a blank line what do you want to see in each of the textboxes on the userform. For example what do you want to see in Textbox3?
0
 

Author Comment

by:Kiwi-123
ID: 38332943
I have been specific, TextBox3 will equal the value which is held in offset 20. All the other textboxes work fine, the only problem I have is with:-

txtTransDate
txtTransTime

The two textboxes combine to input a value of dd/mm/yy HH:MM, however on recall the value should split to:-

txtTransDate dd/mm/yy
txtTransTime hh:mm

The problem I have is when on input, someone only adds the dd/mm/yy into txtransdate

If you would attempt to recall(open userform upon this line) the following would appear:-

txtTransDate dd/mm/yy
txtTransTime dd/mm/yy

I don't want the date re-entered into txtTransTime, it should remain blank because I7 equals "25/08/2012 :"

The whole point is that someone could see that the date was there but the time was blank
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332948
Kiwi

The problem isn't that they only add the date.

It's that they add the date followed by a colon.

That colon is what's causing all the problems and if you got rid of it all your problems would be gone.

Try this test for me.

Put this formula in a cell:

=ISNUMBER(I7)

This will return TRUE if there is a 'real' date in I7, and FALSE if there isn't.
0
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 800 total points
ID: 38332957
The problem I have is when on input, someone only adds the dd/mm/yy into txtransdate
Okay you've now answered the question of how the : gets there.

How about preventing the bad input with something like tis:


Private Sub cmdAdd_Click()

    If Not IsDate(txtTransDate) Then
        MsgBox "Please enter a valid time in the foramnt HH:MM"
        Exit Sub
    End If
    With rngStart
        Cells(.Row, "I") = txtTransDate.Value & " " & Left(txtTransTime.Value, 2) & ":" & Right(txtTransTime.Value, 2)


        Cells(.Row, "W") = txtClosedDate.Value & " " & Left(txtClosedTime.Value, 2) & ":" & Right(txtClosedTime.Value, 2)

        .Offset(, 0).Value = TextBox1
        .Offset(, 1).Value = TextBox16
        .Offset(, 2).Value = TextBox17
        .Offset(, 16).Value = TextBox15
        .Offset(, 20).Value = TextBox3
        .Offset(, 5).Value = TextBox5
        .Offset(, 19).Value = TextBox8
        .Offset(, 6).Value = TextBox18

Unload Me
End With

End Sub

Open in new window

0
 

Author Comment

by:Kiwi-123
ID: 38332960
The ISNUMBER(I7) is false but then I know that, because I haven't added the hh:MM

That is the toruble, when I come to input I may remember the date but may have to double check and enter the time and a later date. Therefore the recall option allows me to see what is already there and what I need to complete.

So in answer, I am aware that the date is not complete, but there may be times when I just need to add the date until I am aware of what hh:mm to input

So if the value equalled "25/08/2012:" could txtTransTime not equal ""?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332961
Martin

There is no IsTime function.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332963
I know, I changed it to IsDate.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38332966
No the ISNUMBER is not FALSE because you haven't added the time, it's false because of the colon.

The colon basically turns the value into a string.

If that colon wasn't there then there would be no problem.

If there was just the date and no colon then there would be no problem.

Please try removing the colon and see what happens.
0
 

Author Comment

by:Kiwi-123
ID: 38332975
Okay, but can the code not allow for a string?

if "dd/mm/yy :" then ""
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38332977
Try this:


Private Sub cmdAdd_Click()

    With rngStart
        If txtTransTime.Value = "" Then
            Cells(.Row, "I") = txtTransDate.Value
        Else
            Cells(.Row, "I") = txtTransDate.Value & " " & Left(txtTransTime.Value, 2) & ":" & Right(txtTransTime.Value, 2)
        End If

        If txtClosedTime.Value = "" Then
            Cells(.Row, "W") = txtClosedDate.Value
        Else
            Cells(.Row, "W") = txtClosedDate.Value & " " & Left(txtClosedTime.Value, 2) & ":" & Right(txtClosedTime.Value, 2)
        End If

        .Offset(, 0).Value = TextBox1
        .Offset(, 1).Value = TextBox16
        .Offset(, 2).Value = TextBox17
        .Offset(, 16).Value = TextBox15
        .Offset(, 20).Value = TextBox3
        .Offset(, 5).Value = TextBox5
        .Offset(, 19).Value = TextBox8
        .Offset(, 6).Value = TextBox18

Unload Me
End With

End Sub

Open in new window

0
 
LVL 35

Accepted Solution

by:
Norie earned 1200 total points
ID: 38332982
I apologise.

I now see where the colon comes from.

I had assumed it was from manual input from the user.

Anyway, this should fix it.
Private Sub cmdAdd_Click()

    With rngStart

        Cells(.Row, "I") = txtTransDate.Value & " " & IIf(txtTransTime.Value <> "", Left(txtTransTime.Value, 2) & ":" & Right(txtTransTime.Value, 2), "")
        Cells(.Row, "W") = txtClosedDate.Value & " " & IIf(txtClosedTime.Value <> "", Left(txtClosedTime.Value, 2) & ":" & Right(txtClosedTime.Value, 2), "")


        .Offset(, 0).Value = TextBox1
        .Offset(, 1).Value = TextBox16
        .Offset(, 2).Value = TextBox17
        .Offset(, 16).Value = TextBox15
        .Offset(, 20).Value = txtCommence
        .Offset(, 5).Value = TextBox5
        .Offset(, 19).Value = TextBox8
        .Offset(, 6).Value = TextBox18

    End With

    Unload Me
End Sub

Open in new window

0
 

Author Comment

by:Kiwi-123
ID: 38333027
What about a case function

If .Offset(, 7).Value = txtTransTime.Value IS NOT equal to "dd/mm/yy hh:MM" then "" would that idea work?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38333031
What is wrong with my post ID: 38332977?
0
 

Author Comment

by:Kiwi-123
ID: 38333034
Nothing I just refreshed the page and saw it
0
 
LVL 35

Expert Comment

by:Norie
ID: 38333037
Kiwi123

The problem was that if the time part wasn't entered the colon was being added to the date.

The code Martin and I posted will deal with this.

Martin's is clearer so I would probably go with that.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38333041
Thanks imnorie but I'm happy with the Author's split.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38333211
As imnorie pointed out there is no IsTime function but that doesn't mean you can't write one:)


Private Function IsTime(str As String) As Boolean

'If you ever want to require that something be entered then uncomment these lines
'    If Trim(str) = "" Then
'        IsTime = False
'    End If
   
    With CreateObject("VBScript.RegExp")
        .Pattern = "^(20|21|22|23|[01]\d|\d)(([:][0-5]\d){1,2})$"
        IsTime = .test(str)
    End With
End Function
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 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