VB code for Excel sheet

[Kerry]

I am trying to write a code for a form with check boxes in excel 2003. When a
person chooses a number of checkboxes it will put the information into a cell
and then go to the next blank cell and add the next checkbox data. This is
the code I have so far but instead of choosing the next blank cell it chooses
a set cell. How can I change this to choose the next blank cell?
Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Cars").Activate
Range("A1").Select
If chkFord = True Then
ActiveCell.Offset(0, 5).Value = "Ford"
End If
If chkToyota = True Then
ActiveCell.Offset(1, 5).Value = "Toyota"
End If
If chkMazda = True Then
ActiveCell.Offset(2, 5).Value = "Mazda"
End If
End Sub
Thanks Kerry

 

[DomThePom]

Not sure exactly what you are trying to do - your code at present just puts a
car name in cells F1, F2, or F3 (column F is 5 columns offset from column A)
if a particular check box has been clicked.
if you need to get the next empty cell then you need to use the 'end' method
of the range object - this is like using ctrl + left, right, up , down arrows
on the keyboard.

For example, if the activecell is in a1, at the top of a column of entries,
and you want to find the next empty cell beow this column then:

activecell.end(xldown)

will take you to the last entry in the column, so

activecell.end(xldown) .offset(1,0) will take you to the next (empty) cell
down

 

[Kerry]

Thanks for your interest

What I am trying to do is when a checkbox is ticked then that data will be
entered into the spreadsheet. If one of the checkboxes is not selected then I
want the next lot of data from the next ticked checkbox to go into the next
empty cell. At the moment the data is going into set cells and if a checkbox
is left blank this leaves a blank in the list. So if I enter
If chkFord = True Thenthis should enter ford in the next empty cell I hope.

Thanks I will have a go.

 

[Kerry]

I can see that it should work but it doesn't. Keep getting a runtime error
message
1004 application defined or object defined error.

 

[DomThePom]

I can see that it should work but it doesn't. Keep getting a runtime error
message
1004 application defined or object defined error.

 

[Kerry]

If chkFord = True Then

ActiveCell.Value = "Ford"

End If

If chkToyota = True Then

ActiveCell.End(xlDown).Offset(1, 0).Value = "Toyota"

End If

If chkMazda = True Then

ActiveCell.End(xlDown).Value = "Mazda"

End If
The error message is on the line ActiveCell.End(xlDown).Offset(1, 0).Value =
"Toyota"
The Ford line works as it makes the active cell Ford then I assume the above
line should find the next empty cell and enter Toyota but instead I get an
error message.

Thanks

 

[DomThePom]

OK - we were trying to go off the sheet

What you need is a function defing the next empty cell in relation to the
active cell. Copy this into your module:

Function NextEmptyCellDown(rngCell As Range) As Range
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, rngCell.Column).End(xlUp)
If IsEmpty(LastCell) Then
Set NextEmptyCellDown = LastCell
Else
Set NextEmptyCellDown = LastCell.Offset(1, 0)
End If
End With
End Function

then you can use the function in your code:

If chkFord = True Then

NextEmptyCellDown (activecell).Value = "Ford"

End If

If chkToyota = True Then

NextEmptyCellDown (activecell).Value = "Toyota"

End If

If chkMazda = True Then

NextEmptyCellDown (activecell).Value = "Mazda"

End If

 

[Kerry]

DomThePom you're a bloody genius, that works like a charm.

Thanks for your perseverance.

Kerry

 

[DomThePom]

You are very welcome!

DomThePom you're a bloody genius, that works like a charm.

Thanks for your perseverance.

Kerry