VB code not updating Excel Sheet 1

[sentinelace]

Okay now, my master sheet doesn't seem to be updating but the code seems to work perfect. Sheeet 2 updates, but sheet 1 isn't. Here is my code:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:15")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "ThisWorkbook.Switch"
End Sub
Rate this post

[westconn1]

Okay now, my master sheet doesn't seem to be updating but the code seems to work perfect. Sheeet 2 updates, but sheet 1 isn't. Here is my code:

i do not see anything in your code that changes sheets, so nothing to update
using pause as both a procedure and a variable is very likely to cause problem, though i do not see that pause is called from anywhere

i do not really see the point in looping through all the sheets and activating them, but i suppose you have some reason

[Chrissy]

I think if we had a better understanding of what you are trying to accomplish it would be easier to offer help.

[sentinelace]

Basically I have two sheets. This is displayed on TV screen 24/7. The code above cycles through the two sheets and updates one of the fields automatically based on the date. It works perfect except the first sheet isn't updating. Make more sense?

[Chrissy]

Is there code behind he the worksheet_activate event for each sheet?

[sentinelace]

all this code is in thisworkbook

[Chrissy]

I guess I'm trying to understand how it is updated... is there a formula in a cell that gets updated when the date changes or is there other code that executes, etc.

[sentinelace]

Yes sheet one has cells with formulas that update the cells. Sheet 2 is all text other than the vbcode field.

[Siddharth Rout]

Can I see your file?

Sid

[VBFnewcomer]

Can I see your file?

Sid

can I see you!!!
Ha Ha Ha

[Siddharth Rout]

can I see you!!!
Ha Ha Ha

I am there... I am there

Sid

[sentinelace]

Attached is the file. The forumlas are based from a software piece that is installed on the clients, so that is how they update. any help is appreciated.

[Siddharth Rout]

You have got the locations mixed up.

You have pasted the Workbook Open event in the module and the module code in Workbook Area... Try this...

Sid

[sentinelace]

with the one above the spreadsheet isn't switching anymore and I get an error " cannot run the macro, it maybe not be in this workbook or disabled." Now what?

[Siddharth Rout]

Ok do one thing...open the workbook and see where have i place the code... Make those changes in your original file...

Sid

[sentinelace]

I just extracted the xls you posted. Not sure what else you mean. I get that macro error.

[Siddharth Rout]

Ok Do this...

Open your original workbook

Click on Menu ~~~> Tools ~~> Macro ~~> Visual basic Editor and copy the code in the module and paste it in the workbook code area instead of module. similarly copy the code from the Workbook code area and paste it in the Module. Hope that makes sense?

Sid

[sentinelace]

Ok Do this...

Open your original workbook

Click on Menu ~~~> Tools ~~> Macro ~~> Visual basic Editor and copy the code in the module and paste it in the workbook code area instead of module. similarly copy the code from the Workbook code area and paste it in the Module. Hope that makes sense?

Sid

I am running Excel 2010. Also, did that and still get that macro error.

[Siddharth Rout]

Put this in the Workbook Open event

Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

[sentinelace]

Okay. I want to make sure we're on the same page. Here is ThisWorkbook

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "ThisWorkbook.Switch"
End Sub

and here is module 1:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:15")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

This is not working. Cells don't update on master sheet. Now when I click on the excel doc, the goal is for the macro to autorun and obviously have the cells update on both sheets.

[Siddharth Rout]

Please re-upload your workbook with the changes that you have done.

Sid

[sentinelace]

Here you go. Slap me if missed something.

[Siddharth Rout]

SLAP SLAP!



Ok I have checked it and my guess was right...

Compare my post 19 and your post 20 once again please. Do you see some difference? Any difference?

Sid

[sentinelace]

okay I see what I did. I forgot to change it to "switch". Now it does switch again but no filelds are updating on sheet 1, not even the time.

[Siddharth Rout]

Ok. Now the macro is running but where is the code for updating the sheets? Or how is it supposed to update?

Sid

[sentinelace]

Page 1 (master). is all forumlas. That sheet works perfect if the macro is not running. It updates based on the forumla.

[Siddharth Rout]

Is this what you are trying to do?

Public Sub Switch()
    Dim ws As Worksheet
    Do
        For Each ws In ThisWorkbook.Worksheets
            ws.Activate
            Calculate
            Application.Wait Now() + TimeValue("00:00:15")
            If GetAsyncKeyState(vbKeyShift) Then Exit Sub
            DoEvents
        Next ws
    Loop
End Sub

[sentinelace]

I added that and the forumla still doesn't update until I kill the macro

Module 1:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Calculate
Application.Wait Now() + TimeValue("00:00:15")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

[Siddharth Rout]

Try

ws.calculate

Sid

[sentinelace]

still not updating, any other ideas guys?

[sentinelace]

bump. Anyone?

[westconn1]

i made up a workbook for testing, even with a UDF the updating worked correctly (with ws.calculate), so it must have to do with your external data source which we can not test at all

there was no need to start a new thread to say you did not have a solution in this one

[sentinelace]

So what now? Is there a way I can simply use the excel with just sheet 1 and have it switch between html and excel?

[westconn1]

at no point in the past have you mentioned html, you have been talking about 2 worksheets

the only way for anyone to help you is to have the datasource for testing

[sentinelace]

I am just trying to figure out a way to make it update. Makes no sense

[sentinelace]

what about completely stopping the macro every so many seconds. Since that seems to have it update when it stops, then loop. That possible?

[westconn1]

ok, try using application.ontime, to switch sheets, instead of application.wait

afaik during the wait period nothing can happen in excel

not tested

Sub Switch()
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
If ActiveSheet.Index = Sheets.count Then
    nexsht = 1
    Else: nexsht = ActiveSheet.Index + 1
End If
Sheets(nexsht).Activate
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

[sentinelace]

I get a compile error. Where do I put this code? Do I replace all my existing?

[westconn1]

just replace your existing switch procedure

does not look like you are using pause procedure anyway

I get a compile error

what error message?
what code is highlighted?

[sentinelace]

Here is what I have for Module 1:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub
 
Public Sub Switch()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      ws.Calculate
      Application.Wait Now() + TimeValue("00:00:15")
      If GetAsyncKeyState(vbKeyShift) Then Exit Sub
      DoEvents
    Next ws
  Loop
 
End Sub

This workbook looks like this:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

So what am I replacing here?

I tried:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Sub Switch()
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
If ActiveSheet.Index = Sheets.Count Then
nexsht = 1
Else: nexsht = ActiveSheet.Index + 1
End If
Sheets(nexsht).Activate
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub
 
Public Sub Switch()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      ws.Calculate
      Application.Wait Now() + TimeValue("00:00:15")
      If GetAsyncKeyState(vbKeyShift) Then Exit Sub
      DoEvents
    Next ws
  Loop
 
End Sub

Public Sub gets the compile error. Do I need to do anything to thisworkbook? I assume I edited it wrong.