Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: formula does not work

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •