Excel VBA Help

I hope you all are doing great; what type of content are you willing to see?

Website URL for easier to follow tutorial:
https://vbaexcelhelp.000webhostapp.com/index.php/2019/10/22/create-form-with-add-delete-users/

Facebook is bad at rendering code even when I try, but this code will work very nicely in an environment where you need to insert data, delete, changing works the same way as insert except that you will be inserting in to an existing row instead of a new row, very easy to do, this little code search from the combo box if a user is selected and then clicked away (afterUpdate). If there is an existing user, you can add a message box to warn users but my assumption is that a new key is better instead of names, here is the code and screenshot including pictures of the setup:

Private Sub cmbName_AfterUpdate()
Dim i As Long, lRow As Long
Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet1")
' find last row
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
' Loop through to see if information can be found from selection
For i = 2 To lRow
' trim ensure there are no extra blank spaces
If Trim(Me.cmbName.Value) = Trim(ws.Cells(i, 2).Value) Then
' load user information
Me.txtDate.Value = ws.Cells(i, 1).Value
Me.cmbName.Value = ws.Cells(i, 2).Value
Me.txtPass.Value = ws.Cells(i, 3).Value
Exit For ' exit the loop if the first name is found
' additional condition statements can be ran to detect
' users with the same name if necesssary.
End If
Next i
End Sub

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet1")
' find last row
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1 ' select the next empty field
' check that user has entered some values, all 3 fields required
If Me.cmbName.Value "" And Me.txtDate.Value "" And Me.txtPass.Value "" Then
' insert the date info
ws.Cells(lRow, 1).Value = Me.txtDate.Value
' insert the name info
ws.Cells(lRow, 2).Value = Me.cmbName.Value
' insert the pass info
ws.Cells(lRow, 3).Value = Me.txtPass.Value
MsgBox "User Name: " & Me.cmbName.Value & " has been created", vbOKOnly, "New User Entered"
Else
MsgBox "You need to fill in all fields.", vbInformation, "Empty Fields"
End If
' clear fields
Me.cmbName.Clear '

Navigate through rows & specified column, then populate based on the target value that contains some sort of value (helpful for gathering data on that contains blank cells):

Sub populator()
Dim i As Long, x As Long, l As Long
Dim ws As Worksheet
Set ws = Application.Worksheets("Sheet1")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
l = 1
For i = 2 To lRow
For x = 1 To 6
If ws.Cells(i, x).Value "" Then
l = l + 1
Range("A" & l).Value = ws.Cells(i, x).Value
End If
Next x
Next i
End Sub

A website is coming for full and colored tutorials. Please stay tuned. For now; get Windows username logons like this:

Dim userName as string
userName = Environ("UserName")

' You can then loop through authenticated users:

Select Case userName
Case "User1"
msgbox "Welcome User #1", vbOkOnly, "Welcome!"
Case "User2"
msgbox "Welcome User #2", vbOkOnly, "Welcome!"
End Select

Force users to activate macros; display a sheet called "Macros" with a message, once the users clicks the enable macros it will open all of the other sheets except "Macros". This is important so that users cannot modify the existing file or if you need them to use a form instead of manually doing entries.

The second code can be placed in a button to close your form and then save it or it will hide the and mark the document as unsaved, you can disable the save command and save at the very end of all runs.

Keep in mind that it always returns to the "Macro" page but will revert back to normal as long as it detects that you have macro enabled on the document.

Place code in the "ThisWorkbook" by double clicking it.

Working with Arrays:

' define valid entries.
Dim code As Variant ' define the array
code = Array("A", "B", "C", "D", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "N/A", "NA", "NCR")
badCode = "0"
For i = 0 To 16 Step 1
If VBA.UCase(Me.txtNext.Value) = code(i) Then
badCode = "1" ' setting a value if condition is met
Exit For ' end if needed
End If
Next i

Finding the last row is easy and adding data to it.

Private Sub cmdClicker_Click()
Dim lastRow As Long
' selects the next empty cell at column 'A'
lastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
End Sub