Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim ws As Worksheet
Dim c As Range
Dim FirstAddress As String
Set ws = Sheets("Employees")
Set LastRow = ws.Range("AE65536").End(xlUp)
Unload Me
Application.ScreenUpdating = False
ws.Activate
With ws.Range([AE8], [AE65536].End(xlUp))
Set c = .Find(TextBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
MsgBox "Employee number already exists" & vbCrLf & _
"Please try again", vbCritical + vbOKOnly, "User Exists"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
UserForm2.Show
End If
End With
Sheets("User Form").Activate
Application.ScreenUpdating = True
With LastRow
' Employee #
.Offset(1, 0) = TextBox1.Value
' Employee Name
.Offset(1, 1) = TextBox2.Value
' Start Date
.Offset(1, 2) = TextBox3.Value
End With
' Add Exempt Status
If OptionButton1 = True Then LastRow.Offset(1, 3) = "Exempt"
If OptionButton2 = True Then LastRow.Offset(1, 3) = "NonExempt"
If OptionButton3 = True Then LastRow.Offset(1, 3) = "PartTime"
End Sub
i have this macro that makes a table of employees. to put the person name like this Joe SMoe
can i use the formula listed below in macro to change it to Smoe, Joe
=RIGHT(AF9,LEN(AF9)-FIND(" ",AF9))&", "&LEFT(AF9,FIND(" ",AF9)-1)
Private Sub CommandButton1_Click()
Dim LastRow As Object
Dim ws As Worksheet
Dim c As Range
Dim FirstAddress As String
Set ws = Sheets("Employees")
Set LastRow = ws.Range("AE65536").End(xlUp)
Unload Me
Application.ScreenUpdating = False
ws.Activate
With ws.Range([AE8], [AE65536].End(xlUp))
Set c = .Find(TextBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
MsgBox "Employee number already exists" & vbCrLf & _
"Please try again", vbCritical + vbOKOnly, "User Exists"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
UserForm2.Show
End If
End With
Sheets("User Form").Activate
Application.ScreenUpdating = True
With LastRow
' Employee #
.Offset(1, 0) = TextBox1.Value
' Employee Name
.Offset(1, 1) = TextBox2.Value
' Start Date
.Offset(1, 2) = TextBox3.Value
End With
' Add Exempt Status
If OptionButton1 = True Then LastRow.Offset(1, 3) = "Exempt"
If OptionButton2 = True Then LastRow.Offset(1, 3) = "NonExempt"
If OptionButton3 = True Then LastRow.Offset(1, 3) = "PartTime"
End Sub
i have this macro that makes a table of employees. to put the person name like this Joe SMoe
can i use the formula listed below in macro to change it to Smoe, Joe
=RIGHT(AF9,LEN(AF9)-FIND(" ",AF9))&", "&LEFT(AF9,FIND(" ",AF9)-1)