You're welcome and you have a good weekend, too.

Steve E wrote:

All I can really say is "THANKS".

Hope you have a great (and safe) holiday!

Steve

"Dave Peterson" wrote:

Change your code to include the password.

ActiveSheet.Unprotect password:="hi"
'do the work
ActiveSheet.Protect password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

====
And remember to protect the project from snoops.

Inside the VBE
tools|VBA Project Properties
Protection tab

(give it a memorable password).

==============
But do remember that worksheet protection is not really a security setting.
It's easily broken. So don't put stuff you don't want to share on any worksheet
of any workbook you're gonna share with others.

The VBA project protection isn't secure either. But most users don't even know
it exists (in my experience).



Steve E wrote:

Sheesh. How long did it take for you to learn this stuff...

Anyway... you da man.

The D18:D34 range was wrong... should have been D18:D32 this is a range
where a user enters a product model and the adjacent cells (L:V) are
variables entered dependent on the specific model selected (and therefore
entered) in the D cell. All of the inputs for L:V are dyanmic lists based on
the input to the left...

Now I have one more stupid question.

I have a form button on the top of this worksheet to allow a user to clear
out all of the information that they've entered (to make double sure that
there aren't any residual inputs that might screw them up down the road.

I recorded a macro to clear all of the necessary ranges and it worked fine
until I protect the sheet... then the macro prompts my user for a password...
which kind of defeats some of the reason that I have the sheet protected in
the first place [realizing that someone who prowls this usergroup already
knows how to crack thru the password sheet protection]... how can I convert
my macro to VBA and assign it to a command button?

Sub UnprotectClearFormProtect()
'
' UnprotectClearFormProtect Macro
' Macro recorded 9/1/2006 by Steven W. Edwards
'

'
ActiveSheet.Unprotect
Range("D1:D2,D9:G9,D12:G12,J1:L9,C18:D32,G18:V32,C 37:G43").Select
Range("C37:D42").Activate
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks again for all of your help. You (and the rest of the folks that make
this user group go) rock!

Steve

"Dave Peterson" wrote:

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson