Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to save excel sheet | Excel Discussion (Misc queries) | |||
locking excel sheet from vb code | Excel Programming | |||
help: code for importing xml into excel sheet | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
how to programmingly insert a new row in excel sheet?(using VB code) | Excel Programming |