VBA (Userform)

[djb14128]

Hi All,

I just would like to know why my code isn't working. Here is my 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]

Get rid of all those End If lines

 

[djb14128]

Private Sub OKButton_Click()

[B]Worksheets("Estimates").Active[/B]

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



Next i

End Sub

The one in bold is highlighted in yellow.

 

[dmt32]

Hi,
you have changed name of your checkboxes?

try this change to my posted code

Private Sub OKButton_Click()

    Dim i As Integer

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

End Sub

You have not answered my question in post #8 - If the TAB name is Esitimates then change this line

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

to this

If .Value Then Worksheets("Estimates").Cells(i, 2).Value = .Caption

Dave

 

[Fluff]

It should be Activate, not Active

 

[djb14128]

I really appreciate everyone's help. Thanks!

I have another question. How do I find the next empty row in the next 50 rows? What's the code for it?

I used this code:

emptyCell = WorksheetFunction.CountA(Range("A:A")) + 1

 

[Fluff]

How about

Dim NextRow As Long
NextRow = Worksheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

 

[djb14128]

Here is my code right now:

Private Sub OKButton_Click()

Worksheets("Estimates").Activate

[B]Sheets("Sheet2").Range("C5:C10").SpecialCells(xlCellTypeBlanks) = Now()[/B]


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

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

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

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

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

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

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

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

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

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


End Sub

The one in bold is highlighted in yellow. I'm trying to find the next empty cell in a column of 50 rows.

What I want to do is tick any of the boxes and they should fill in the empty spaces in the column for the next 50 rows.

 

[Fluff]

Did you try what I suggested?

 

[djb14128]

How about

Dim NextRow As Long
NextRow = Worksheets("Estimates").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

I have. It doesn't work. It fills up a filled cell with the elements not the unfilled cells in a column.

 

[djb14128]

It fills up one cell with the elements that I've ticked/checked.