Hi all,
I have a userform with the following code for a command button:
Private Sub CommandButtonOK_Click()
Dim sFormFamilyName As String
Dim sFormGiveName As String
Dim sFormFederation As String
Dim sFormConfederation As String
sFormFamilyName = UCase(Me.TextBoxFamilyName.Value)
sFormGivenName = Me.TextBoxGivenName.Value
sFormFederation = Me.ComboBoxFederation.Value
sFormConfederation = Me.ComboBoxConfederation.Value
AddNationalReferee sFormFamilyName, sFormGivenName, sFormFederation, sFormConfederation
Me.Tag = "OK"
Me.Hide
End Sub
Now when I run the form and hit the OK button I get the error message "Invalid use or property". I've been searching and trying different things for 2 days. Just when I change the procedure name it runs once or twice and then goes back to the error message. It is probably a minor thing I'm overlooking, but I just don't know anymore at the moment. I hope someone can help.
The procedure I'm calling is located in a User Module and looks like this:
Public Sub AddNationalReferee(ByVal sFamilyName As String, ByVal sGivenName As String, ByVal sFederation As String, Optional ByVal sConfederation As String = "CEV")
Dim sDisplayName As String
Dim iEmptyRow As Integer
Dim oCell As Range
sFamilyName = UCase(sFamilyName)
sDisplayName = sFamilyName & " " & sGivenName
MsgBox sFamilyName & vbCrLf & sGivenName & vbCrLf & sDisplayName & vbCrLf & sCountry & vbCrLf & sConfederation
'Add national ref to Referee_list
'Find first empty row on Referee_list worksheet
iEmptyRow = 0
For Each oCell In Worksheets("Referee_list").Range("A:A").Cells
If IsEmpty(oCell) Then
iEmptyRow = oCell.Row
Exit For
End If
Next
If iEmptyRow > 0 Then
'Add national ref to Referee_list
Worksheets("Referee_list").Cells(EmptyRow, 1) = sDisplayName
Worksheets("Referee_list").Cells(EmptyRow, 2) = sGivenName
Worksheets("Referee_list").Cells(EmptyRow, 3) = sFamilyName
Worksheets("Referee_list").Cells(EmptyRow, 5) = sFederation
Worksheets("Referee_list").Cells(EmptyRow, 6) = sConfederation
Worksheets("Referee_list").Cells(EmptyRow, 10) = "Nat."
Worksheets("Referee_list").Cells(EmptyRow, 11) = LCase(sGivenName & Left(sFamilyName, 1))
Worksheets("Referee_list").Cells(EmptyRow, 13).Formula = "=CONCATENATE(LEFT(B" & EmptyRow & ";1);LEFT(C" & EmptyRow & ";1))"
Else
MsgBox "No more room in the list to add a national referee to the list of referees"
End If
'Add national ref to control sheet
'Find first empty row on Referee_list worksheet
iEmptyRow = 0
For Each oCell In Worksheets("Control sheet").Range("A10:A32").Cells
If IsEmpty(oCell) Then
iEmptyRow = oCell.Row
Exit For
End If
Next
If iEmptyRow > 0 Then
'Add national ref to control sheet
Worksheets("Control sheet").Cells(EmptyRow, 1) = sDisplayName
Else
MsgBox "No more room in the control sheet to add a national referee"
End If
'When this works, in reset workbook, remove all local refs
End Sub
Thanks for your time and support.
KR, Bas
Bookmarks