Update cell in xcel

[vbuser123]

I added some click buttons to Xcel sheet and link it to a cell say M169...

and I wrote this in VB:

If (Target.Address = "M169") Or (Target.Address = "$D$173") Or(Target.Address = "$D$175") Then
etc...

When I click the buttons, the Xcel sheet is not updated automatically.

How can I go about solving this problem?

I am still fairly new to VB.

Thanks for any help.

[DKenny]

Can you post all the code for the Click event of one of the buttons?

[vbuser123]

DKenny,
Thanks for your reply.

I tried to add the click buttons vb code. But, it did not work. I don't think I am doing it right.
Sub OptionButton207_Click()
End Sub

[DKenny]

This sub has no code in it!

[vbuser123]

I know it has no code in it. I don't even know how to work the code I stated before in the click button sub. Maybe this is not the solution.

Please!!!!!!!! somebody must have a solution.

[vbuser123]

Maybe I am not stating my problem very clear:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "M169") Or (Target.Address = "$D$173") Or(Target.Address = "$D$175") Then
etc...

I can not get the xcell to update when the click button I added to the xcell sheet is clicked.

Would somebody give me an example code?

Thanks.

[DKenny]

Can you post the complete code for the Worksheet_Change event?

If you want the code to run when you click the button, then you should be putting it in the _Click event of the button. Where you have it now it will only run when you make a change to the worksheet.

[vbuser123]

This is the exact code:

Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "M169") Or (Target.Address = "$D$173") Or(Target.Address = "$D$175") Then



If Range("M169").Value = 1 And Range("D173").Value <= 7 Then

If (Range("D175").Value > 0) And (Range("D175").Value <= 10) Then
Range("I175").Value = (0.3)
Range("I179").Value = (-1#)
Range("I183").Value = (-1.8)
Range("I191").Value = (-2.8)
Range("I195").Value = "N/A"
Range("I199").Value = (-1.7)
Range("I203").Value = (-1.7)
Range("I207").Value = (-2.8)
End If

End If


End If


End Sub


Thanks. I appreciate.

[DKenny]

OK
Next question - when do you want this code to execute?
a/ When the user changes the values in M169,D173 or D175?
b/ When the user clicks on a button?
c/ At some other time/event?

[vbuser123]

Maybe "c" - I want this code to execute when user clicked on the button or make changes to values in D173 or D175.

Cell M169 is linked to the click buttons.

[DKenny]

OK
First up - a command button cannot be linked to a cell. I'm assuming that its a spinner control that you've added, that is a control that is linked to a cell and allows the user to change the value of that cell by specific increments.

If so, then the WS_Change event is all you need. Here is a revised version of your code. There were a couple of things I had to change and some others that were just housekeeping.

Required Changes
1/ The address property of a range will always give a result string with absolute references, unless you use some of the optional flags. So in your 1st IF statement I had to change M169 to $M$169.
2/ As the code is going to change the WS and is being called by the WS_change event an infinite loop is created. To avoid this I have disabled events while the code is running. Remembering to re-enable when I'm finished.

Housekeeping
1/ Reduced the 2nd two nested IF statements to a single IF statement.
2/ Removed the brackets around the values that you are setting the cells to, they are unnecessary.
3/ Indented lines between IF and End IF statements. Does nothing for the running of your code, but sure makes things easier to read.


Try this version and let me know if you still have problems.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$M$169" _
    Or Target.Address = "$D$173" _
    Or Target.Address = "$D$175" Then
        Application.EnableEvents = False
        If Range("M169").Value = 1 _
        And Range("D173").Value <= 7 _
        And Range("D175").Value > 0 _
        And Range("D175").Value <= 10 Then
                Range("I175").Value = 0.3
                Range("I179").Value = -1
                Range("I183").Value = -1.8
                Range("I191").Value = -2.8
                Range("I195").Value = "N/A"
                Range("I199").Value = -1.7
                Range("I203").Value = -1.7
                Range("I207").Value = -2.8
        End If
        Application.EnableEvents = True
    End If
End Sub

[vbuser123]

I tried it and it is still not doing what I want it to do.

It's option button that I am trying to apply.

The option buttons are linked to cell M169. I have 3 option buttons. The option button giving me a 1, 2, or 3 in cell M169.

So, when option button gives me a 1 in cell M169, I would expect a change in the xcell sheet. Likewise when a value is entered in D173 or D175 I expect a update in the Xcel.

Bottom line is I must have the option buttons and the cell value in D173 or D175.

[DKenny]

Have a look at the code in the attached and let me know if these options buttons are working the way you would expect yours to work.

[vbuser123]

I will try it later today. I will let you know.

Thanks for helping me with this.

[vbuser123]

DKenny,
I tried what you did. But, still I can not get it to work.
Did you link the option buttons to cell M169?