Hi everyone, Would appreciate it if I can get some help with this.
I'm basically doing a data entry form for guest registration for a dinner.
However, I have to ensure guest names cannot have duplicate entries, and if possible, guests are registered in the cell's next to the seat number that have already been entered, rather than adding new entries for seat and name everytime. The reason being, I'm linking it to another sheet which is the floor seating plan. Chairs turn blue when a guest's name is registered to the seat number
Would love some help to return the address found by countif (a) and have the guest's name (Combobox1.value) transferred to the cell next to it
Private Sub CommandButton1_Click()
Dim a As Long, x As Long
a = Application.WorksheetFunction.CountIf(Sheet3.Range("A:A"), Me.ComboBox2.Text)
'Checks for duplicate entries for the seat specified in combobox 2
x = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1
'Total amount of cells + 1
C = Application.WorksheetFunction.CountIf(Sheet3.Range("B:B"), Me.ComboBox1.Text)
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And a = 0 And C = 0 Then
Sheet3.Range("a" & x).Value = Me.ComboBox2.Text
Sheet3.Range("b" & x).Value = Me.ComboBox1.Text
Range("A2:A108").Select
ActiveWorkbook.Worksheets("A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("A2"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("A").Sort
.SetRange Range("A2:B108")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ElseIf Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And C = 0 And a <> 0 Then
MsgBox "Seat is already registered. Registering guest"
'Find address of countif for seats and transfer me.combobox1.text to the cell next to it
'Loc = Sheet3.Cells("a").Address
'Sheet3.Range("loc").Value = Me.ComboBox1.Text
End If
If C <> 0 Then
MsgBox "Guest is already registered"
End If
End Sub
I'm basically doing a data entry form for guest registration for a dinner.
However, I have to ensure guest names cannot have duplicate entries, and if possible, guests are registered in the cell's next to the seat number that have already been entered, rather than adding new entries for seat and name everytime. The reason being, I'm linking it to another sheet which is the floor seating plan. Chairs turn blue when a guest's name is registered to the seat number
Would love some help to return the address found by countif (a) and have the guest's name (Combobox1.value) transferred to the cell next to it
Private Sub CommandButton1_Click()
Dim a As Long, x As Long
a = Application.WorksheetFunction.CountIf(Sheet3.Range("A:A"), Me.ComboBox2.Text)
'Checks for duplicate entries for the seat specified in combobox 2
x = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1
'Total amount of cells + 1
C = Application.WorksheetFunction.CountIf(Sheet3.Range("B:B"), Me.ComboBox1.Text)
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And a = 0 And C = 0 Then
Sheet3.Range("a" & x).Value = Me.ComboBox2.Text
Sheet3.Range("b" & x).Value = Me.ComboBox1.Text
Range("A2:A108").Select
ActiveWorkbook.Worksheets("A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("A2"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("A").Sort
.SetRange Range("A2:B108")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ElseIf Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And C = 0 And a <> 0 Then
MsgBox "Seat is already registered. Registering guest"
'Find address of countif for seats and transfer me.combobox1.text to the cell next to it
'Loc = Sheet3.Cells("a").Address
'Sheet3.Range("loc").Value = Me.ComboBox1.Text
End If
If C <> 0 Then
MsgBox "Guest is already registered"
End If
End Sub