Clearing cells but also not clearing other dependent cells
 

I have cells that use code to enter data (running total in same cell) and
other cells with formulas that use the data from the code cells. When I clear
the cells both these types of cells they all "0" out. I want completely
different cells to maintain some of these totals but because they clear the
final total cells also go to "0" because there is no value in the cells that
were cleared. I need to add to the clear contents macro so these other cells
keep there totals after the others have cleared.
Please help.
Thanks

Clearing cells but also not clearing other dependent cells
 

There are two methods you should look into. both can be done either by
formulas or VBA


1) special Cells method in VBA. this is equivalent to the following
menu item on a worksheet

Edit - GoTo - special


You can use this method to find cells with and without formulas.


2) PasteSpecial - If you have formulas that you want to retain a value
after the cell that the formula references you must replace the formula
with a value. Use Paste Special and Past the Value only to remove the
formula. the Pastespeciual on the worksheet is only enabled after you
copy cell(s) and is on the Edit Menu.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Clearing cells but also not clearing other dependent cells
 

Thanks..maybe I was not clear. below is the code I am using with a form
control.

Sub Clear_all()
Range("D6:G6,A7,A11").ClearContents
Range("D10:G10").ClearContents

End Sub

Through functions I record the various totals in columns "M,N,O,and P" which
are dependent on the values in the other cells. When I clear the above cells
with the code there is no value left and so the columns also clear. I would
like to know the code so that the columns retain there values.

I am new at "code" so please do not asume I know what I am doing...I got my
code off the net.

Thank you.

"joel" wrote:


There are two methods you should look into. both can be done either by
formulas or VBA


1) special Cells method in VBA. this is equivalent to the following
menu item on a worksheet

Edit - GoTo - special


You can use this method to find cells with and without formulas.


2) PasteSpecial - If you have formulas that you want to retain a value
after the cell that the formula references you must replace the formula
with a value. Use Paste Special and Past the Value only to remove the
formula. the Pastespeciual on the worksheet is only enabled after you
copy cell(s) and is on the Edit Menu.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

Clearing cells but also not clearing other dependent cells
 

This is a little complicated. Let me know if you need futher
explanation


Sub Clear_all()

Set ClearRange = Range("D6:G6,A7,A11,D10:G10")

Set FormulaRange = ClearRange.SpecialCells(xlCellTypeFormulas)

For Each Cell In FormulaRange
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValues
Next Cell

For Each Cell In ClearRange
If Application.Intersect(Cell, FormulaRange) Is Nothing Then
Cell.ClearContents
End If
Next Cell

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Clearing cells but also not clearing other dependent cells
 

Hi Joel'
Thanks for the reply.
I tried this a couple of ways with the form control and just by running the
Macro and both ways I got a message with a big red "X" and the # 400. Nothing
was cleared. Also I was not sure about the clear syntax as I need to use
clear contents...
Would appreciate any other help you might have.

"joel" wrote:


This is a little complicated. Let me know if you need futher
explanation


Sub Clear_all()

Set ClearRange = Range("D6:G6,A7,A11,D10:G10")

Set FormulaRange = ClearRange.SpecialCells(xlCellTypeFormulas)

For Each Cell In FormulaRange
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValues
Next Cell

For Each Cell In ClearRange
If Application.Intersect(Cell, FormulaRange) Is Nothing Then
Cell.ClearContents
End If
Next Cell

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

Clearing cells but also not clearing other dependent cells
 

I need some additional information to understand what you are doing.

First there are two different types of forms. Some people call a
worksheet a form when the format the sheet to take user inputs. Then
there are the VBA Userforms where people add controls. One of the
controls is a user worksheet. It sounds like you have a userform with a
worksheet control that you are trying to clear. I suspect the problem
is you are not refereing to the worksheet control properly. I think I
need to see your code and also I need to know where your code is
located.


If you have a Userform and you are trying to refer to an obect on the
userform you need to do something like this

Userform1.Listbox1.text


Now if you are trying to access an obect on the Userform and you are
trying to get an object on the userform you can use "ME" instead of
specifying Userform1

Userform1.Listbox1.text

or

me.Listbox1.text



Now if you are running code insdie a userform and need to access the
worksheet you need to specfy the worksheet. Activesheet usually won't
work


Sheets("sheet1").Range("A1")


A lot of problems occur because people don't specify the complete
location of obects. I don't leavve anything to chance. I always have
unique names for my worksheets. I also change the default Listbox names
and other objects to the function name. Instead of using Listbox1 I
will change the Listbox name to LastNamebox. I will change userform1 to
NameAdressForm.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Clearing cells but also not clearing other dependent cells
 

Joel ...I am not sure that I understand everything you have responded with as
I am new at VBA. I have a control form button on the spreadsheet that has the
following macro:
Sub Clear_all()
Range("D6:G6,A7,A11").ClearContents
Range("D10:G10").ClearContents

End Sub

This is the other code that I also came up with on the net and adjusted:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblVal As Double, rngInterest As Range, lngCount As Long
On Error GoTo ExitPoint
Application.EnableEvents = False
Set rngInterest = Intersect(Target, Range("A7,D6:G6,A11,D10:G10"))
lngCount = Application.WorksheetFunction.CountA(rngInterest)
If lngCount Then
dblVal = Val(rngInterest(1).Value)
Application.Undo
If lngCount = 1 Then
rngInterest.Value = dblVal + Val(rngInterest.Value)
Else
MsgBox "Multiple Updates Not Permitted", vbCritical, "Computer Says
No"
End If
End If
ExitPoint:
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub

I need to clear contents using the button and clear the contents of the
associated cells but do not clear the cells in columns M,N,O, and P. These
cells have totals equalling the totals in the other cells.
The only other way I can think of is to actually send you a copy of the
spreadsheet as an email attachment...doin't know if that is allowed or
something that is done through this forum.

Thanks again



"joel" wrote:


I need some additional information to understand what you are doing.

First there are two different types of forms. Some people call a
worksheet a form when the format the sheet to take user inputs. Then
there are the VBA Userforms where people add controls. One of the
controls is a user worksheet. It sounds like you have a userform with a
worksheet control that you are trying to clear. I suspect the problem
is you are not refereing to the worksheet control properly. I think I
need to see your code and also I need to know where your code is
located.


If you have a Userform and you are trying to refer to an obect on the
userform you need to do something like this

Userform1.Listbox1.text


Now if you are trying to access an obect on the Userform and you are
trying to get an object on the userform you can use "ME" instead of
specifying Userform1

Userform1.Listbox1.text

or

me.Listbox1.text



Now if you are running code insdie a userform and need to access the
worksheet you need to specfy the worksheet. Activesheet usually won't
work


Sheets("sheet1").Range("A1")


A lot of problems occur because people don't specify the complete
location of obects. I don't leavve anything to chance. I always have
unique names for my worksheets. I also change the default Listbox names
and other objects to the function name. Instead of using Listbox1 I
will change the Listbox name to LastNamebox. I will change userform1 to
NameAdressForm.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

Clearing cells but also not clearing other dependent cells
 

I made a change to the 2nd mqcro on the following line. Intersect only
works if the two ranges are on the same worksheet. Range is usualy the
activesheet but when yo have auserform it sometimes doesn't work if the
focus isn't on a worksheet.

Set rngInterest = Intersect(Target,
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))


I added parent. The Parent of a Cell refernce is the sheet, the parent
of the sheet is the workbook. Excel doesn't know which worksheet the
range is refering to and giving an eror. You could also
change Parent to refer to a specific sheet like I made to the 1st
macro.

The 1st macro may fail depending on the object that is selected. It
will fail if you have a sheet that isn't a worksheet. Worksheet are the
spreadsheets that have rows and columns. You can also have sheets like
charts which aren't worksheets. You will get an error on the clear
macro if the activesheet is a chart. You also get errors sometimes when
the focus is on an object in the userform.. I added to the 1st macro a
rerfernce to either the Activesheet or a specific sheet.

Sub Clear_all()
with Sheets("sheet1")
.Range("D6:G6,A7,A11").ClearContents
.Range("D10:G10").ClearContents
End Sub

or

Sub Clear_all()
with Activesheet
.Range("D6:G6,A7,A11").ClearContents
.Range("D10:G10").ClearContents
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblVal As Double, rngInterest As Range, lngCount As Long
On Error GoTo ExitPoint
Application.EnableEvents = False
Set rngInterest = Intersect(Target,
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))
lngCount = Application.WorksheetFunction.CountA(rngInterest)
If lngCount Then
dblVal = Val(rngInterest(1).Value)
Application.Undo

If lngCount = 1 Then
rngInterest.Value = dblVal + Val(rngInterest.Value)
Else
MsgBox "Multiple Updates Not Permitted", vbCritical, _
"Computer Says No"
End If
End If

ExitPoint:
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

Clearing cells but also not clearing other dependent cells
 

Joel ...I was out for a few days. I tried copying your latest efforts but the
macro dialog bos kept poping up and lines five and six were red and I don't
know what that means. Being a novice I have decided to go with multiple
sheets for all the years until I have a bit more knowledge of VBA.
Thanks for all your efforts.

"joel" wrote:


I made a change to the 2nd mqcro on the following line. Intersect only
works if the two ranges are on the same worksheet. Range is usualy the
activesheet but when yo have auserform it sometimes doesn't work if the
focus isn't on a worksheet.

Set rngInterest = Intersect(Target,
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))


I added parent. The Parent of a Cell refernce is the sheet, the parent
of the sheet is the workbook. Excel doesn't know which worksheet the
range is refering to and giving an eror. You could also
change Parent to refer to a specific sheet like I made to the 1st
macro.

The 1st macro may fail depending on the object that is selected. It
will fail if you have a sheet that isn't a worksheet. Worksheet are the
spreadsheets that have rows and columns. You can also have sheets like
charts which aren't worksheets. You will get an error on the clear
macro if the activesheet is a chart. You also get errors sometimes when
the focus is on an object in the userform.. I added to the 1st macro a
rerfernce to either the Activesheet or a specific sheet.

Sub Clear_all()
with Sheets("sheet1")
.Range("D6:G6,A7,A11").ClearContents
.Range("D10:G10").ClearContents
End Sub

or

Sub Clear_all()
with Activesheet
.Range("D6:G6,A7,A11").ClearContents
.Range("D10:G10").ClearContents
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblVal As Double, rngInterest As Range, lngCount As Long
On Error GoTo ExitPoint
Application.EnableEvents = False
Set rngInterest = Intersect(Target,
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))
lngCount = Application.WorksheetFunction.CountA(rngInterest)
If lngCount Then
dblVal = Val(rngInterest(1).Value)
Application.Undo

If lngCount = 1 Then
rngInterest.Value = dblVal + Val(rngInterest.Value)
Else
MsgBox "Multiple Updates Not Permitted", vbCritical, _
"Computer Says No"
End If
End If

ExitPoint:
Set rngInterest = Nothing
Application.EnableEvents = True
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

Excel Live Chat

.

Clearing cells but also not clearing other dependent cells
 

I think it is just some type error in the line. Make sure your have
the lines of code exactly like I posted them. I also don't know why but
sometimes with the Intersect method you need application in front of the
method like below. there are somethings that work a little different on
one PC verses another PC. Never figured out exactly why. I sometimes
get things working at home and have to make slight changes when I get to
work. both PC's have windos XP installed and Office 2003.


Set rngInterest = _
Application.Intersect(Target, _
Target.Parent.Range("A7,D6:G6,A11,D10:G10"))


If you are getting two lines highlighted I suspect the line got too
long on the posting and I split the line above using the line
continuation character (the underline).


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=185387

http://www.thecodecage.com/forumz/chat.php