ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VB code for Excel sheet (https://www.excelbanter.com/excel-programming/400641-vbulletin-code-excel-sheet.html)

Kerry
VB code for Excel sheet
 
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
VB code for Excel sheet
 
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

Kerry
VB code for Excel sheet
 
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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

Kerry
VB code for Excel sheet
 
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" wrote:

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

DomThePom
VB code for Excel sheet
 
On what line of code are you getting the error?

"Kerry" wrote:

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

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

Kerry
VB code for Excel sheet
 
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" wrote:

On what line of code are you getting the error?

"Kerry" wrote:

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

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

DomThePom
VB code for Excel sheet
 
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" wrote:

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

On what line of code are you getting the error?

"Kerry" wrote:

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

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

Kerry
VB code for Excel sheet
 
DomThePom you're a bloody genius, that works like a charm.

Thanks for your perseverance.

Kerry

"DomThePom" wrote:

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

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

On what line of code are you getting the error?

"Kerry" wrote:

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

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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

DomThePom
VB code for Excel sheet
 
You are very welcome!

"Kerry" wrote:

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

Thanks for your perseverance.

Kerry

"DomThePom" wrote:

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

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

On what line of code are you getting the error?

"Kerry" wrote:

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

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 Then
activecell.end(xldown) .offset(1,0).Value = "Ford"
End If
this should enter ford in the next empty cell I hope.

Thanks I will have a go.



"DomThePom" wrote:

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


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com