onchange + option button

[goforito]

Hello!

I have used in VBA (excel 2000) 3 option buttons, which are grouped. Currently, one of them is clicked and the data connected to it are correct.
The problem is that if I make a change in a cell, the data are NOT re-calculated. I have to click another option button, and then re-click the one I want and only then the data become correct again.

So the point is: Is there a way to make the option button to re-calculate the data, whenever ANY change occurs in the current sheet? Something like "onchange"...

Please help !!
Thanks!!

[RobDog888]

Welcome to the Forums.

I would need to see some code to be able to optimize it because depending how your calculations are I could
suggest a couple of different methods.

[goforito]

Well, there are three OptionButtons which call the Sub fetchData. This Sub copies the values of 5 cells to other 5 cells. Its pameters are four. The first two indicate the cells (by row+column), where the data must be pasted. The other two parameters indicate the cells (by row+column again) from where the data are copied.

What I want is to copy and paste the data whenever a change occurs. If I make a change to the cells from where the data are copied, no change occurs to the cells where the data are pasted. It works only if I re-click the button.

Thanks a lot!


CODE:

Private Sub fetchData(toRow, toCol, fromRow, fromCol)
toRow = Range("rev1").Row + toRow
toCol = Range("rev1").Column + toCol

For i = 0 To 4
Cells(toRow + i, toCol) = Cells(fromRow, fromCol + i)
Next i

End Sub

Private Sub OptionButton1_Click()
Call fetchData(0, 0, Range("bas1").Row, Range("bas1").Column)
End Sub

Private Sub OptionButton2_Click()
Call fetchData(0, 0, Range("six1").Row, Range("six1").Column)
End Sub

Private Sub OptionButton3_Click()
Call fetchData(0, 0, Range("phon1").Row, Range("phon1").Column)
End Sub

[RobDog888]

Looking through the code I would leave the option buttons code since you want to see an update whenever you
click a different button. I would make use of the SelectionChange event behind the Sheet. This event fires
everytime a different cell gets the focus. So if you enter a new value in a cell and press enter the focus automatically
changes to the cell below, thus in effect firing the SelectionChange event and in there you can call your fetchData
procedure. Note it contains a target range object that may come in handy.

[goforito]

Thanks a lot!
It works!