Solved: Macros and cell references

[mhblake]

Hi all-

I'm trying to create (record) a macro that will run a formula. In essence, I have toggle switches to change values, and I also created a 'reset' button to re-set the values to what they previously were.

I got the macro to record (the formula is " = [cell]" to change the value back to what it was), but I'm experiencing a problem where if you have a random cell selected when you click the 'reset' button, the macro may - or may not - enter values in those random cells.

I'm developing this spreadsheet as an illustration for c-suite executives, and I'd rather have this as clean as possible. I do have all the other cells locked and the sheet protected to prevent errors, but this continues to happen.

Also, 'relative references' is turned off when I'm recording. Any ideas? Anything I can plug into the macro/VBA to allow it to modify only the selected cells?

Thanks!

(PS I'm running excel 2007)

[Kenneth Hobs]

Welcome to the forum!

Recording a macro is just the first step. You may need to change the code to make it more useful and efficient.

Often, you will see recorded code with the word Selection. That could be one cell or many. You need to decide what to do for both scenarios if you are using the Selection object range of cells.

It is better to explain what you want. You can post recorded code but that is just the beginning.

You can use a sheet's Selection, Change, and Calculation types of events for some tasks.

The user should be able to use Undo to undo any changeable data entered. They can always close without saving if they screw up too much.

I like to use the Workbook's Open event to Protect and use the UserInterfaceOnly option which lets your code modify protected cells without the need to unprotect, change, and protect.

[mhblake]

Hm... okay.

I see the word "select" in here. As a disclaimer, I'm quite proficient with excel as compared to the rest of my office, but this is my first foray into VBA/macros. So this is an entirely new language that I haven't seen before.

Here's the macro:

[VBA]Sub ResetCmcChoice()
'
' ResetCmcChoice Macro
'
'
ActiveCell.FormulaR1C1 = "=RC[-9]"
Range("N31").Select
ActiveCell.FormulaR1C1 = "=RC[-9]"
Range("N32").Select
ActiveCell.FormulaR1C1 = "=RC[-9]"
Range("N33").Select
ActiveCell.FormulaR1C1 = "=RC[-9]"
Range("N34").Select
End Sub[/VBA]

So I see the 'select' word, but how do you recommend I tell it to ONLY modify those 4 cells (N31:N34)?

[Kenneth Hobs]

IF you want to hard code the range:
[vba]Range("N31:N34").FormulaR1C1 = "=RC[-9]" [/vba] Or for a Selected range:
[vba]Selection.("N31:N34").FormulaR1C1 = "=RC[-9]"[/vba]

[mhblake]

Yes! This is perfect!

Thanks so much!!!