Thursday, October 26, 2017

Create Regestry Form for Employee Support Excel VBA





Code Userform1

Step1
Private Sub UserForm_Initialize()
a = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
Me.TextBox1 = a + 1000
Me.TextBox2.SetFocus
End Sub


Step2
Private Sub OptionButton1_Click()
Me.TextBox7 = "Male"
Me.TextBox3.SetFocus
End Sub

Private Sub OptionButton2_Click()
Me.TextBox7 = "Female"
Me.TextBox3.SetFocus
End Sub

Step3
Private Sub CommandButton1_Click()
a = Application.GetSaveAsFilename()
Me.TextBox6 = a
Me.Image1.Picture = LoadPicture(Me.TextBox6)
End Sub

Step4
Private Sub CommandButton2_Click()
Dim i As Long
i = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
If Me.TextBox1 = "" Or Me.TextBox2 = "" Then
MsgBox "Please Input Name & Contact"
Else
For X = 1 To 7
Sheet2.Range("A" & i).End(xlToLeft).Offset(1, X - 1) = Me("textbox" & X)
Next X
End If
Unload Me
UserForm1.Show
End Sub

Code Userform2

Step1
Private Sub UserForm_Initialize()
Me.TextBox7.SetFocus
End Sub

Step2
Private Sub CommandButton1_Click()
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))
If Sheet2.Cells(i, 1) = Val(Me.TextBox7) Then
For X = 1 To 4
Me("textbox" & X) = Sheet2.Cells(i, X + 1)
Next X
Me.TextBox5 = Sheet2.Cells(i, "G")
Me.Image1.Picture = LoadPicture(Sheet2.Cells(i, "F"))
End If
Next i
End Sub

Sunday, October 15, 2017

Saturday, October 7, 2017

MS Excell Listbox Search By Textbox VBA



VBA Code

Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub

Private Sub TextBox1_Change()
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Dim i As Long
Me.ListBox1.Clear
On Error Resume Next
For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, 1).Text, a) = Left(Me.TextBox1.Text, a) Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(i, 2).Value
End If
Next i
End Sub

Thursday, October 5, 2017

VLookup Lookup TableArray In Closed Workbook InFolder Excel VBA



VBA Code

Private Sub CommandButton1_Click()
Dim databook As Workbook
Application.ScreenUpdating = False
Set databook = Workbooks.Open(ThisWorkbook.Path & "/" & "Database.xlsm")
For x = 1 To 4
Sheet1.Cells(2, x) = Application.WorksheetFunction.VLookup(Sheet1.Cells(3, "E"), _
Sheets("Sheet1").Range("A:D"), x, 0)
Next x
databook.Close
Application.ScreenUpdating = True
End Sub

Download Workbook 1
Download Workbook 2