Clearing cells but also not clearing other dependent cells

[rtlars]

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

 

[rtlars]

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.

 

[rtlars]

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.

 

[rtlars]

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

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/showthread.php?t=185387

Excel Live Chat

.

 

[rtlars]

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.