Macro Help - Worksheet change dynamic cell

[kdibai]

Hello everybody!


Let's see if you guys can help me. With my knowledges and a few researchs, I made something that is working, but I would like to do it in a better way.


The file is here: http://dl.dropbox.com/u/13865966/Example.xlsm


1. In A7 we can see what option was selected (if it will be in currency or percentage).

2. The number format has to change correspondingly with the choice

3. I tried to do a worksheet change event to run the number format macro automatically when A7 changes, but it won't recognize the change in A7 has a change.

4. To solve that, I thought that I could put a refresh buttom that contain a copy and past value macro that would create a "real" change in a cell and use this cell as the reference for the number format macro.


Everything is working ok, but what it would be perfect is if all of that could play smoothly in an automatic way without this refresh butom.

Is there a way to recognize a cell change in this dynamic situation or another solution?


I did a similar question here ( http://chandoo.org/forums/topic/bring-number-format-with-a-formula ) but trying to solve the primary stage of my doubt with a formula. As I found a better solution in macro, I came with this new question now that will be applied in many other situations.


Ty you in advance

 

[Luke M]

If the cell in A7 is a dropdown itself, the worksheet_change event "should" be detecting it. If it's a formula looking at somewhere else, you'll need to use the worksheet_calculate event.


I'm afraid I can't access your workbook from my location, so I am guessing at your setup a little.

 

[kdibai]

Hi Luke. Yeah, you figured that well, even without seeing the sheet.

It's not a drop down, it's a radio buttom, but I guess it's all the same. The Worksheet_change is not working in its linked cell.


The code that I'm using and is not working with the radio buttom change is:


Private Sub Worksheet_change(ByVal Target As Range)

If Target.Address <> "$A$7" Then Exit Sub

Select Case Target.Value

Case 1

[A8:A11].NumberFormat = "$ 0"

Case 2

[A8:A11].NumberFormat = "0.00%"

Case Else

End Select

End Sub


To solve, I copy paste this value with another macro to B7 cell and use the Worksheet_change there.


I didn't know about Worksheet_calculate. With your anwser, I did some research and tried to build this code, but didn't work. I guess I did something wrong...


Private Sub Worksheet_Calculate()

Dim target As Range

Set target = Range("A7")


Case 1

[A8:A11].NumberFormat = "$ 0"

Case 2

[A8:A11].NumberFormat = "0.00%"

Case Else

End Select

End Sub

 

[Luke M]

Since macros are okay to use, I would first suggest looking at using the ActiveX radio button(s) instead of the Form buttons. Gives you a bit more controls, and can be directly linked to their own macros. Once you've inserted the two ActiveX radio buttons, code should be something like:

[pre]

Private Sub OptionButton1_Click()
[A8:A11].NumberFormat = "$ 0"
End Sub

Private Sub OptionButton2_Click()
[A8:A11].NumberFormat = "0.00%"
End Sub

[/pre]
You should double-click on the objects to generate the actual macro, as the names may be slightly different.

 

[kdibai]

Worked! Many Thanks Luke M

 

[Luke M]

Hooray, glad I could help.