Hi:

Win2k
Excel2k

The code bellow in "***" works when the Sheet1 and sheet2 are not protected, that is when there is no protection at all. However, when I protect the sheet and then unprotect it to perform a tast, namely the one in the code that is surrounded by "*", it no longer works. Is there a way to bypass this so it can work again?


In sheet2 I add two values
cell("a1").value = 0:00:20
cell("A2").value = 0:00:30
sheet1 j cell value = sheet2.cell("a3").value = the sum of both a1 and a2

Does anybody know how to enter these formulas in excel vb code:

=SUM(A1:A2)
=AVERAGE(A1:A2)

I figured if there is no way aroung my first problem, then I will perform the calculation in sheet1, but I don't want the user to see it.


Private Sub Openbutton_Click()
ActiveSheet.Unprotect Password:="123"

range("D" & CStr(ActiveCell.Row)).Value = "X"
Range("E" & CStr(ActiveCell.Row)).Value = ""
Range("F" & CStr(ActiveCell.Row)).Value = ""
Range("C" & CStr(ActiveCell.Row)).Value = ""
If Range("D" & CStr(ActiveCell.Row)).Value = "X" Then
Range("L" & CStr(ActiveCell.Row)).Value = "1"
Range("H" & CStr(ActiveCell.Row)).Interior.ColorIndex = 0
End If
Call StopTimer
Range("J" & CStr(ActiveCell.Row)).Value = Range("J2").Value
If Range("J" & CStr(ActiveCell.Row)).Interior.Color = vbYellow Then
Range("J" & CStr(ActiveCell.Row)).Interior.Color = vbGreen
Range("I" & CStr(ActiveCell.Row)).Interior.Color = vbYellow
Else
Range("J" & CStr(ActiveCell.Row)).Interior.Color = vbGreen
End If
Call StopIt
Range("J2").Value = ""

'************************************************* *
If Sheet1.CommandButton1.Caption = "Stop Old" Then
Dim newval As Date
newval = Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MMS")
Sheet2.Range("A2").Value = newval
Range("J" & CStr(ActiveCell.Row)).Value = Sheet2.Range("A3").Value
Range("J" & CStr(ActiveCell.Row)).Interior.Color = vbGreen
Sheet1.CommandButton1.Caption = "Stop Old"
'Sheet2.Range("A2").Value = ""
'Sheet2.Range("A1").Value = ""
End If
'************************************************* ******
ActiveWorkbook.Save
UserForm1.Hide
Call replace_active_cell
ActiveSheet.Protect Password:="123"
end sub

Hi

It is a little unclear where the “Private Sub Openbutton_Click()” code is written, or which sheet is the active sheet.

On the assumptions that sheet1 is the active sheet, both sheets are protected and that the cell written to in sheet 2 are individually protected, then you will not be able to write to Sheet2 because you have only unprotected the ActiveSheet not both!

Just a thought


To enter the formula in the cell

Use

Cells(x,y)=”=SUM(A1:A2)”
Cells(I,j)=”=AVERAGE(A1:A2)”

OR

Range(“A3”)=”=SUM(A1:A2)”
Range(“A4”)=”=AVERAGE(A1:A2)”


MTB