Applying same code to multiple fields

[gastoff]

I am using the following code to format the entry of time into a text field:

Private Sub txtTime1_AfterUpdate()
With txtTime1
   If IsDate(.Value) Then
        .Value = Format(.Value, "HH:MM AM/PM")
    Else
        MsgBox "Input time as HH:MM"
        .Value = ""
        Cancel = True
    End If
End With
End Sub

I have 30 more text fields (txtTime1,2,3, etc) that need the same formatting to be done after the field is updated. Can this be done with a loop? Create a module that can be applied to each field as it is adjusted? How would one go about doing this other than copy/pasting and adjusting the code for each field?

 

[L. Howard]

Is txtTime1, txtTime2, txtTime3... etc. each a single cell?

What is a 'text field'?

How does txtTime1 relate to a 'text field'?

If txtTime1 (and others) are a single cell, are they in a row or a column or scattered about the worksheet?

It may be possible to use a Change_Event macro to include only the cells that contain a txtTime and then the "Changed" txtTime becomes Target and something like this may work.

Howard

UN-TESTED

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCells As Range
    Set MyCells = Range("B1:B20") '/ Cells 1 TO 20, column B only
    
 If Not Application.Intersect(MyCells, Range(Target.Address)) _
           Is Nothing Then

  With Target
      If IsDate(.Value) Then
        .Value = Format(.Value, "HH:MM AM/PM")
      Else
        MsgBox "Input time as HH:MM"
        .Value = ""
        Cancel = True
    End If
  End With

End If

End Sub

 

[gastoff]

Apologies for not mentioning it in my post: The fields are part of my userform. I have 32 text fields on the userform that will be used to input times. The code I posted was a test code I used on one cell to make sure it was formatting the entry properly after update.
Now that my code is working for that one text field object (txtTime1) the way I want, I need to apply it to the other text fields on my userform. Is there a way to go about doing this?

 

[L. Howard]

Okay, I believe I got the idea. However, I'm not sure how to go about that.

So the best I can offer is perhaps the UserForm_Initialize to set the date format for the fields.

Private Sub UserForm_Initialize()

 'code here
 'but don't know how it should be written

End Sub

Sorry, I'm not much help.

Howard

 

[gastoff]

If the various fields are all formatted at date/time when the form is initialized, how would a check be done to make sure they are inputting in the correct format? The only way I know to do it is by using the code I posted earlier tied to the object's AfterUpdate event.
Will I need to copy the code for each of my 32 fields? Or is there a way to have multiple objects key to the same AfterUpdate coding?

I'm so new to VBA that I don't even know if I'm making sense anymore

 

[L. Howard]

I was hoping someone with Userform expertise would jump in and offer a solution. I'll try, but am short on confidence in the Userform arena.

The code, below the text balloon, is for an example userform with twelve fields. It was written by a pro for me as a reference, so the code is correct, and actually works fine with the userform. It is not something you can use with your userform, rather its a correctly written example you may be able to refer to.

There is a bold line of text referring to "CLng or CDbl or CInt" Long, Double or Integer and how to return a real number to the sheet.

In this balloon are some examples of how to return a date using CDate and DateValue.

I suspect you would use CDate for your fields, as you see here for this field which is set with Clng, CDate instead of CLng.

Dest.Offset(, 8) = CLng(TextBox6.Value)

Howard

CDate will convert a number or text to a date.

CDate(41035) 'Converts to a date of 5/6/2012
CDate("1/15/2014") 'Converts to a date of 1/15/2014


DateValue will convert date in text format (only) to a date.

DateValue("1/15/2014") 'Converts to a date of 1/15/2014
DateValue(41035) 'Throws a mismatch error

Private Sub CommandButton1_Click()
Dim Dest As Range

With Sheets("Master")
    Set Dest = .Cells(Rows.Count, 1).End(xlUp)(2)
    If Len(TextBox1) > 0 And Len(TextBox2) > 0 Then
    
        'textbox or a combobox always contains text even if it is a number
       [B] 'To get a real number into the sheet you have to change it with CLng or CDbl or CInt[/B]        'Numeric value???
        Dest = CLng(TextBox1.Value)
        
        Dest.Offset(, 1) = TextBox2.Value
        Dest.Offset(, 2) = ComboBox1.Value
        Dest.Offset(, 3) = ComboBox2.Value
        Dest.Offset(, 4) = ComboBox3.Value
        Dest.Offset(, 5) = TextBox3.Value
        Dest.Offset(, 6) = TextBox4.Value
        Dest.Offset(, 7) = TextBox5.Value
        
        'Numeric value???
        Dest.Offset(, 8) = CLng(TextBox6.Value)
        
        Dest.Offset(, 9) = TextBox7.Value
        Dest.Offset(, 10) = TextBox8.Value
        Dest.Offset(, 11) = TextBox9.Value
    Else
        MsgBox "No entries for Name and Employee ID"
        Exit Sub
    End If
    Unload Me
End With
End Sub

Private Sub UserForm_Initialize()
Dim strTitle As String, strMF As String, strType As String
Dim varTitle As Variant, varMF As Variant, varType As Variant

strTitle = "Worker,Clerical,Exhibition"
strMF = "Female,Male"
strType = "Full Time,Contract,Other"

varTitle = Split(strTitle, ",")
varMF = Split(strMF, ",")
varType = Split(strType, ",")

ComboBox1.List = varTitle
ComboBox2.List = varType
ComboBox3.List = varMF
End Sub

 

[gastoff]

Thanks for stepping out of your comfort zone to try helping a greenie like myself. I'll work on adapting the code you provided to what I have and see if it works. If all else fails, I'll just buckle down and set up individual codes for each field.
Thanks a bunch!

 

[L. Howard]

You could post a link to an example workbook using one of the link utilities, I use Drop Box, but there are others. (You cannot do attachments in this forum)

With a look-see it may be a bit clearer as what needs to be done. I'd be glad to take a look.

Re explain what you are shooting for referencing cell, rows, columns, TextBox and the such...

Howard

 

[gastoff]

I had a busy week, but I am finally back to working on this project. I uploaded my workbook to dropbox and you can find the file here:
https://www.dropbox.com/s/g5374w5mibbd5zd/Timecard.xlsm?dl=0

It is still a major work in progress...
What I want it to eventually do:
After clicking SUBMIT, for each employee selected from dropdown box, post the data they have entered into the 4 cells into a row on my JobLog page. So, if only 1 employee is entered, 1 row is poulated. 2 employees, 2 rows, etc.
The same thing will be done for selected equipment. Each piece selected receiving their own row in the JobLog. (Later, I will extract this data to do billing, payroll, etc.)

The question I had earlier concerned the formating of the time in each of the text fields that will be used for entering time. The code I got to work checks the number entered and displays them in Date/Time format (If it is valid), and give the user an error message if they did not enter their time in the correct format. Since the coding is initiated when the field is updated, I do not know how to have this same check run regardless of which field is updated. I could make 60 different "_AfterUpdate()" codes, but I was hoping there was a more streamlined way to do it.
Any insight?
As you can tell, I am still very new to VBA...hoping someone with more expertise can give me some direction!
-Jake