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

 

[ladyck3]

When I open the workbook I'm prompted for a password.

I entered the password (which the end user will not know so this really can't work, can it?)

I get runtime error 28
Out of stack space

When I hit 'debug' the code gets highlighted.

.Calculation = xlCalculationManual

By default, the Tools > Options > Formulas > Autocalculate is turned on and I can't seem to set it to manual and have it keep.

I tried opening excel and selecting this option then closing the program. I reopen and its still automatic.

I'm sure this is the problem why the code is breaking right?

I might have to be happy with the unprotected cells and 'trust' mode.


LadyCK3
aka: Laurie

 

[ladyck3]

my workbook is hosed right now.... the master page the first project link is model xxxx and that is what is 'flashing' in the forumlua bar

When i locate the worksheet for model xxxx that two has the responsible party's name flickering or flashing in the formula bar.

What the HECK have I done?

Seriously, are we ready to say UNCLE on this?

LadyCK3
aka: Laurie

 

[SkipVought]

You have TWO DIFFERENT THINGS...

Workbook Password - user must know PASSWORD, to keep unwanted users from opening workbook

Worksheet Password - user must know password, ONLY to UNPROTECT the sheet. Any user who can open the workbook, can select and change UNLOCKED cells.

Is there any confusion with passwords?

Skip,

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

 

[ladyck3]

If I am to understand right, on the ThisWorkbook Object window it looked like this initially:

Option Explicit

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  With Sh
  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 With
End Sub

It should now appear like this???

Option Explicit

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Sh.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 With
End Sub

When I try to enter a date into E12,
I get

Compile Error:
Invalid or unqualified reference

It opens the code above window and if I click ok the Line

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

turns yellow and the first line [D3] is where the cursor rests with it highlighted.
The yellow arrow selects the entire Private line but apparently it fails at D3 which sould update to like 9% or some such.


LadyCK3
aka: Laurie

 

[SkipVought]

No.

The only things changing in the ThisWorkbook object are to paste in the two events as previously posted, after which you will have THREE Workbook events.

Skip,

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

 

[ladyck3]

We're somewhere but I'm not sure where....
I did what I think you are asking and now when I enter dates into Team1 the workbook locks up and is calculating its butt off but going nowhere and I lose functionality because its never endingly calculating.
I can't scroll up the window to see what is happening but "Completed Date" is flashing/quivering in the formula bar. I had to use the task Manager to End Task on the application.

I hit SAVE just in case and reopened the workbook, it has calculated 100% for Team1 but the date is not entered in cell E7

D3 does how 33%

Here is how I have the ThisWorkbook object now coded.

Option Explicit

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  With Sh
  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 With
End Sub

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

Am I getting this... these are 2 "EVENTS" you were talking about along with the calculation one making it 3....

I just pasted them in the window.... the program split them at top and bottom with the other code in the middle...

My "To Master & Save" macro is not working but I'm not worried about that 'yet' because it may just be all this messed up code I've created... right?

I hope I can understand all of this and get it right in the next 45 minutes to 1 hr... that's all the time I have for today.

Skip, I hope you see that I'm trying.
And ALL OF YOUR TIME... I really appreciate the help and hand holding.


LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

You need to think analytically about the logic of how your workbook works.

When user enters data, you want stuff to happen.

But the stuff that happens, must not cause other stuff to happen, that you don't want to happen.

So we have the VERY important application.enableevents=false statment that will keep 'other' stuff from happening -- and that means when the VBA CODE assigns values, you probably don't want all this Change/Calculate code to trigger another event, cuz it can SNOWBALL.

So the code I posted that DISABLES/ENABLES events, need to be in each of your events that assigns values to a sheet.

Understand?

Skip,

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

 

[ladyck3]

Skip,

I understand what you are saying that there are multiple things going on in my workbook/worksheets.

ActiveSheet - when a date is entered in any cell from E12 to E37 (green on the picure) that the things happening are as follows:

Cell G7 will increase its percentage (Team score)
Cell D3 will increase (Project Score)

At the same time:

The score on the master Depending on the team (Columns M, N and O and the corresponding row linked to the project worksheet changes but that's no calculation that's simply cell=reference.

On the master still, the total project score in a cell=reference updates in Column K but all data on the Master is simply a cell=cell reference to the individual project worksheets by model.

So as I think this through I have two things happening when a date is entered, G7:G9 can change depending on the Team and the Project (D3) will update as well.

I had two separate calculate code windows a long time ago in this process and you said only one CALCULATE so they are added together here.

you state this earlier

Application.EnableEvents =false

and you said to Enable and Disable but I'm so turned around I don't know what goes with what anymore.

WHERE does this go? Where do I put it?

I am syntax / language challenged (stupid).

You have probably explained this to me now about a bazillion times but I don't know what I"m reading and I don't know how to write it and its not up to you to do this for me and I'm about to fail and that is not an option.

Peter Principle has been invoked:
Promoted beyond one's capabilities.

I just don't get it... I know what I want this to do but I don't understand all that you've presented and I know you are trying so hard.

LadyCK3
aka: Laurie

 

[ladyck3]

OH MY GOD I THINK I'VE GOT IT

I added those two sections to the code for the master page the one with the K4:K150 and it seems to be working as it should.

Did *I* actually GET IT?

And my macro to go to the Master and SAVE is working too.

Skip your patience has paid off???? <she said cautiously)



LadyCK3
aka: Laurie

 

[ladyck3]

Nope its locking up and I have to leave for the day...
I just created data to make one team turn to 100% and its locked.

It let loose and let me close it but the dates are not being posted in cells E3, and G7:G9.

I'm crazy upset with myself over all of this.

I do have to end here for the day.... I'll check back later to see if there are any more replies but I understand if you are totally fed up... I'm about there.

LadyCK3
aka: Laurie

 

[ladyck3]

And my goto master/save macro stopped working again.

ARGH!

Shutting down PC for now. <sigh>

LadyCK3
aka: Laurie

 

[SkipVought]

Put a BREAK at the beginning of each event.

Make a change on the sheet where you expect stuff to happen.

When the code stops, then STEP thru the code, noting exactly which procedure you are in and when it 'switches' to another procedure.

As you do this you can look at the activesheet and observe what other changes might be occuring.

This kind of DEBUGGING is what you have to do sometimes. I'm currently in the process of doing somthing very similar with an application that I'm working on. It can take alot of time, but you will learn in exactly what sequence things are occurring.

Skip,

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

 

[ladyck3]

I can appreciate that but I don't know how to put a break in and I don't know how to follow that stuff, ... that's my dang problem, I don't know HOW! <frustrated at me>

Not sure how to enter a break or start a new one ... etc...

I just don't know how.... your help file went over my head.

Good luck on your app

LadyCK3
aka: Laurie

 

[SkipVought]

The enter a BREAK, Example:

Place you cursor in the line you want the BREAK on (or off) and hit F9...

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)[highlight]
  With Sh[/highlight]
  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 With
End Sub

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

Skip,

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

 

[ladyck3]

Skip,

Thanks I did that and it stops wherever and I don't get any access back.

I'm sorry, I have no clue what I'm doing and probably causing myself more grief than the actual problem.

I have also been through so much today that I have no clue where at I am at anymore...

UNCLE.

I just can't take up any more of your time. I just don't know what I'm going to do anymore. I can't read the code and understand so I try to follow what you say but I know that a misplaced space or comma or ' can cause so much trouble and I just don't understand.

I am at the point where if had someone explaining everything and pointing here or there.. but that's not possible, I don't know anyone who I can turn to...

Thanks but maybe another day we can revisit this.

I think I'm beyond help here. I did the F9 thing and it locks up the entire worksheet to the poitn that every move is DING DING DING of the machine and I have to use the task manager to close the application.

Thanks...

I just can't waste your time any more.. but i appreciate you hanging in there with me.

If you think I'm close, fine... but I think I'm needing to go back to square 1 and I just cant. I've got more than one code window for the same function so I don't know how the hell that happened... like the G7:G9 functions I think I have 2 pages of that and the K4:K150 I Think I have two fo those too...

I'm SOOOOOOOOOOOOOOOOOOO Stressed out by this right now.

Thank you really....

Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

Once the debugger stops at a break, you can Step (F8) thru the code.

Skip,

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