Excel 2007 - VBA Code running on all worksheets 1

[ladyck3]

This is being revisited:
Excel = Enter Static Date when Reaching 100%
thread707-1583430


HELP... this module or whatever its called is not running/working on all worksheets. When this is applied doesn't it cover the entire workbook?

Here's what I've changed (so its broken right?) and what I tried to do today... Lord help me.... Well I can't tell you what I did because i can't FIND THE CODE.. ok I found it (see what I mean I'm a mess)

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

So what we is this (to try and help you picture)

When Cell D3 = 100% then E3 has the date entered automatically and I have it formatted to turn Red if post the date in cell B9 and turn Green if the same as or prior to the date in B9 (Project Due Date)

Under this I have 3 sections with multiple tasks by team.

So when Team 1 reaches 100% (E7) then the date is automatically entered in G7
Likewise for Team 2 in E8 and G8
Likewise for Team 3 in E9 and G9

All of which turn red/green depending on due.

Problem is, the team ones work on some worksheets and not on others. Why?

Then... this morning I really blew it up (working on a copy of course) I added a Form button (Back to Master) and recorded a macro to return back to the Master List worksheet and save the file. Because I did not know how to get it to save and close (how do you stop the macro after your close or will the closing of the workbook while recording the macro tell the macro you are done and will it save it? Are you confused? Think of how *I* feel.

That's when I noticed, as I was adding this button to each of the 60 worksheets in the workbook that some of the projects were at 100% but the dates were not populating in the G7,8,9 cells.

NOW what am I doing wrong/not understanding/breaking?

I'm sure that's probably a better way, right?

Thanks....


LadyCK3
aka: Laurie

 

[ladyck3]

BTW.. the button I created is spanning G4 and G5 (merged)

LadyCK3
aka: Laurie

 

[ladyck3]

Findings:

If I go to a working page (the one that I know works) And go to the Developer's tab and View Code, 4 windows of code come up as follows:

(this is not in the workbook with the "Go to Master / Save" button, this is the what I thought was "known Good - working" on that is not working)

Top Dropdown says:  Worksheet   then Calculate
Option Explicit
---------------------------

Private Sub Worksheet_Calculate()
    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
End Sub

Top Dropdown says:  Worksheet   then Calculate
Option Explicit
---------------------------

Private Sub Worksheet_Calculate()
    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
End Sub

Top Dropdown says:  Worksheet   then Calculate
Option Explicit
---------------------------

Private Sub Worksheet_Calculate()
    If [D3].Value = 1 And [E3].Value = "" Then [E3].Value = Date
End Sub

Woksheet               Calculate

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

As you can see, this last one does not show the Option Explicit line, and that may be the issue, how do I get it to do that?

And do I need this same one (1-3) 3 times ... if not how do I delete two for them?
Edit > Clear (DEL)?


LadyCK3
aka: Laurie

 

[economistleo]

Where is the code at? You should put it in a new MODULE, not on a Worksheet -> On the vba editor, the 'tree' on the left, you have "Microsoft Excel Objects". Right-Click on it and add a `module'. Write your code there, and teka that `private' out of the Sub header.
(I can't really say I understood your question entirely, but i guess this would allow your code to run on all the worksheets).

 

[ladyck3]

Thanks Leo (may I all you Leo for short). I don't know VBA programming... I've been getting help, sporadially from the team here... so I get what you are saying (don't know much but AM Dangerous kinda thing)....

I did what you said, it named it module1 but ok...thing is I'm not sure that's the answer.. I have a few pages which this should NOT work on...

I kinda flustered right now because I think I'm way deep in the "Peter Principle" where one is promoted past their competency... they've asked for this tool, with Skip's help it was working but for some reason its now working on one of the individual worksheets ONLY.

I just went in to view the code again and all 4 of those codes listed above were not present, only the last one. Why is this changing?

I'm totally scared this thing is fried and that's so... not good.



LadyCK3
aka: Laurie

 

[SkipVought]

Hi,

In the VB Editor, VB Project Explorer, double click the ThisWorkbook Object.

The ThisWorkbook Code Window is now visible.

Select Workbook in the Object dropbown above the Code Window.

Select SheetCalculate in the Procedure dropbown above the Code Window.

This will fire each time a calculate is done on any sheet in the workbook. The Sh Object indicates with Worksheet is active and can be used in your called procedure if required.



Skip,

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

 

[ladyck3]

This file was moved from one server to another last night in an upgrade to the server but all of the info is self-contained so that should not matter, right?


I go to the Developers Tab
Visual Basic (first icon)
Project - VBAProject panel

I see:

VBAProject (CC Readiness 2010_SKIPFIX.xlsm) (hoping you hold your patience with me, this is what i renamed it)

If I double Click I see folder "Microsoft Excel Objects" and then the individual sheets are listed but therei s nothing on the right.

Below the Project - VBVAProject window is another on the left called "Properties - Sheet 10" because I've randomly selected that sheet.

But no codes appear... so I'm trying to follow what you are telling me but I'm not seeing what you are describing, all of it anyway.




LadyCK3
aka: Laurie

 

[ladyck3]

Ok, at the bottom of the list I found

ThisWorkbook

if I double click it I have a blank code window it says

(General) (Declarions)
Option Explicit

But no code... so what am I missing in this box? and is this my problem?

LadyCK3
aka: Laurie

 

[SkipVought]

In the dropdown the indicated (General), select Workbook.

Skip,

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

 

[ladyck3]

I found that, then in the Declarations I found SheetCalculate and this is the result:

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

So I saved it and then went to a known 'not working' sheet. One of the three groups is at 100% so I pulled a manually entered date and save it... it changed to 75%.

I re-entered the date and E8 changed to 100% but G8 is blank, no date.



LadyCK3
aka: Laurie

 

[SkipVought]

From THIS event, you must call your procedure and reference th Sh object. Probably this...

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

Skip,

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

 

[ladyck3]

OH my word, thanks so much Skip, that is working.

Now, as I started this thread I said that I had created a macro, and I have a button at the top of the sheet in merged cells G4:G5.

If I add this to each page is this going to screw up any of these other codes?

Here is the code:

First drop down (General)  second (Declaration)

Option Explicit

Sub Indx()
'
' Indx Macro
' Go to Index Page and Save
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master (2010)").Select
    ActiveWorkbook.Save
End Sub

So is this going to break anything? I just have to create a new button on each and every worksheet and use the idx macro name as the operator.

No breakie right?


LadyCK3
aka: Laurie

 

[SkipVought]

I assume that you have ASSIGNED each button to run Indx

'First drop down (General)  second (Declaration)

Option Explicit

Sub Indx()
'
' Indx Macro
' Go to Index Page and Save
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Sheets("Master (2010)").Select  'go to Index Sheet
    ThisWorkbook.Save               'Save
End Sub

Skip,

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

 

[ladyck3]

Yes that is what I've done and thank you kind sir... <crossing my fingers>.

If you are not at home but about to travel there, be careful.
Tomorrow be REALLY Careful... Luckily I have the ability to work from home, its going to be miserable.

Thanks!

LadyCK3
aka: Laurie

 

[ladyck3]

Oh and I posted a star for each tip, but I think only one registers.. I thank you so much!

LadyCK3
aka: Laurie