Find & Replace

[tomhelle]

I have a workbook consisting of many unique worksheets. Each worksheet has a
similar but not identical layout and format. Within each worksheet there are
certain cells that are unprotected that enable inputs by the user. I want to
add a "dash board" feature (worksheet) that will enable the user to make a
single input that will change the appropriate cells on each affected
worksheet. Within each of these worksheets, I want to retain the user's
ability to overtype the input that was created by the "dash board". My intent
is to reduce the quantity of redundant user changes but enable them to
continue to have the freedom to make unique adjustments to any of the
individual sheets.

Hope this is clear enough - thanks in advance for your help!

 

[Gary''s Student]

Say the input cell is Sheet1 - B9
Say we want to allow the user to directly type in Sheet1 - B9 or enter the
value in dashboard - A1.

Enter the following event macro in the dashboard code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Sheets("Sheet1").Range("B9").Value = r.Value
Application.EnableEvents = True
End Sub

This will give you the ability to either enter data directly in Sheet1 - B9
or indirectly thru the dashboard.