VB for an entire worksheet
 

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

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

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

When I think I've figured out the logic I get some continuous loopin
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

--
msals2
-----------------------------------------------------------------------
msals22's Profile: http://www.excelforum.com/member.php...fo&userid=3090
View this thread: http://www.excelforum.com/showthread.php?threadid=54903

VB for an entire worksheet
 

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).ClearContent s
On Error GoTo 0


---
Regards,
Norman



"msals22" wrote in
message ...

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 he
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

VB for an entire worksheet
 

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" a écrit dans
le message de news: ...

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 he
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

VB for an entire worksheet
 

Norman,
The way I have the worksheet setup is like this...if Yes than a tex
string appears in the next cell to "enter a value". The user enters
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 th
conditional to call the text string "enter a value." I would like th
user to be able to clear choices, and each time keep the conditional
to enter a value. I hope this is a little clearer

--
msals2
-----------------------------------------------------------------------
msals22's Profile: http://www.excelforum.com/member.php...fo&userid=3090
View this thread: http://www.excelforum.com/showthread.php?threadid=54903

VB for an entire worksheet
 

Actually; it's getting darker than before!

If you are lost, you cant post your workbook as an attachment to:


HTH
--
AP

"msals22" a écrit
dans le message de news:
...

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