ZEROing A range of Cells on multiple work sheet from a User form Command Button

nm766 · Feb 20th 2011

Hi Friends,
Can anyone help me on the following issue :

(a) I have a work book say MY PROJECT 1.

(b) In that I have a work sheet say DAILY SCORE DATA. This Data is stored in the Cell Block of E2 to M67. M Column is the sum-total of Column ‘E’ to Column ‘L’ for each row. Means E2+F2+G2+H2+I2+J2+K2+L2 = M2 (and similar for other rows till 67th row). These data are fed from a User form and get stored when OK button on the form is clicked.These data input from the user form is also reflected in another worksheet Say BULL SCORE DATA (Y2:AF67) for Columns E : L. The data of Column M(sum total) is reflected in Column Q of the sheet simultaneously on clicking the OK button.

(c) I need to reset the data in the above said cell blocks (E2:M67 in Sheet DAILY SCORE DATA and Y2:AF67 and R2:R67 in Sheet BULL SCORE DATA) on next day (assuming the data was entered today evening) by clicking a command button (named RESET) on the same user form. As a result the data in cells should reset to ZERO. This should be conditioned with the computer’s date calculation so that if I want to reset on the same day , it won’t reset. It will be reset only on the next day when the computer date changes to next day.

(d) It may be noted that, some rows in random, might contain ZERO data previously. So while resetting, it should not create any problem.

Your precious help will be highly appreciated.
Thanks in advance.

With regards,
Nm766

Bill Rockenbach · Feb 20th 2011

Re: ZEROing A range of Cells on multiple work sheet from a User form Command Button

Designate a cell to contain a control date that will be posted as today's date when the Daily Score Data is posted. The Bull Score Data can not be cleared or posted to until the control date is less than Tody's date. If your are posting the Daily Score more than once in teh day you may need an override feature.

Code

Option Explicit


Private Sub cmbReset_Click()
'clear old data
    With Sheets("Bull Score Data")
        If Sheets("Bull Score Data").Range("A1") >= Date Then
            MsgBox "To early to clear old data"
            Exit Sub
            .Range("Y2:AF67").ClearContents
            End If
        End With
        With Sheets("Daily Score Data")
            .Range("E2:M67").ClearContents
        End With


    End Sub


Private Sub cmbOK_Click()
'post new data
    If Sheets("Bull Score Data").Range("A1") >= Date Then
        MsgBox "To early to post new data"
        Exit Sub
    End If
    Sheets("Bull Score Data").Range("A1") = Date  ' A1 or some unused cell
    With Sheets("Daily Score Data")
        'post new data
    End With


End Sub

Display More

nm766 · Feb 21st 2011

Re: ZEROing A range of Cells on multiple work sheet from a User form Command Button

Dear Bill,
Thanks for your quick response. I will come back to you after implementing & checking the code. Thanks a lot for your precious help.

Regards,

nm766

ZEROing A range of Cells on multiple work sheet from a User form Command Button

Participate now!

Share