Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub

On May 1, 10:54 am, Atishoo wrote:
Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub

Try saving the sum into an intermediate temporary value ...

Private Sub ComboBox54_Click()
Dim Newvalue, c
With Worksheets("Main Board")

Set c = .Range("D82")
Newvalue = Range("I86") + ActiveCell.Offset(c, 0).Value

ActiveCell.Offset(c, 0).Value = Newvalue
Range("I86") = ""
End With

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

End Sub

There should be no problem with adding another cell value to the first cell
value to increase the first cell value. That said, in your code you use the
Set statement for a cell, which makes that Set value an object variable. You
then use the object variable in a relative cell reference like Offset(c, 0).
This can cause problems because of data type. If you are going to use the
variable c as a number value contained in the cell D82 then you don't need
the Set statement, just use:

c = Range("D82").Value

That assumes that the value will be a number which can function in the
Offset statement.

I think your problem lies elsewhere. If you have a Worksheet_Change macro
in the sheet you are working on, then it is probably tiggering a calculation
when you click the button and sets off a chain reaction. Everytime the cell
adds the offset value it triggers the worksheet change event and on an on and
on.

"Atishoo" wrote:

Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub

Thanks I changed the set c as suggested and that resolved a problem with two
combo boxes interfering with each other! The offset function is operating
exactly as you describe and works well! I think you are right about the
problem lying with a change macro somewhere I have previously deleted a shed
load of old VBA in this project but cant find any change macro's left might
they be hidden in some strange locaion deep in the bowels of vis basic? and
if i just delete the piece of code does that fully remove the macro?

"JLGWhiz" wrote:

There should be no problem with adding another cell value to the first cell
value to increase the first cell value. That said, in your code you use the
Set statement for a cell, which makes that Set value an object variable. You
then use the object variable in a relative cell reference like Offset(c, 0).
This can cause problems because of data type. If you are going to use the
variable c as a number value contained in the cell D82 then you don't need
the Set statement, just use:

c = Range("D82").Value

That assumes that the value will be a number which can function in the
Offset statement.

I think your problem lies elsewhere. If you have a Worksheet_Change macro
in the sheet you are working on, then it is probably tiggering a calculation
when you click the button and sets off a chain reaction. Everytime the cell
adds the offset value it triggers the worksheet change event and on an on and
on.

"Atishoo" wrote:

Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub

Oh yes forgot to mention Im not actually adding values together but am
combining text so if i select "john" from the combo box it will put "john" in
a cell offset to the active cell, then if i select "Peter" It will return
"john Peter" in the offset cell etc etc until I use a clear vba to reset the
offset cell

"JLGWhiz" wrote:

There should be no problem with adding another cell value to the first cell
value to increase the first cell value. That said, in your code you use the
Set statement for a cell, which makes that Set value an object variable. You
then use the object variable in a relative cell reference like Offset(c, 0).
This can cause problems because of data type. If you are going to use the
variable c as a number value contained in the cell D82 then you don't need
the Set statement, just use:

c = Range("D82").Value

That assumes that the value will be a number which can function in the
Offset statement.

I think your problem lies elsewhere. If you have a Worksheet_Change macro
in the sheet you are working on, then it is probably tiggering a calculation
when you click the button and sets off a chain reaction. Everytime the cell
adds the offset value it triggers the worksheet change event and on an on and
on.

"Atishoo" wrote:

Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub

If you are trying to concatenate cell I86 value to the offset value then:

ActiveCell.Offset(c, 0).Value = Range("I86").Value & " " & _
ActiveCell.Offset(c, 0).Value

This will put John and Peter in the same cell with a space between the two.
If you don't want the space then:

ActiveCell.Offset(c, 0).Value = Range("I86").Value & ActiveCell.Offset(c,
0).Value

Use the ampersand (&) instead of plus (+) to concatenate strings. Use the +
to add numbers. It avoids confusion.

"Atishoo" wrote:

Oh yes forgot to mention Im not actually adding values together but am
combining text so if i select "john" from the combo box it will put "john" in
a cell offset to the active cell, then if i select "Peter" It will return
"john Peter" in the offset cell etc etc until I use a clear vba to reset the
offset cell

"JLGWhiz" wrote:

There should be no problem with adding another cell value to the first cell
value to increase the first cell value. That said, in your code you use the
Set statement for a cell, which makes that Set value an object variable. You
then use the object variable in a relative cell reference like Offset(c, 0).
This can cause problems because of data type. If you are going to use the
variable c as a number value contained in the cell D82 then you don't need
the Set statement, just use:

c = Range("D82").Value

That assumes that the value will be a number which can function in the
Offset statement.

I think your problem lies elsewhere. If you have a Worksheet_Change macro
in the sheet you are working on, then it is probably tiggering a calculation
when you click the button and sets off a chain reaction. Everytime the cell
adds the offset value it triggers the worksheet change event and on an on and
on.

"Atishoo" wrote:

Am trying to get a combo box to keep adding its selected contents to a cell
(actually to an offset cell) have used this sub successfully with a set list
fill range but as soon as I use a variable range it becomes circular and just
keeps adding and adding to itself etc! how do I make a cell = itself plus
another cell once only per combo box click?

Private Sub ComboBox54_Click()

With Worksheets("Main Board")

Set c = .Range("D82")

ActiveCell.Offset(c, 0).Value = Range("I86") + ActiveCell.Offset(c, 0).Value
Range("I86") = " "
End With

End Sub