VBA (Userform)

djb14128

New Member
Joined
Nov 14, 2019
Messages
27
Hi All,

I just would like to know why my code isn't working. Here is my VBA code:

VBA Code:
Private Sub OKButton_Click()

Dim i As Integer

'Make Sheet1 active
Estimates.Active

For i = 10 To 19

If DataCheckBox1.Value = True Then Cells(i, 2).Value = DateCheckBox1.Caption

If DataCheckBox2.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox2.Caption

If DataCheckBox3.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox3.Caption

If DataCheckBox4.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox4.Caption

If DataCheckBox5.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox5.Caption

If DataCheckBox6.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox6.Caption

If DataCheckBox7.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox7.Caption

If DataCheckBox8.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox8.Caption

If DataCheckBox9.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox9.Caption

If DataCheckBox10.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & DateCheckBox10.Caption

End Sub



Private Sub ClearButton_Click()

Call UserForm_Initialize

End Sub

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub UserForm_Initialize()

'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False

End Sub
Thanks in advance.

Kind regards,
Ian
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,194
Office Version
365
Platform
Windows
In what way isn't it working?
 

djb14128

New Member
Joined
Nov 14, 2019
Messages
27
When I press the command button in the Excel spreadsheet I get the "Run-time error '424': Object required". It just directs me to the Userform in the code with an error message as I mentioned.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,265
Office Version
2013
Platform
Windows
Hi,
You are missing Next in your for loop

Assuming Estimates is your worksheets code name then try this update to your code & see if does what you want

VBA Code:
Private Sub OKButton_Click()

    Dim i As Integer

    For i = 10 To 19
        With Me.Controls("DataCheckBox" & i-9)
            If .Value Then Estimates.Cells(i, 2).Value = .Caption
        End With
    Next i

End Sub
Dave
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,194
Office Version
365
Platform
Windows
It just directs me to the Userform in the code with an error message as I mentioned.
I don't see any mention of that. :unsure:
In the VB editor select Tools > Options > General > select "Break in class module"
That way when you get an error it will show where the error occurs.

I suspect that you have misnamed your controls, some start Date & others Data
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,265
Office Version
2013
Platform
Windows
It's still not working
More helpful if you tell us where the code breaks

Is your worksheet tab named Estimates?

or have you change the sheets code name to Estimates?

Dave
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,194
Office Version
365
Platform
Windows
What line of code is highlighted when you click "Debug"?
 

djb14128

New Member
Joined
Nov 14, 2019
Messages
27
VBA Code:
Private Sub OKButton_Click()

Worksheets("Estimates").Active

Dim i As Integer

For i = 10 To 19

If CheckBox1.Value = True Then Cells(i, 2).Value = CheckBox1.Caption

If CheckBox2.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox2.Caption

If CheckBox3.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox3.Caption

If CheckBox4.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox4.Caption

If CheckBox5.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox5.Caption

If CheckBox6.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox6.Caption

If CheckBox7.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox7.Caption

If CheckBox8.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox8.Caption

If CheckBox9.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox9.Caption

If CheckBox10.Value = True Then Cells(i, 2).Value = Cells(i, 2).Value & " " & CheckBox10.Caption

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Next i

End Sub
This code is my problem now
 

Forum statistics

Threads
1,081,953
Messages
5,362,349
Members
400,673
Latest member
mikansang

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top