VBA Compile Error for each control variable must be variant or object
I have a vba code I am having issues with. The way it’s supposed to function is a userform opens when the button is pressed. In the UF, there are four checkboxes: A only, B only, C only, and D only each of which should be selected if the user’s data will contain that column (know as the master column). If so, the user is to enter the column letter it starts at in the text box next to it. Once the checkbox is clicked, a list of the potential column letters for the subcategories appears so the user can select where the data from the master column letter should be outputted into. Each Master column has a range it could fall into. When I run the code, nothing outputs.
Thanks in advance for any help!
I attached the sample document below. I also include the code that runs but doesn’t process any data. then i commented the updated one that gives me the compile error:
I have a vba code I am having issues with. The way it’s supposed to function is a userform opens when the button is pressed. In the UF, there are four checkboxes: A only, B only, C only, and D only each of which should be selected if the user’s data will contain that column (know as the master column). If so, the user is to enter the column letter it starts at in the text box next to it. Once the checkbox is clicked, a list of the potential column letters for the subcategories appears so the user can select where the data from the master column letter should be outputted into. Each Master column has a range it could fall into. When I run the code, nothing outputs. Thanks in advance for any help! Codes for the userform:Private Sub chboxA_Click() TextBox1.Enabled = chboxA.Value ‘ Toggle the enabling of TextBox1 based on chboxA’s state lstA.Visible = chboxA.Value ‘ Toggle the visibility of lstA based on chboxA’s stateEnd Sub Private Sub chboxB_Click() TextBox2.Enabled = chboxB.Value ‘ Toggle the enabling of TextBox2 based on chboxB’s state lstB.Visible = chboxB.Value ‘ Toggle the visibility of lstB based on chboxB’s stateEnd Sub Private Sub chboxC_Click() TextBox3.Enabled = chboxC.Value ‘ Toggle the enabling of TextBox3 based on chboxC’s state lstC.Visible = chboxC.Value ‘ Toggle the visibility of lstC based on chboxC’s stateEnd Sub Private Sub chboxD_Click() TextBox4.Enabled = chboxD.Value ‘ Toggle the enabling of TextBox4 based on chboxD’s state lstD.Visible = chboxD.Value ‘ Toggle the visibility of lstD based on chboxD’s stateEnd Sub Private Sub UserForm_Initialize() ‘ Initialize ListBoxes with columns Dim col As Variant For Each col In Array(“lstA”, “lstB”, “lstC”, “lstD”) With Me.Controls(col) .List = Array(“I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”) .MultiSelect = fmMultiSelectMulti .Visible = False ‘ Initialize all ListBoxes as hidden End With Next col ‘ Initialize the visibility based on checkboxes lstA.Visible = chboxA.Value lstB.Visible = chboxB.Value lstC.Visible = chboxC.Value lstD.Visible = chboxD.Value TextBox1.Enabled = chboxA.Value TextBox2.Enabled = chboxB.Value TextBox3.Enabled = chboxC.Value TextBox4.Enabled = chboxD.ValueEnd Sub Private Sub cmdProcessData_Click() Application.ScreenUpdating = False ‘ Disable screen updating to enhance performance Application.Calculation = xlCalculationManual ‘ Avoid recalculation until the end for performance Dim ws As Worksheet Set ws = ThisWorkbook.Sheets(“BidTrial”) Dim i As Integer Dim targetCols As String Dim masterCol As String Dim colLetter As String For i = 1 To 4 ‘ Check if the corresponding checkbox is checked If Me.Controls(“chbox” & Chr(64 + i)).Value Then colLetter = Me.Controls(“TextBox” & i).Text ‘ Validate the column letter is correct and only allow single column letters If colLetter <> “” And IsColumnValid(colLetter) Then masterCol = colLetter & “10:” & colLetter & “121” ‘ Data starts at row 10 targetCols = GetSelectedRanges(Me.Controls(“lst” & Chr(64 + i))) ‘ Copy data from master column to selected target columns If targetCols <> “” Then CopyData ws, masterCol, targetCols End If Else MsgBox “Invalid column letter in TextBox” & i, vbExclamation End If End If Next i Application.Calculate ‘ Recalculate all formulas in the workbook Application.ScreenUpdating = True ‘ Re-enable screen updating MsgBox “Data processing complete!”End Sub Function IsColumnValid(col As String) As Boolean IsColumnValid = (col >= “A” And col <= “W”) And Len(col) = 1 ‘ Ensure single letter between A and WEnd Function Function GetSelectedRanges(lstBox As MSForms.ListBox) As String Dim result As String Dim i As Integer For i = 0 To lstBox.ListCount – 1 If lstBox.selected(i) Then If result <> “” Then result = result & “,” result = result & lstBox.List(i) & “10:” & lstBox.List(i) & “121” ‘ Specify full range for each selected column End If Next i GetSelectedRanges = resultEnd Function Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String) Dim targetRange As Range, cell As Range, targetCell As Range Dim colRange As Variant For Each colRange In Split(targetCols, “,”) Set targetRange = ws.Range(colRange) For Each cell In ws.Range(masterCol) If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then For Each targetCell In targetRange If Not IsGrey(targetCell) Then targetCell.Value = cell.Value End If Next targetCell End If Next cell Next colRangeEnd Sub Function IsGrey(cell As Range) As Boolean IsGrey = (cell.Interior.Color = RGB(128, 128, 128)) ‘ Check if the cell’s color is greyEnd Function This is how my data looks and where it should be outputted into. For example, since A only has data, the user would enter H in the text box, then select each column that has A in it (L:O). Then the code would copy the data in H into those columns. For B only, the same would occur but only for headers with B (L:N). The code runs, but does not output. When I try to update the copydata to this: Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String) Dim targetRange As Range, cell As Range, targetCell As Range Dim colRange As String ‘ Ensure that colRange is treated as a String here For Each colRange In Split(targetCols, “,”) Set targetRange = ws.Range(colRange) For Each cell In ws.Range(masterCol) If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then For Each targetCell In targetRange.Cells ‘ Make sure to iterate over Cells of targetRange If Not IsGrey(targetCell) Then targetCell.Value = cell.Value End If Next targetCell End If Next cell Next colRangeEnd Sub When I update the code, I tend to have issues with it running due to For Each colRange In Split(targetCols, “,”). It’s being used as a variant because of it iterating over an array resulting from the split function, however, there seems to be inconsistencies in how vba is handling it. Not sure how to fix it.: I attached the sample document below. I also include the code that runs but doesn’t process any data. then i commented the updated one that gives me the compile error: Sub CopyData(ws As Worksheet, masterCol As String, targetCols As String) Dim targetRange As Range, cell As Range, targetCell As Range Dim colRange As String ‘ Ensure that colRange is treated as a String here For Each colRange In Split(targetCols, “,”) Set targetRange = ws.Range(colRange) For Each cell In ws.Range(masterCol) If Not IsGrey(cell) And Not IsEmpty(cell.Value) Then For Each targetCell In targetRange.Cells ‘ Make sure to iterate over Cells of targetRange If Not IsGrey(targetCell) Then targetCell.Value = cell.Value End If Next targetCell End If Next cell Next colRangeEnd Sub Read More