| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
userform textbox
How can i set a userform textbox properties so i can enter HH:MM.?
Thanks |
|
#2
|
|||
|
|||
|
Phil,
Here is one way, a bit kludgy but it works in full HH:MM mode Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim fColon As Boolean Dim iPos As Long iPos = InStr(1, TextBox1.Value, ":") fColon = iPos 0 Select Case KeyAscii Case 48 To 57: ' 0-9 Case 58: 'colon If fColon Then KeyAscii = 0 'colon already entered End If Case Else: KeyAscii = 0 Exit Sub End Select If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then If KeyAscii Asc("5") Then KeyAscii = 0 Exit Sub End If ElseIf Len(TextBox1.Text) = 1 Then 'nothing to do ElseIf Len(TextBox1.Text) = 2 Then If KeyAscii Asc(":") Then KeyAscii = 0 Exit Sub End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... How can i set a userform textbox properties so i can enter HH:MM.? Thanks |
|
#3
|
|||
|
|||
|
Excellent, thanks Bob.
Can i trouble you for another ? It concerns an If statement. In C8 i have =IF(C2<0.208333333333333,0.208333333333333-C2,0) which works fine but i also want to have to C7 decrease by 1 if the statement is true and remain the same if it is false! hope you understand me. Thanks again "Bob Phillips" wrote in message ... Phil, Here is one way, a bit kludgy but it works in full HH:MM mode Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim fColon As Boolean Dim iPos As Long iPos = InStr(1, TextBox1.Value, ":") fColon = iPos 0 Select Case KeyAscii Case 48 To 57: ' 0-9 Case 58: 'colon If fColon Then KeyAscii = 0 'colon already entered End If Case Else: KeyAscii = 0 Exit Sub End Select If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then If KeyAscii Asc("5") Then KeyAscii = 0 Exit Sub End If ElseIf Len(TextBox1.Text) = 1 Then 'nothing to do ElseIf Len(TextBox1.Text) = 2 Then If KeyAscii Asc(":") Then KeyAscii = 0 Exit Sub End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... How can i set a userform textbox properties so i can enter HH:MM.? Thanks |
|
#4
|
|||
|
|||
|
Phil,
You can't do this withn a formula as you want to change the cell that the formula would be in, so you need event code. I have put this together, and it will reduce C7 every time C2 is changed. It will only reduce C7 if it is greater than zero. Is this what you want? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$C$2" Then If .Value < 0.208333333333333 Then If Range("C7").Value 0 Then Range("C7").Value = Range("C7").Value - 1 End If End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... Excellent, thanks Bob. Can i trouble you for another ? It concerns an If statement. In C8 i have =IF(C2<0.208333333333333,0.208333333333333-C2,0) which works fine but i also want to have to C7 decrease by 1 if the statement is true and remain the same if it is false! hope you understand me. Thanks again "Bob Phillips" wrote in message ... Phil, Here is one way, a bit kludgy but it works in full HH:MM mode Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim fColon As Boolean Dim iPos As Long iPos = InStr(1, TextBox1.Value, ":") fColon = iPos 0 Select Case KeyAscii Case 48 To 57: ' 0-9 Case 58: 'colon If fColon Then KeyAscii = 0 'colon already entered End If Case Else: KeyAscii = 0 Exit Sub End Select If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then If KeyAscii Asc("5") Then KeyAscii = 0 Exit Sub End If ElseIf Len(TextBox1.Text) = 1 Then 'nothing to do ElseIf Len(TextBox1.Text) = 2 Then If KeyAscii Asc(":") Then KeyAscii = 0 Exit Sub End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... How can i set a userform textbox properties so i can enter HH:MM.? Thanks |
|
#5
|
|||
|
|||
|
Tried it Bob with no luck. This is what i get...
C2 04:00 C3 18:00 C4 13:30 C5 8:00 C6 5:30 C7 1:00 Its a timesheet scenario. C4 is total hrs(less break), C5 is basic, c6 is time x1.5 and c7 is time x2. If x2 hrs(C7) increase , x1.5hrs(c6) decrease by same amount. In the above the total hrs(C4) is 13:30, but basic(C5) + x1.5(C6) + x2 (C7) = 14:30. A pain i know, thats why i ask! "Bob Phillips" wrote in message ... Phil, You can't do this withn a formula as you want to change the cell that the formula would be in, so you need event code. I have put this together, and it will reduce C7 every time C2 is changed. It will only reduce C7 if it is greater than zero. Is this what you want? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$C$2" Then If .Value < 0.208333333333333 Then If Range("C7").Value 0 Then Range("C7").Value = Range("C7").Value - 1 End If End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... Excellent, thanks Bob. Can i trouble you for another ? It concerns an If statement. In C8 i have =IF(C2<0.208333333333333,0.208333333333333-C2,0) which works fine but i also want to have to C7 decrease by 1 if the statement is true and remain the same if it is false! hope you understand me. Thanks again "Bob Phillips" wrote in message ... Phil, Here is one way, a bit kludgy but it works in full HH:MM mode Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim fColon As Boolean Dim iPos As Long iPos = InStr(1, TextBox1.Value, ":") fColon = iPos 0 Select Case KeyAscii Case 48 To 57: ' 0-9 Case 58: 'colon If fColon Then KeyAscii = 0 'colon already entered End If Case Else: KeyAscii = 0 Exit Sub End Select If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then If KeyAscii Asc("5") Then KeyAscii = 0 Exit Sub End If ElseIf Len(TextBox1.Text) = 1 Then 'nothing to do ElseIf Len(TextBox1.Text) = 2 Then If KeyAscii Asc(":") Then KeyAscii = 0 Exit Sub End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... How can i set a userform textbox properties so i can enter HH:MM.? Thanks |
|
#6
|
|||
|
|||
|
Have sorted it by shifting cells and using helper cells. thanks anyway
Bob(and all who looked) "Phil" wrote in message . uk... Tried it Bob with no luck. This is what i get... C2 04:00 C3 18:00 C4 13:30 C5 8:00 C6 5:30 C7 1:00 Its a timesheet scenario. C4 is total hrs(less break), C5 is basic, c6 is time x1.5 and c7 is time x2. If x2 hrs(C7) increase , x1.5hrs(c6) decrease by same amount. In the above the total hrs(C4) is 13:30, but basic(C5) + x1.5(C6) + x2 (C7) = 14:30. A pain i know, thats why i ask! "Bob Phillips" wrote in message ... Phil, You can't do this withn a formula as you want to change the cell that the formula would be in, so you need event code. I have put this together, and it will reduce C7 every time C2 is changed. It will only reduce C7 if it is greater than zero. Is this what you want? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Address = "$C$2" Then If .Value < 0.208333333333333 Then If Range("C7").Value 0 Then Range("C7").Value = Range("C7").Value - 1 End If End If End If End With ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... Excellent, thanks Bob. Can i trouble you for another ? It concerns an If statement. In C8 i have =IF(C2<0.208333333333333,0.208333333333333-C2,0) which works fine but i also want to have to C7 decrease by 1 if the statement is true and remain the same if it is false! hope you understand me. Thanks again "Bob Phillips" wrote in message ... Phil, Here is one way, a bit kludgy but it works in full HH:MM mode Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Dim fColon As Boolean Dim iPos As Long iPos = InStr(1, TextBox1.Value, ":") fColon = iPos 0 Select Case KeyAscii Case 48 To 57: ' 0-9 Case 58: 'colon If fColon Then KeyAscii = 0 'colon already entered End If Case Else: KeyAscii = 0 Exit Sub End Select If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then If KeyAscii Asc("5") Then KeyAscii = 0 Exit Sub End If ElseIf Len(TextBox1.Text) = 1 Then 'nothing to do ElseIf Len(TextBox1.Text) = 2 Then If KeyAscii Asc(":") Then KeyAscii = 0 Exit Sub End If End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Phil" wrote in message . uk... How can i set a userform textbox properties so i can enter HH:MM.? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| UserForm to select current month or earlier | Excel Discussion (Misc queries) | |||
| Userform | Excel Worksheet Functions | |||
| Help needed with textbox formatting in Excel 2000 | Excel Discussion (Misc queries) | |||
| How can I change size of the picture without changing chartobject? | Charts and Charting in Excel | |||
Linear Mode
