VB code for Excel sheet

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Daniel Gash-Jones]

Also to make your code simpler I would suggest:

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Cars").Activate
Range("A1").Select
with activecell
If chkFord Then .Offset(0, 5).Value = "Ford"
If chkToyota Then .Offset(1, 5).Value = "Toyota"
If chkMazda Then .Offset(2, 5).Value = "Mazda"
End with
End Sub

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" wrote:

> 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

 

[Guest]

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

 

[Guest]

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

 

[Daniel Gash-Jones]

Private Sub cmdOK_Click()
dim x as integer
ActiveWorkbook.Sheets("Cars").Activate
Range("A1").Select
x = 0
with activecell
If chkFord Then
.Offset(x, 5).Value = "Ford"
x = x + 1
end if
If chkToyota Then
.Offset(x, 5).Value = "Toyota"
x = x + 1
end if
If chkMazda Then
.Offset(x, 5).Value = "Mazda"
x = x + 1
end if
End with
End Sub

Not elegant at all but I think it should do what you want

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Thanks for your perseverance.

Kerry

 

[Guest]

You are very welcome!

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

Thanks for your perseverance.

Kerry