Hello
I have this following function in a module called (MyFunctions):
Sub AddPlayer(GivenPlayerName As String, GivenPrefixContactNumber As String, GivenContactNumber As String, GivenCompanyName As String)
UnProtectSheet PlayersList
Players_List_SetNextEntry
PlayersList.Cells(Players_List_NextEntry, Players_List_No_Column).Value = Players_List_NextEntry - (Players_List_FirstEntry - 1)
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerName_Column).Value = GivenPlayerName
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerContact_Column).Value = "(" + GivenPrefixContactNumber + ") " + GivenContactNumber
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerCompany_Column).Value = GivenCompanyName
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Locked = False
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.Delete
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=GetGroups
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.IgnoreBlank = True
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.InCellDropdown = True
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.InputTitle = ""
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.ErrorTitle = "Error Inputting"
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.InputMessage = ""
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.ErrorMessage = "Only groups that are available from the drop list are allowed in this field"
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.ShowInput = True
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.ShowError = True
ProtectSheet PlayersList
End Sub
So, When I call this function (AddPlayer) from within the subroutine Workbook_Open() then it works just fine, however when I call it from MyFormButton_OnClick() subroutine then it gives me the error message that you can find attached in this post as an image format. So, when I hit the debug button it hightlights and takes me to the following line of this (AddPlayer) function:
PlayersList.Cells(Players_List_NextEntry, Players_List_PlayerGroup_Column).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=GetGroups
I don't get it, what's wrong here?
Note: The (GetGroups) in that line of code above is a function that gets the available groups names that are to be selected from for each player name.
Bookmarks