08-23-2020 10:54 PM
08-23-2020 10:54 PM
I have the current MS Office 365, and am using Excel.
I created a button on Sheet1 to click [Change Member Details]
On click, the fields on the form are initialized, with only those to use for the search being made visible on the "UpdateMemberForm".
The user form prompts for required search input (member number, or a phone number, or a combination of name and street address).
I entered the member number, and clicked the [Find Member] button on the "UpdateMemberForm".
The "WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V")" was to search Sheet1, column A, and return the string of data to an array "GetMemberData" that I wanted to subsequently display in all the fields on the user form.
The data was to be simply read, or allow for record change/correction and written back to sheet1, or deleted if [Delete Member] button was clicked.
The member numbers are in Sheet1 Column A.
Each record row is contained within columns A through V.
As a test, I used a member number that I knew was in Sheet1 column A.
This is the code I am using that returns the error: Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class, highlighting the XLookup code line.
' >>>>> UPDATE MEMBER <<<<< ' Sub RectangleRoundedCorners3_Click() Set Sheet_Name = ThisWorkbook.Sheets("Sheet1") Dim Array_Count As Long Dim SetSwitch As Boolean Dim GetMemberData As Variant ReDim GetMemberData(0 To 22) As Variant Load UpdateMemberForm UpdateMemberForm.Show End Sub __________________________________________________ ' ' >>>>> Begin User Form Data Input <<<<< ' ========================== Private Sub I_Mem_Num_Enter() I_Mem_Num.BackColor = vbYellow I_Suburb.Visible = False I_Postcode.Visible = False I_Birth.Visible = False I_Age.Visible = False I_Comment.Visible = False I_Email.Visible = False I_Mem_Status.Visible = False I_Mem_Receipt.Visible = False I_Mem_Date_Paid.Visible = False I_Joining_Date.Visible = False I_Film_Fee.Visible = False I_Film_Receipt.Visible = False I_Film_Date_Paid.Visible = False Mem_Category.Visible = False Btn_Gender_Male.Visible = False Btn_Gender_Female.Visible = False Btn_Gender_Other.Visible = False Btn_Vote_Yes.Visible = False Btn_Vote_No.Visible = False Btn_Photo_Yes.Visible = False Btn_Photo_No.Visible = False End Sub Private Sub I_Given_Enter() I_Mem_Num.BackColor = vbWhite I_Given.BackColor = vbYellow End Sub Private Sub I_Surname_Enter() I_Given.BackColor = vbWhite I_Surname.BackColor = vbYellow End Sub Private Sub I_Phone_Enter() I_Surname.BackColor = vbWhite I_Phone.BackColor = vbYellow End Sub Private Sub I_Phone_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Stop_Switch = "Go" Do Until Stop_Switch = "Stop" If IsNumeric(I_Phone.Text) = False Then I_Phone.Text = InputBox("Please enter 8 or 10 digit phone number ") ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 8 Then I_Phone.Text = Format(I_Phone.Text, "0000-0000") Stop_Switch = "Stop" Exit Do ElseIf IsNumeric(I_Phone.Text) And Len(I_Phone.Text) = 10 Then I_Phone.Text = Format(I_Phone.Text, "0000-000-000") Stop_Switch = "Stop" Exit Do Else Stop_Switch = "Go" End If Loop End Sub Private Sub I_Street_Enter() I_Email.BackColor = vbWhite I_Street.BackColor = vbYellow End Sub Private Sub L_Mem_Num_Click() End Sub __________________________________________________ Private Sub Btn_Find_Member_Click() SetSwitch = True ReDim GetMemberData(0 To 21) As Variant Do Until SetSwitch = False If I_Mem_Num.Value > 0 Then MsgBox I_Mem_Num GetMemberData = WorksheetFunction.XLookup(I_Mem_Num, "A:A", "A:V") MsgBox GetMemberData SetSwitch = False Exit Do
As a further test, I created Sheet2 using A1 as the record number input to search, with the Xlookup formula in Sheet2, A3. It read the data on sheet1, and returned the array of values from Sheet1 A to V, and worked perfectly.
Help with a solution to this problem would be greatly appreciated.
Thank you in advance.
08-24-2020 05:25 AMSolution
@Debs_au If you are doing an XLookup on the ID it will fail because you are getting the value from a textbox and the data in your worksheet contains numerical ID's. This means XLookup will return #N/A, which results in a run-time error. In addition you must change your declaration of GetMemberData so XLOOKUP can add the found values to it. This appears to work:
Private Sub Btn_Find_Member_Click() SetSwitch = True Dim GetMemberData As Variant Do Until SetSwitch = False If I_Mem_Num.Value > 0 Then MsgBox I_Mem_Num GetMemberData = WorksheetFunction.XLookup(I_Mem_Num * 1, Range("A:A"), Range("A:V")).Value ' MsgBox GetMemberData SetSwitch = False Exit Do ElseIf I_Phone > 0 Then GetMemberData = WorksheetFunction.XLookup(I_Phone, "H:H", "A:V") SetSwitch = False Exit Do ElseIf IsEmpty(I_Given) = False And IsEmpty(I_Surname) = False And IsEmpty(I_Street) = False Then GetMemberData = WorksheetFunction.XLookup(I_Given & I_Surname & I_Street, "F:F" & "G:G" & "J:J", "A:W") SetSwitch = False Exit Do Else SetSwitch = True MsgBox "There is not enough unique information to search. Please use [Member number] OR [Phone number] OR [Given-Name and Surname and Street]" End If Loop ' >>>>> Make Text Boxes Visible <<<<< I_Suburb.Visible = True I_Postcode.Visible = True I_Birth.Visible = True I_Age.Visible = True I_Comment.Visible = True I_Email.Visible = True I_Mem_Status.Visible = True I_Mem_Receipt.Visible = True I_Mem_Date_Paid.Visible = True I_Joining_Date.Visible = True I_Film_Fee.Visible = True I_Film_Receipt.Visible = True I_Film_Date_Paid.Visible = True Mem_Category.Visible = True Btn_Gender_Male.Visible = True Btn_Gender_Female.Visible = True Btn_Gender_Other.Visible = True Btn_Vote_Yes.Visible = True Btn_Vote_No.Visible = True Btn_Photo_Yes.Visible = True Btn_Photo_No.Visible = True ' ' >>>>> Put Retrieved Data into Text Boxes <<<<<< I_Mem_Num.Value = GetMemberData(1, 1) I_Mem_Status.Value = GetMemberData(1, 2) I_Mem_Receipt.Value = GetMemberData(1, 3) I_Mem_Date_Paid.Value = GetMemberData(1, 4) Mem_Category.Value = GetMemberData(1, 5) I_Given.Value = GetMemberData(1, 6) I_Surname.Value = GetMemberData(1, 7) I_Phone.Value = GetMemberData(1, 8) I_Email.Value = GetMemberData(1, 9) I_Street.Value = GetMemberData(1, 10) I_Suburb.Value = GetMemberData(1, 11) I_Postcode.Value = GetMemberData(1, 12) If GetMemberData(1, 13) = "Male" Then Btn_Gender_Male.Value = True ElseIf GetMemberData(1, 13) = "Female" Then Btn_Gender_Female.Value = True Else: Btn_Gender_Other.Value = True End If I_Birth.Value = GetMemberData(1, 14) I_Age.Value = GetMemberData(1, 15) I_Joining_Date.Value = GetMemberData(1, 16) If GetMemberData(1, 17) = "Yes" Then Btn_Vote_Yes.Value = True Else: Btn_Vote_No.Value = True End If If GetMemberData(1, 18) = "Yes" Then Btn_Photo_Yes.Value = True Else: Btn_Photo_No.Value = True End If ' Column S (19) >Special Needs = not used I_Comment.Value = GetMemberData(1, 20) I_Film_Fee.Value = GetMemberData(1, 21) I_Film_Receipt.Value = GetMemberData(1, 22) I_Film_Date_Paid.Value = GetMemberData(1, 23) End Sub
Except for the last statement as it appears only 22 columns are in the source range of the XLOOKUP.
08-24-2020 05:26 AM
08-24-2020 07:49 AM
Thank you SOOOO much! I've been stressing over this for days, and you've explained it so I understand the problem. Also, thank you for showing the solution to getting the returned values from the array into the text-boxes. So much appreciated.