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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,802
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,922
Office Version
2019
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
42,802
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,922
Office Version
2019
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
42,802
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,593
Messages
5,487,749
Members
407,609
Latest member
Chrissie1970

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top