[VBA] Error 13 when retrieving data from Unpivoted data using Vlookup, based on the selected slicer arguments in a Userform

K

kpchow77

New Member
Joined
Apr 9, 2023
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Recently, I created a userform with three listboxes that are interdependent on each other. The main purpose of the userform is to allow the user to run a macro after selecting an option/value from the listboxes.
There are two worksheets in the Excel file:
  1. Worksheet "LookupTable"
  • Contains a pivoted table (named "TableSource") containing values for the listboxes.
Master Record.xlsm
ABCDEFGHI
1StageCaseDoc1Doc2Doc3Doc4Doc5Doc6Doc7
21.Stage_11_WithdrawMinuteReply_Letter
31.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEEmailReferral_memoPhoto_affected_areaMinuteLayout+PhotoPhoto_affected_area_2Reply_Letter
41.Stage_13_>2mMinuteReply_Letter
51.Stage_15_Verify ownershipMinute
62.Stage_21_CWTMinuteLayout+PhotoCWT_Record
72.Stage_23_Reply_M2MinuteForm_44
83.Stage_32_Form 44_S3MinuteForm_44
93.Stage_33_ComplyMinuteLayout+Photo_UCLayout+Photo_CReply_Letter
104.Other4_EOT_MinMinute
114.Other5_PO Review M1Minute
124.Other7_Access informationMinute
LookupTable


  1. Worksheet "UnpivotedData"
  • Contains an unpivoted data table (named "UnpivotedData_tbl") retrieved from the pivoted table in Worksheet "LookupTable" using Power Query.
  • Column D is input manually, and the format of all values in column D is number.
  • There are also three slicers.
Master Record.xlsm
ABCDE
1StageCaseDocIdentifierDoc_Type
21.Stage_11_WithdrawMinute1.1.1Doc1
31.Stage_11_WithdrawReply_Letter1.1.2Doc2
41.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEEmail1.2.1Doc1
51.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEReferral_memo1.2.2Doc2
61.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEPhoto_affected_area1.2.3Doc3
71.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEMinute1.2.4Doc4
81.Stage_12_REFER_TO_BD_DEFECTIVE_PIPELayout+Photo1.2.5Doc5
91.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEPhoto_affected_area_21.2.6Doc6
101.Stage_12_REFER_TO_BD_DEFECTIVE_PIPEReply_Letter1.2.7Doc7
111.Stage_13_>2mMinute1.3.1Doc1
121.Stage_13_>2mReply_Letter1.3.2Doc2
131.Stage_15_Verify ownershipMinute1.4.1Doc1
142.Stage_21_CWTMinute1.4.2Doc1
152.Stage_21_CWTLayout+Photo1.4.3Doc2
162.Stage_21_CWTCWT_Record1.4.4Doc3
172.Stage_23_Reply_M2Minute1.5.1Doc1
182.Stage_23_Reply_M2Form_441.6.1Doc2
193.Stage_32_Form 44_S3Minute1.6.2Doc1
203.Stage_32_Form 44_S3Form_441.7.1Doc2
213.Stage_33_ComplyMinute1.7.2Doc1
223.Stage_33_ComplyLayout+Photo_UC1.8.1Doc2
233.Stage_33_ComplyLayout+Photo_C1.8.2Doc3
243.Stage_33_ComplyReply_Letter2.1.1Doc4
254.Other4_EOT_MinMinute2.1.2Doc1
264.Other5_PO Review M1Minute2.1.3Doc1
274.Other7_Access informationMinute4.7.1Doc1
UnpivotedData

1681038792667.png


The data source for the listboxes is from the above slicers.

1681040018090.png

* According to the selection from listbox3 (most right hand side), three macro will be run.

A macro was assigned to a command button, which should run the macro after clicking the button. VBA code was written to retrieve a value from the table in Column D using Vlookup and run a specific macro based on the value in the column.

However, when the macro is run, Error 13 occurs. Debugging the code revealed that the values for listbox1 to listbox3 were successfully obtained, but the value of Column D is empty, and the type of the value is String. Debugging led me to the line with "Select Case CStr(idVal)".

VBA Code: 
Private Sub CommandButton3_Click()
     Dim lb1Val As String
     Dim lb2Val As String
     Dim lb3Val As String
     Dim idVal As Variant
     Dim LookupRange As Range
          
     Set LookupRange = ThisWorkbook.Worksheets("UnpivotedData").Range("A1:D53")
          
     lb1Val = Trim(ListBox1.Value)
     lb2Val = Trim(ListBox2.Value)
          
     If ListBox3.ListIndex <> -1 Then ' Check if an item is selected in ListBox3
         Dim i As Long
         For i = 0 To ListBox3.ListCount - 1 ' Loop through all items in ListBox3
             If ListBox3.Selected(i) Then ' Check if the item is selected
                 If lb3Val = "" Then ' If it's the first selected item, assign the value
                     lb3Val = ListBox3.List(i)
                 Else ' If it's not the first selected item, append the value
                     lb3Val = lb3Val & "," & ListBox3.List(i)
                 End If
             End If
         Next i
     End If
          
     lb3Val = Trim(lb3Val) ' Move the trimming of lb3Val here
     
     ' Assign a default value to idVal
     idVal = ""
     
     ' Debug prints
     Debug.Print "idVal: " & idVal
     Debug.Print "Type of idVal: " & TypeName(idVal)
     Debug.Print "lb1Val: " & lb1Val
     Debug.Print "lb2Val: " & lb2Val
     Debug.Print "lb3Val: " & lb3Val
     Debug.Print "Lookup values: " & lb1Val & ", " & lb2Val & ", " & lb3Val
     Debug.Print "Lookup range: " & LookupRange.Address
          
     If lb1Val <> "" And lb2Val <> "" And lb3Val <> "" Then
     Debug.Print "Lookup values: " & lb1Val & ", " & lb2Val & ", " & lb3Val

         
         ' VLOOKUP function with idVal assigned a value
         idVal = Application.VLookup(Array(lb1Val, lb2Val, lb3Val), LookupRange, 4, True)
         
         If Not IsError(idVal) Then
             Select Case CStr(idVal)
                 Case "151"
                     Call S1_5_1
                     'Add more cases as needed
                 Case Else
                     'Handle case when idVal does not match any of the expected values
             End Select
         Else
             idVal = "Not found" 'Assign a default value if the lookup fails
         End If
          
     Else
         MsgBox "Please select values in all three list boxes"
     End If
          
     If TextBox1.Value <> "" Then
         Shell "explorer.exe " & TextBox1.Value, vbNormalFocus
     End If
End Sub

If more information is required, please let me know! I have tried many ways to solve the error, but I have been unsuccessful. Your help would be very meaningful to me!

Thank you!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Sorry for the typo mistake. It was Case "1.5.1" when ran. Updated code is listed as
VBA Code: 
Private Sub CommandButton3_Click()
     Dim lb1Val As String
     Dim lb2Val As String
     Dim lb3Val As String
     Dim idVal As Variant
     Dim LookupRange As Range
         
     Set LookupRange = ThisWorkbook.Worksheets("UnpivotedData").Range("A1:D53")
         
     lb1Val = Trim(ListBox1.Value)
     lb2Val = Trim(ListBox2.Value)
         
     If ListBox3.ListIndex <> -1 Then ' Check if an item is selected in ListBox3
         Dim i As Long
         For i = 0 To ListBox3.ListCount - 1 ' Loop through all items in ListBox3
             If ListBox3.Selected(i) Then ' Check if the item is selected
                 If lb3Val = "" Then ' If it's the first selected item, assign the value
                     lb3Val = ListBox3.List(i)
                 Else ' If it's not the first selected item, append the value
                     lb3Val = lb3Val & "," & ListBox3.List(i)
                 End If
             End If
         Next i
     End If
         
     lb3Val = Trim(lb3Val) ' Move the trimming of lb3Val here
    
     ' Assign a default value to idVal
     idVal = ""
    
     ' Debug prints
     Debug.Print "idVal: " & idVal
     Debug.Print "Type of idVal: " & TypeName(idVal)
     Debug.Print "lb1Val: " & lb1Val
     Debug.Print "lb2Val: " & lb2Val
     Debug.Print "lb3Val: " & lb3Val
     Debug.Print "Lookup values: " & lb1Val & ", " & lb2Val & ", " & lb3Val
     Debug.Print "Lookup range: " & LookupRange.Address
         
     If lb1Val <> "" And lb2Val <> "" And lb3Val <> "" Then
     Debug.Print "Lookup values: " & lb1Val & ", " & lb2Val & ", " & lb3Val

        
         ' VLOOKUP function with idVal assigned a value
         idVal = Application.VLookup(Array(lb1Val, lb2Val, lb3Val), LookupRange, 4, True)
        
         If Not IsError(idVal) Then
             Select Case CStr(idVal)
                 Case "1.5.1"
                     Call S1_5_1
                     'Add more cases as needed
                 Case Else
                     'Handle case when idVal does not match any of the expected values
             End Select
         Else
             idVal = "Not found" 'Assign a default value if the lookup fails
         End If
         
     Else
         MsgBox "Please select values in all three list boxes"
     End If
         
     If TextBox1.Value <> "" Then
         Shell "explorer.exe " & TextBox1.Value, vbNormalFocus
     End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
VBA in User Form. Xlookup formula for Dependent Combo Box.
Replies
8
Views
223
Kscoof
K
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
166
Candyman8019
C
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
155
krunal123
K
D
  • Question Question
Error Retrieving data with XLOOKUP after using VBA to copy to source cells.
Replies
6
Views
364
Alex Blakenburg
A
Guinaba
  • Solved
VBA Function to read values from slicer
Replies
2
Views
861
Guinaba
Guinaba

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top