Excel = Enter Static Date when Reaching 100% 2

[ladyck3]

I was in the MS OFFICE Forum and they lead me here.
I know how to get into the VBA window and paste code, that's my knowledge of VBA for the most part. I am in desperate need of assistance and I'm not a programmer. I know Excel very well up to the point of needing code and I freeze.

With these conditions, will you still help?

If so, here is my dilemma. D3 is the final SCORE or Percentage in a long line of calculations. When D3 turns or calculates to 100%, I need E3 to populate with a static date. This is for a scorecard of sorts and we need to know WHEN (not date/time) just the date... when D3 turns 100%.

I will apply conditional formatting at that time..

If D3 turned 100% on or before the date in B9, then E3 will turn GREEN, if it is after the date in B9, it will turn RED.

Knowing how all powerful VBA is, I'm sure there must be a way to do this.... I can do the formatting after I get the cell to hard code the date...

However, I believe if you are privy to all f these criteria and can do the date part, VBA can also handle the formatting, right?

If I am to naive for you to consider helping me with this, please help me to find a resource.

SINCERELY and humbly...


LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

Guess what? When you venture into the world of "programming", sometimes ya gotta do programmy thangs when stuff happens.

And, believe me, STUFF happens!

But I'm pleased that it seems to be working for you. The only gacha might be that ANYONE can go in and change a Date in column L to whatever they might like. I would want to PREVENT that.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Well I "would" want to prevent that. Precisely. And I am not downplaying "programmy" things... in any way shape or form......

I 'seriuosly' just do not have the mental capacity to take programming courses... I would not 'retain' the knowledge. My memory is SO poor.

I read the article referenced and my head just went "HUH"...

I totally get that programming is required and I profusely apologize for my lack of knowledge and am so totally appreciative when I do get help from this resource and you Specifically..

I give back randomly when I can but I'm not as knowledgeable as I would wish to be... do I ask too much? If I do (and be honest) I will respectfully remove myself. I honestly fear being a burden any time I log in. I hope that I'm not... too much.

You suggestion of prevention, I do have the spreadsheets password protected so they are unable to manually fix the project completion dates.

That's what the thought of using check boxes on a previous post was all about. If there is a simple check box that the user can populate, which will then take that current date placed into another cell automatically (protected so once placed its done) but then what about checking in error.

Oh I think having the sheet protected is plenty enough, right? I'm dealing with folks that can barely open excel much less try to hack it.

LadyCK3
aka: Laurie

 

[SkipVought]

If your sheet is password protected, how does your code run and add the date?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Oh crud.... well that's why it was not working on all pages.. it works when I testing it and the pages are not protected but when the thing is protected and the shared, the dates are not working.

Oh I've got a headache.... I have to unprotect those cells.

I was doing all of my testing and when all worked, I then password protected it and said GREAT its working... but its not.

I'm over my head right? Right.

LadyCK3
aka: Laurie

 

[SkipVought]

Not necessarily.

here's the drill....

in the worksheet_calculate event...

1. UNPROTECT the sheet

2. DISABLE events

3. run your loop

4. ENABLE events

5. PROTECT your sheet

You DISABLE events by

Application.EnableEvents =false

Or you can set Application.Calculation to xlCalculationManual before the loop if it is not set to xlCalculationManual.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

SERIOUSLY? I'm lost....

I have the code opened that shows the 4 events
Private Sub Worksheet_Calculate()

IF [D3] ....
IF E7, E8, E9 ....

and now you say
-------------------------------------
in the worksheet_calculate event...
1. UNPROTECT the sheet (Literally just unprotect the worksheet)
2. DISABLE events (how do I do this, what do you mean?)
3. run your loop (sorry for being so stupid... ????)
4. ENABLE events (Re-enable whatever I disabled that I don't know yet??)
5. PROTECT your sheet (Literally go ahead and protect the sheet again after the project is completed at 100%?)

I know you want me to run a test but when I think I might have my hand almost on the handle, ya yank the pot away...

Oh... I see, you describe how to disable the events by using the code in the code box but where do I put that?

But what does this do? Going through all of the above?

And what does this mean:


Or you can set Application.Calculation to xlCalculationManual before the loop if it is not set to xlCalculationManual.


You've lost me

LadyCK3
aka: Laurie

 

[SkipVought]

Turn on your macro recorder and record the process of unprotecting a sheet.

Do the same to protect the sheet.

Use the recorded code in your process. You might need to modify. Post back with your recorded code for help.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok here's what I have in the mcro window

LadyCK3
aka: Laurie

 

[ladyck3]

The actual code:

Sub Unprotect()
'
' Unprotect Macro
' Unprotect Sheet
'
' Keyboard Shortcut: Ctrl+u
'
    ActiveSheet.Unprotect
End Sub
Sub Protectworkbook()
'
' Protectworkbook Macro
'
' Keyboard Shortcut: Ctrl+p
'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sorry I did not include on the previous post.

LadyCK3
aka: Laurie

 

[SkipVought]

So here's what to do...

Copy this into any MODULE

Sub Unprotect[b]Sheet[/b]()
'
' Unprotect Macro
' Unprotect Sheet
'
' Keyboard Shortcut: Ctrl+u
'
    ActiveSheet.Unprotect
End Sub
Sub Protect[b]Sheet[/b]()
'
' Protect[b]Sheet[/b] Macro [red][b]sheet not workbook[/b][/red]
'
' Keyboard Shortcut: Ctrl+p
'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

In your Sheet Code event window...

Private Sub Worksheet_Calculate()
  Dim r as range, oldcalculation
'unprotect sheet
  UnprotectSheet

'set calculation to manual
  with application
     oldcalculation=.calculation
     .calculation = xlCalculationManual 
  end with

  for each r in Range("K4:K150")
     with r
       if .value = 1 and .offset(0,1).value = "" then .offset(0,1).value = Date 
     end with
  next

'reset calculation to what it was
  with application
     .calculation = oldcalculation
  end with

'protect the sheet
   ProtectSheet
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Do I do the same with this code? Preface and end with the same statement?

Private Sub Worksheet_Calculate()

    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
    
    If [E7].Value = 1 And [G7].Value = "" Then [G7].Value = Date
    
    If [E8].Value = 1 And [G8].Value = "" Then [G8].Value = Date
        
    If [E9].Value = 1 And [G9].Value = "" Then [G9].Value = Date
    End Sub

LadyCK3
aka: Laurie

 

[SkipVought]

Absolutely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I did what you suggested above but every time I enter a date into the 'task' completion area that is on a saved worksheet (individual product) with those cells unlocked I get an error

Compile error:
Sub or Function not defined

And it highlights the line above End Sub which is ProtectSheet

BTW, the password I'm using is a space (one tap of the spacebar) not a biggy password but it protects and that's all I need.

There is no prompt to protect/unprotect and that's good because the end user would not know the password but I'm confused now it knows what the password might be to unprotect/protect.


LadyCK3
aka: Laurie

 

[SkipVought]

WHERE did you put the ProtectSheet & UnprotectSheet procedures? You did NOT put them in a MODULE, which is where they belong.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I'm dealing with two things.

The Master (2010) tab which is the one that has the K4:K150 code and the rest (60+) are the pages which use the code for the D3, G7, 8 and 9 cells code.

I'm on one of the individual sheets and enter a date in cells that will result in the date entered in G7, I get the compile error and it takes me to the "Protect" line as described in my last post for the Master page (the one with the k4:K150 information on it).

Why is it calling that window? The only time the Master comes into play is when D3 turns to 100% after G7, G8 and G9 each independently turn to 100%.

Here's a copy of what my sheet looks like (individual product sheet).

I should have done this a long time ago.

LadyCK3
aka: Laurie

 

[SkipVought]

Because you are CALCULATING (which kicks off your code) and if your CALCULATION is set to AUTOMATIC (Tools > Options > Calculation TAB), Excel Calculates the ENTIRE WORKBOOK, which means--GUESS WHAT?

So SINCE that sheet is not ACTIVE ==> TILT!

You must be careful with Calculate as a trigger. As a practice, I have Calculation set to MANUAL -- ALL THE TIME! I hate to be waiting for other sheets, other workbooks, -- the world passes by--waaaaaaaaiiiiiting!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I have Calculation set to MANUAL -- ALL THE TIME!

Doesn't that cause problems sometimes? I'll occasionally have a workbook refuse to recalculate unless I enable calculations, close the file, and open it again.

 

[ladyck3]

Ok, so I'm confused yet again.
I looked at where I pasted the information, into the INDX module and realized that i missed a couple of crucial "enters" as the code was all green... my bad.

I fixed it and I turned off the calculation to Manual but the percentages then do not automatically update (no kidding sherlock, I know) which is a problem for my users who will not see the changes and will FA-REEEEK.

But I'm getting errors in the code for the master sheet. What you ask, but its so confusing right now (this is the one with the K4:K150 on the Master page.

I did not catch it all, I'm sorry.

And when I do save it, and it recalculates, the dates are not being posted in E3 and G7, 8, and 9 so now I've lost that functionality... ARGH the further I think I might be getting the behinder I actually get.

I know its stupid user... but I'm almost over frustrated so I can imagine how you are feeling with trying to help me.



LadyCK3
aka: Laurie

 

[ladyck3]

Ok I've cleaned up some things I think.
Now what happens is when I enter a date in cell E12 I get a password prompt.

The end user will not know the password. What have *I* done wrong?

Yes I have the Automatic Calculation option turned on... I've got to or this tool will just not work for me, the end users will never understand this.... they need to see things change.


LadyCK3
aka: Laurie

 

[SkipVought]

What I would do is ONLY calculate the SHEET that the user enters data into.

So here's the approch.

1) In the ThisWorkbook Object Workbook_Open event, set Application.Calculation = xlCalculationManual

Private Sub Workbook_Open()
   Application.Calculation = xlCalculationManual
End Sub

2) In the ThisWorkbook Object Workbook_SheetChange event, perform a Sheet Calculation any time the user makes a CHANGE

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   sh.calculate
End Sub

On your most recent post, the user should NOT know or need to know any password. That's for YOU. And, BTW, that password, must be used in your unprotect & protect code.

You should have the cells that the user can change, UNLOCKED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}