VB code not updating Excel Sheet 1

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

Quote:

---

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

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

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?

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

all this code is in thisworkbook

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.

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

Can I see your file?

Sid

Quote:

---

Originally Posted by koolsid

Can I see your file?

Sid

---

can I see you!!!:wave:
Ha Ha Ha

Quote:

---

Originally Posted by VBFnewcomer

can I see you!!!:wave:
Ha Ha Ha

---

:D

I am there... I am there

Sid

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.

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

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?

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

Sid

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

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

Quote:

---

Originally Posted by koolsid

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.

Put this in the Workbook Open event

Code:

---

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

---

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.

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

Sid

Here you go. Slap me if missed something.

SLAP SLAP!

:D

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

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.

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

Sid

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

Is this what you are trying to do?

Code:

---

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

---

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

Try

Code:

---

ws.calculate

---

Sid

still not updating, any other ideas guys?

bump. Anyone?

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

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?

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

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

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

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

afaik during the wait period nothing can happen in excel

not tested

vb Code:

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

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

just replace your existing switch procedure

does not look like you are using pause procedure anyway

Quote:

---

I get a compile error

---

what error message?
what code is highlighted?

Here is what I have for Module 1:

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
      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:

Code:

---

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

---

So what am I replacing here?

I tried:

Code:

---

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.

you can not have 2 subs named switch, comment out the original or change name of one you are not using

Quote:

---

just replace your existing switch procedure

---

I get compile error on nexsht = 1

This is module 1:

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
Sub Switch()
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
If ActiveSheet.Index = Sheets.Count Then
nexsht = 1
Else: nexsht = ActiveSheet.Index + 1
End If
heets(nexsht).ActivateApplication.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

---

Quote:

---

I get compile error on nexsht = 1

---

any particular error or just some random error each time?

you should declare nexsht as integer

It just says compile error. How do I declare nexsht as integer? I just put that line somewhere? Sorry I'm a noob.

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
Sub Switch()
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
If ActiveSheet.Index = Sheets.Count Then
Dim nexsht As Integer
nexsht = 1
Else: nexsht = ActiveSheet.Index + 1
End If
Sheets(nexsht).ActivateApplication.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

---

When I try this, sheets get's highlighted and I get a runtime error 438

Quote:

---

Sheets(nexsht).ActivateApplication.OnTime Now + TimeValue("00:00:15"), "Switch"

---

i can see this line would cause error
you could look at the original code i posted, to see how it should be

I think you lost me. That code didn't work remember? What is this code you just posted?

try reading back all the posts till you find where you copied the above line from

Huh? But that code doesn't work. Still lost.

but did you find why it gave error?

I have no idea on any error. This is the first time I've even approached VB. I am completely new and nothing about it, which is why I cam to this forum. I really need someone to lay out the code so I can test it.

Quote:

---

I really need someone to lay out the code so I can test it.

---

but you have to copy correctly

Okay here is what I have now:

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
Sub Switch()
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
If ActiveSheet.Index = Sheets.Count Then
Dim nexsht as Integer
nexsht = 1
Else: nexsht = ActiveSheet.Index + 1
End If
Sheets(nexsht).Activate
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

---

works!!!!!