PDA

View Full Version : Solved: Update UNLOCKED cells on a different sheet?



Gingertrees
08-31-2010, 05:50 PM
On Sheet1, users can enter client names (column A) and what date the client last came in to sign paperwork (column B).

The "paperwork" is on other sheets in the workbook (let's use just Sheet2 for the example).

Excel then places in column C, what date the client must next sign paperwork on (column B + 182). Columns A and B are unlocked, column C is locked (to do the calculation).

I would like the user to have the option to update the Sheet1, column B (and thereby, column C) dates whenever they print out the paperwork on Sheet2. Since this is already a complex processor-intensive workbook and we have slow computers, I'd like to do this in a command button as opposed to a Workbook_Change.

It seems like this could be done with a Replace function, but I know not the syntax. Help?

Simon Lloyd
09-01-2010, 04:26 AM
is:
Private Sub CommandButton1_Click() 'replace
With Sheets("Sheet1")
.Unprotect Password:="password"
.Range("B4") = Sheets("Sheet2").Range("D2")
.Protect Password:="password"
End With
End Sub

Gingertrees
09-01-2010, 07:34 AM
Partially there. Problem is, B4 was just the example that corresponds to "Mary". If I was doing the update for "Peter" or "Paul" instead, how do I tell Excel which cell in Sheet1, column B to update?

Simon Lloyd
09-01-2010, 07:59 AM
try this one instead:
Private Sub CommandButton1_Click() 'replace
Dim fRng As Range
On Error GoTo Nxt
Sheets("Sheet1").Unprotect Password:="password"
Set fRng = Sheets("Sheet1").Columns(1).Find(What:=Sheets("Sheet2").Range("B3").Value, After:=Sheets("Sheet1").Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
fRng.Offset(0, 1) = Sheets("Sheet2").Range("D2").Value
Sheets("Sheet1").Protect Password:="password"
Exit Sub
Nxt: MsgBox "Invalid name", vbOKOnly, "Name not found"
End Sub

Gingertrees
09-01-2010, 08:45 AM
Excellent! I love discovering new stuff with VBA. :) Thank you Simon :)