Aug 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.
=XLOOKUP(A1,Sheet1!A:A,Sheet1!A:V)
Help with a solution to this problem would be greatly appreciated.
Thank you in advance.
Aug 24 2020 05:25 AM
Solution@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.
Aug 24 2020 05:26 AM
Aug 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.
Aug 24 2020 05:25 AM
Solution@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.