VB for an entire worksheet

[msals22]

I have a few command buttons to reset some cells in my worksheet. However, I cannot figure out how to keep the conditional statements in the cell after I've cleared the entries in the cells. I am trying to add some VB to the worksheet to do the conditionals for me. I.e., if E16 = Yes, F16 = Enter Value. I've been trying to use some scripts such as:

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Yes"",""Enter Value"","""")"
Range("F16").Select

I am trying to add this code here:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

When I think I've figured out the logic I get some continuous looping when I try to clear the contents of the cell with the command button. An example of the command button code is:

Range("f16:f40").Select
Selection.ClearContents

Thanks for your help.

[Ardus Petus]

Your sub Worksheet_SelectionChange gets called whenever you change
selection.

Your code "Range("f16:f40").Select" gets the sub to be called recursively

You should write:
Range("f16:f40").ClearContents

Another way to avoid these recursive calls:

Application.EnableEvents = False
Range("f16:f40").Select
Selection.ClearContents
Application.EnableEvents = True

HTH
--
AP

"msals22" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> I have a few command buttons to reset some cells in my worksheet.
> However, I cannot figure out how to keep the conditional statements in
> the cell after I've cleared the entries in the cells. I am trying to
> add some VB to the worksheet to do the conditionals for me. I.e., if
> E16 = Yes, F16 = Enter Value. I've been trying to use some scripts
> such as:
>
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Yes"",""Enter Value"","""")"
> Range("F16").Select
>
> I am trying to add this code here:
> Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
>
> When I think I've figured out the logic I get some continuous looping
> when I try to clear the contents of the cell with the command button.
> An example of the command button code is:
>
> Range("f16:f40").Select
> Selection.ClearContents
>
> Thanks for your help.
>
>
> --
> msals22
> ------------------------------------------------------------------------
> msals22's Profile:
> http://www.excelforum.com/member.php...o&userid=30908
> View this thread: http://www.excelforum.com/showthread...hreadid=549034
>

[Norman Jones]

Hi M,

Why not delete only the non-formula cells?


Dim rng As Range
Set rng = Range("f16:f40")

On Error Resume Next
rng.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0


---
Regards,
Norman



"msals22" <[email protected]> wrote in
message news:[email protected]...
>
> I have a few command buttons to reset some cells in my worksheet.
> However, I cannot figure out how to keep the conditional statements in
> the cell after I've cleared the entries in the cells. I am trying to
> add some VB to the worksheet to do the conditionals for me. I.e., if
> E16 = Yes, F16 = Enter Value. I've been trying to use some scripts
> such as:
>
> ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Yes"",""Enter Value"","""")"
> Range("F16").Select
>
> I am trying to add this code here:
> Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
>
> When I think I've figured out the logic I get some continuous looping
> when I try to clear the contents of the cell with the command button.
> An example of the command button code is:
>
> Range("f16:f40").Select
> Selection.ClearContents
>
> Thanks for your help.
>
>
> --
> msals22
> ------------------------------------------------------------------------
> msals22's Profile:
> http://www.excelforum.com/member.php...o&userid=30908
> View this thread: http://www.excelforum.com/showthread...hreadid=549034
>

[msals22]

Norman,
The way I have the worksheet setup is like this...if Yes than a text string appears in the next cell to "enter a value". The user enters a value which in turn clears the if...then conditional I had in the cell. If the user clears the selection to start over, I've then lost the conditional to call the text string "enter a value." I would like the user to be able to clear choices, and each time keep the conditionals to enter a value. I hope this is a little clearer.

[Ardus Petus]

Actually; it's getting darker than before!

If you are lost, you cant post your workbook as an attachment to:
[email protected]

HTH
--
AP

"msals22" <[email protected]> a écrit
dans le message de news:
[email protected]...
>
> Norman,
> The way I have the worksheet setup is like this...if Yes than a text
> string appears in the next cell to "enter a value". The user enters a
> value which in turn clears the if...then conditional I had in the cell.
> If the user clears the selection to start over, I've then lost the
> conditional to call the text string "enter a value." I would like the
> user to be able to clear choices, and each time keep the conditionals
> to enter a value. I hope this is a little clearer.
>
>
> --
> msals22
> ------------------------------------------------------------------------
> msals22's Profile:
> http://www.excelforum.com/member.php...o&userid=30908
> View this thread: http://www.excelforum.com/showthread...hreadid=549034
>