Include or Exclude employee names with their employee id in a dropdown based on a criteria,VBA Teacher Sourav,Kolkata 08910141720

Sub loadcmb()
Dim sdsheet As Worksheet
Set sdsheet = ThisWorkbook.Sheets("Sortsheet")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

SortForm.ComboBox1.Clear

If SortForm.CheckBox1 = True Then


For x = 2 To lr

If SortForm.OptionButton1 = True Then



SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 1) & " - " & Sortsheet.Cells(x, 2)


Else

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 2) & " - " & Sortsheet.Cells(x, 1)

End If


Next x

Else


For x = 2 To lr

If SortForm.OptionButton1 = True Then

If UCase(Cells(x, 14)) = "A" Then

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 1) & " - " & Sortsheet.Cells(x, 2)

End If

Else

If UCase(Cells(x, 14)) = "A" Then

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 2) & " - " & Sortsheet.Cells(x, 1)

End If


End If


Next x


End If


End Sub

Randomly choose between two given characters (for example A and I) and format the row based on that choice using excel,Excel Teacher Sourav,Kolkata 08910141720

=CHAR(CHOOSE(RANDBETWEEN(1,2),65,73))

Or

=CHOOSE(RANDBETWEEN(1,2),"A","I")

Source:https://exceljet.net/formula/random-text-values

Now select the whole section of data ,go to home,conditional formatting ,manage rules,new rule

select use a formula to determine which cells to format

in the formula box write

=$N2="I"

then go to format,under fill tab make sure grey is selected,so that people with
status I (inactive ) have their rows greyed out

Load Combobox with Employee ID and Names on initialization of userform ,VBA Teacher Sourav,Kolkata 08910141720

Private Sub UserForm_Initialize()
Call loadcmb
End Sub


Sub loadcmb()
Dim sdsheet As Worksheet
Set sdsheet = ThisWorkbook.Sheets("Sortsheet")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

SortForm.ComboBox1.Clear

For x = 2 To lr

If SortForm.OptionButton1 = True Then

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 1) & " - " & Sortsheet.Cells(x, 2)


Else

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 2) & " - " & Sortsheet.Cells(x, 1)

End If


Next x

End Sub

Create random but unique numbers for employee id column in a given range using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub fillcolumnwithuniquerandomnumbers()

Sheets("Sortsheet").Select
 Dim cell As Range
    Dim rng As Range
    Dim High As Long, Sample As Long
   
  
    'High = Application.InputBox("Enter population total", Type:=1)
    'Sample = Application.InputBox("Enter the Sample Size", Type:=1)
    High = 500
    Low = 1
   
    'Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
    Set rng = Range("A2:A500")
   
    For Each cell In rng.Cells
        If WorksheetFunction.CountA(rng) = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until rng.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
    rng.Select
    Selection.NumberFormat = "@" 'changing the format to text so that cells can contain leading zeros
   
    For Each cell In rng.Cells
    cell.Value = "000" & cell.Value
   
   
   
    Next
   
   
   
End Sub

Sort excel data from an userform using column names(sort key) in option button using VBA,VBA Teacher Sourav,Kolkata 08910141720

The code behind sort button

Private Sub CommandButton1_Click()

Dim sdsheet As Worksheet
Set sdsheet = ThisWorkbook.Sheets("Sortsheet")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

Set sortarea = Range("A2:L" & lr)
If Me.OptionButton1 = True Then

Set sortcol = Range("A2:A" & lr)
ElseIf Me.OptionButton2 = True Then
Set sortcol = Range("G2:G" & lr)

Else

End If






sdsheet.Sort.SortFields.Clear
sdsheet.Sort.SortFields.Add Key:=sortcol _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sdsheet.Sort
    .SetRange sortarea
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub


Source:https://www.youtube.com/watch?v=6Ju6B99eleo&index=2&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U