A funcionalidade abaixo serve para deixar-nos mais à vontade ao inserir datas no MS Excel.
Esta funcionalidade insere barras "/" para separar dd/mm/aa, ou seja, quando digitamos 05021972, será inserido uma formatação de barras na textbox, alterando o número digitado para 05/02/1972.
Public tx As String
Public k As Integer
Sub btnFormat_Click()If Not IsDate(TextBox1) ThenMsgBox "data inválida", vbInformation, "Saberexcel o site das macros"TextBox1.SetFocusLet TextBox1.SelStart = 10
Let TextBox1.Text = ""
TextBox1.SetFocusElseMsgBox "Data válida", vbInformation, "Saberexcel - o site das macros "End If
End Sub
Sub TextBox1_KeyUp (ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim z As StringIf TextBox1 = "" Then TextBox1 = "--/--/----": k = 0: tx = "": TextBox1.SelStart = 0: Exit SubIf KeyCode = 8 ThenIf k <= 1 Then TextBox1 = "--/--/----": TextBox1.SelStart = 0: k = 0: tx = "": Exit Subk = k - 1tx = Left(tx, k)z = Right("--/--/----", 10 - k)If Len(tx) = 3 Then tx = Left(tx, 2): k = 2: z = "/--/----"If Len(tx) = 6 Then tx = Left(tx, 5): k = 5: z = "/----"TextBox1 = tx & zTextBox1.SelStart = kExit SubEnd IfIf k >= 10 Then TextBox1 = Left(TextBox1, 10): Exit Subk = k + 1tx = tx & Mid(TextBox1, k, 1)z = Right("--/--/----", 10 - Len(tx))If Len(tx) = 6 Then tx = Left(tx, 5) & "/" & Right(tx, 1): z = "-": k = k + 1If Len(tx) = 5 Then tx = tx & "/": z = "----": k = k + 1If Len(tx) = 3 Then tx = Left(tx, 2) & "/" & Right(tx, 1): z = "-/----": k = k + 1If Len(tx) = 2 Then tx = tx & "/": z = "--/----": k = k + 1TextBox1 = tx & zTextBox1.SelStart = kEnd Sub
Pode tentar essas soluções:
DATE(RIGHT(A1,4), LEFT(A1),MID(A1,2,2))
=DATE(VALUE(RIGHT(A2,4)), VALUE(LEFT(A2,1)), VALUE(MID(A2,2,2)))
dDate = Dateserial(Mid(strDateTime, 1, 2), _
Mid(strDateTime, 5, 2), _
Mid(strDateTime, 3, 2))
Estude também a opção abaixo:
Sub Worksheet_Change (ByVal Target As Range)Select Case Target.ColumnCase 3, 5 ' columns C & E are 3rd & 5th columnsLet TypedVal = Application.WorksheetFunction. _
Text(Target.Value, "000000")Let NewValue = Left(TypedVal, 2) & "/" & _Mid(TypedVal, 3, 2) & "/" & _Right(TypedVal, 2)Case 4, 6 ' Columns D & F are time columnsLet TypedVal = Application.WorksheetFunction. _Text(Target.Value, "0000")Let NewValue = Left(TypedVal, 2) & ":" & _Right(TypedVal, 2)End SelectIf NewValue > 0 ThenApplication.EnableEvents = FalseLet Target.Value = NewValueLet Application.EnableEvents = TrueEnd IfEnd Sub
Reference::
Shane Devenshire,
Tags: VBA, Excel, Barra, /, Date, format, slash
Nenhum comentário:
Postar um comentário