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]

You where first looking at column A for the first blank, now you seem to be looking at col C, but you are putting the values in col B, none of which makes any sense.
Try it like

Dim NextRow As Long
NextRow = Worksheets("Estimates").Range("B" & Rows.Count).End(xlUp).Offset(1).Row
If CheckBox1.Value = True Then Cells(NextRow, 2).Value = CheckBox1.Caption

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

 

[djb14128]

I am putting the values in col B, but I want to put them in a range of empty cells in col B. For example, B10 to B19.

 

[Fluff]

Do they need to go below existing data in col B?

 

[djb14128]

 

[Fluff]

That does not answer my question.

 

[djb14128]

Do they need to go below existing data in col B?

I'm not sure what you mean by that.

 

[Fluff]

Let's start from the beginning & please remember that I cannot see your spreadsheet, have no idea what your end goal is & do not know what you are trying to do.
With that in mind, can you please describe, in words, what you want to happen?

 

[djb14128]

For example,

If I have various options to choose from: Ca, P, F, Na, Mg. And if I didn't tick/check Ca in the check box. I don't mind what order the ticked elements are placed, as long as they are in the range B10 to B19.

 

[Fluff]

In that case use the code that dmt32 supplied in post#13

 

[djb14128]

Yep, it works now! Thanks, Fluff and dmt32!