Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > my script wont post, unless i hit a key..
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default my script wont post, unless i hit a key..
this script below.. works it just wont post on a worksheet by itself (
automatically every minute)..

unless I hit any key every minute.. then it will post every minute.

My question is what am I missing....

heres the script.. I set it to 15 seconds just for testing purposes..

Private RunWhen As Double
Private Const cRunIntervalSeconds = 15 ' two minutes
Private Const cRunWhat = "Worksheet_Change"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Today = Now
x = Sheets("sheet1").Range("a65536").End(xlUp).Row + 1
Sheets("sheet1").Range("a" & x) = Range("a1")

x = Sheets("sheet1").Range("b65536").End(xlUp).Row + 1
Sheets("sheet1").Range("b" & x) = Range("b2")

x = Sheets("sheet1").Range("c65536").End(xlUp).Row + 1
Sheets("sheet1").Range("c" & x) = Range("c2")

x = Sheets("sheet1").Range("d65536").End(xlUp).Row + 1
Sheets("sheet1").Range("d" & x) = Range("c4")

x = Sheets("sheet1").Range("e65536").End(xlUp).Row + 1
Sheets("sheet1").Range("e" & x) = Range("c5")

x = Sheets("sheet1").Range("f65536").End(xlUp).Row + 1
Sheets("sheet1").Range("f" & x) = Range("c6")

x = Sheets("sheet1").Range("g65536").End(xlUp).Row + 1
Sheets("sheet1").Range("g" & x) = Range("c7")

x = Sheets("sheet1").Range("h65536").End(xlUp).Row + 1
Sheets("sheet1").Range("h" & x) = Range("c8")

x = Sheets("sheet1").Range("i65536").End(xlUp).Row + 1
Sheets("sheet1").Range("i" & x) = Range("c9")

x = Sheets("sheet1").Range("j65536").End(xlUp).Row + 1
Sheets("sheet1").Range("j" & x) = Range("c10")

x = Sheets("sheet1").Range("k65536").End(xlUp).Row + 1
Sheets("sheet1").Range("k" & x) = Range("c11")

x = Sheets("sheet1").Range("l65536").End(xlUp).Row + 1
Sheets("sheet1").Range("l" & x) = Range("c12")

x = Sheets("sheet1").Range("m65536").End(xlUp).Row + 1
Sheets("sheet1").Range("m" & x) = Range("c13")

x = Sheets("sheet1").Range("n65536").End(xlUp).Row + 1
Sheets("sheet1").Range("n" & x) = Range("c14")

x = Sheets("sheet1").Range("o65536").End(xlUp).Row + 1
Sheets("sheet1").Range("o" & x) = Range("c15")

x = Sheets("sheet1").Range("p65536").End(xlUp).Row + 1
Sheets("sheet1").Range("p" & x) = Range("c16")

x = Sheets("sheet1").Range("q65536").End(xlUp).Row + 1
Sheets("sheet1").Range("q" & x) = Range("c17")

x = Sheets("sheet1").Range("r65536").End(xlUp).Row + 1
Sheets("sheet1").Range("r" & x) = Range("c18")

x = Sheets("sheet1").Range("s65536").End(xlUp).Row + 1
Sheets("sheet1").Range("s" & x) = Range("c19")

StartTimer

End Sub






Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub



THANKS!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default my script wont post, unless i hit a key..
OoOoO,

You're trying to run Worksheet_Change via the OnTime method. Normally, that
proc is triggered automatically by an event -- a change to the worksheet.
Do you want the posting to happen every minute OR when a key is pressed?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"0o0o0" wrote in message
...
this script below.. works it just wont post on a worksheet by itself (
automatically every minute)..

unless I hit any key every minute.. then it will post every minute.

My question is what am I missing....

heres the script.. I set it to 15 seconds just for testing purposes..

Private RunWhen As Double
Private Const cRunIntervalSeconds = 15 ' two minutes
Private Const cRunWhat = "Worksheet_Change"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Today = Now
x = Sheets("sheet1").Range("a65536").End(xlUp).Row + 1
Sheets("sheet1").Range("a" & x) = Range("a1")

x = Sheets("sheet1").Range("b65536").End(xlUp).Row + 1
Sheets("sheet1").Range("b" & x) = Range("b2")

x = Sheets("sheet1").Range("c65536").End(xlUp).Row + 1
Sheets("sheet1").Range("c" & x) = Range("c2")

x = Sheets("sheet1").Range("d65536").End(xlUp).Row + 1
Sheets("sheet1").Range("d" & x) = Range("c4")

x = Sheets("sheet1").Range("e65536").End(xlUp).Row + 1
Sheets("sheet1").Range("e" & x) = Range("c5")

x = Sheets("sheet1").Range("f65536").End(xlUp).Row + 1
Sheets("sheet1").Range("f" & x) = Range("c6")

x = Sheets("sheet1").Range("g65536").End(xlUp).Row + 1
Sheets("sheet1").Range("g" & x) = Range("c7")

x = Sheets("sheet1").Range("h65536").End(xlUp).Row + 1
Sheets("sheet1").Range("h" & x) = Range("c8")

x = Sheets("sheet1").Range("i65536").End(xlUp).Row + 1
Sheets("sheet1").Range("i" & x) = Range("c9")

x = Sheets("sheet1").Range("j65536").End(xlUp).Row + 1
Sheets("sheet1").Range("j" & x) = Range("c10")

x = Sheets("sheet1").Range("k65536").End(xlUp).Row + 1
Sheets("sheet1").Range("k" & x) = Range("c11")

x = Sheets("sheet1").Range("l65536").End(xlUp).Row + 1
Sheets("sheet1").Range("l" & x) = Range("c12")

x = Sheets("sheet1").Range("m65536").End(xlUp).Row + 1
Sheets("sheet1").Range("m" & x) = Range("c13")

x = Sheets("sheet1").Range("n65536").End(xlUp).Row + 1
Sheets("sheet1").Range("n" & x) = Range("c14")

x = Sheets("sheet1").Range("o65536").End(xlUp).Row + 1
Sheets("sheet1").Range("o" & x) = Range("c15")

x = Sheets("sheet1").Range("p65536").End(xlUp).Row + 1
Sheets("sheet1").Range("p" & x) = Range("c16")

x = Sheets("sheet1").Range("q65536").End(xlUp).Row + 1
Sheets("sheet1").Range("q" & x) = Range("c17")

x = Sheets("sheet1").Range("r65536").End(xlUp).Row + 1
Sheets("sheet1").Range("r" & x) = Range("c18")

x = Sheets("sheet1").Range("s65536").End(xlUp).Row + 1
Sheets("sheet1").Range("s" & x) = Range("c19")

StartTimer

End Sub






Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub



THANKS!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 493
Default my script wont post, unless i hit a key..
You can't call an event macro using OnTime - event macros are run
when their events fire (e.g., for Worksheet_Change(), when an entry
is made in the sheet).

Suggestion:

In a *regular* code module (Insert/Module):

Option Explicit
Public gdRunWhen As Double
Public Const cRunIntervalSeconds = 60 'one minute
Public Const cRunWhat = "UpdateSheet"

Public Sub StartTimer()
gdRunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime _
EarliestTime:=gdRunWhen, _
Procedu=cRunWhat, _
Schedule:=True
End Sub

Public Sub StopTimer()
On Error Resume Next
Application.OnTime _
EarliestTime:=gdRunWhen, _
Procedu=cRunWhat, _
Schedule:=False
End Sub

Substitute the name of your source sheet for "Sheet2" in this macro:

Public Sub UpdateSheet()
Dim oSourceSht As Worksheet
Dim i As Integer
Set oSourceSht = Sheets("Sheet2") 'change to suit
With Sheets("Sheet1")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Range("A1").Value
.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Range("B2").Value
For i = 3 To 19
.Cells(Rows.Count, i).End(xlUp).Offset(1, 0).Value = _
oSourceSht.Cells(i - 1, 3).Value
Next i
End With
StartTimer
End Sub

If all the columns have the same last row, this is a bit more
efficient:

Public Sub UpdateSheet()
Dim oSourceSht As Worksheet
Dim nLastRow As Long
Dim i As Integer
Set oSourceSht = Sheets("Sheet2") 'change to suit
With Sheets("Sheet1")
nLastRow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(nLastRow, 1).Value = oSourceSht.Range("A1").Value
.Cells(nLastRow, 2).Value = oSourceSht.Range("B2").Value
For i = 3 To 19
.Cells(nLastRow, i).Value = _
oSourceSht.Cells(i - 1, 3).Value
Next i
End With
StartTimer
End Sub


In article ,
0o0o0 wrote:

this script below.. works it just wont post on a worksheet by itself (
automatically every minute)..

unless I hit any key every minute.. then it will post every minute.

My question is what am I missing....

heres the script.. I set it to 15 seconds just for testing purposes..

Private RunWhen As Double
Private Const cRunIntervalSeconds = 15 ' two minutes
Private Const cRunWhat = "Worksheet_Change"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Today = Now
x = Sheets("sheet1").Range("a65536").End(xlUp).Row + 1
Sheets("sheet1").Range("a" & x) = Range("a1")

x = Sheets("sheet1").Range("b65536").End(xlUp).Row + 1
Sheets("sheet1").Range("b" & x) = Range("b2")

x = Sheets("sheet1").Range("c65536").End(xlUp).Row + 1
Sheets("sheet1").Range("c" & x) = Range("c2")

x = Sheets("sheet1").Range("d65536").End(xlUp).Row + 1
Sheets("sheet1").Range("d" & x) = Range("c4")

x = Sheets("sheet1").Range("e65536").End(xlUp).Row + 1
Sheets("sheet1").Range("e" & x) = Range("c5")

x = Sheets("sheet1").Range("f65536").End(xlUp).Row + 1
Sheets("sheet1").Range("f" & x) = Range("c6")

x = Sheets("sheet1").Range("g65536").End(xlUp).Row + 1
Sheets("sheet1").Range("g" & x) = Range("c7")

x = Sheets("sheet1").Range("h65536").End(xlUp).Row + 1
Sheets("sheet1").Range("h" & x) = Range("c8")

x = Sheets("sheet1").Range("i65536").End(xlUp).Row + 1
Sheets("sheet1").Range("i" & x) = Range("c9")

x = Sheets("sheet1").Range("j65536").End(xlUp).Row + 1
Sheets("sheet1").Range("j" & x) = Range("c10")

x = Sheets("sheet1").Range("k65536").End(xlUp).Row + 1
Sheets("sheet1").Range("k" & x) = Range("c11")

x = Sheets("sheet1").Range("l65536").End(xlUp).Row + 1
Sheets("sheet1").Range("l" & x) = Range("c12")

x = Sheets("sheet1").Range("m65536").End(xlUp).Row + 1
Sheets("sheet1").Range("m" & x) = Range("c13")

x = Sheets("sheet1").Range("n65536").End(xlUp).Row + 1
Sheets("sheet1").Range("n" & x) = Range("c14")

x = Sheets("sheet1").Range("o65536").End(xlUp).Row + 1
Sheets("sheet1").Range("o" & x) = Range("c15")

x = Sheets("sheet1").Range("p65536").End(xlUp).Row + 1
Sheets("sheet1").Range("p" & x) = Range("c16")

x = Sheets("sheet1").Range("q65536").End(xlUp).Row + 1
Sheets("sheet1").Range("q" & x) = Range("c17")

x = Sheets("sheet1").Range("r65536").End(xlUp).Row + 1
Sheets("sheet1").Range("r" & x) = Range("c18")

x = Sheets("sheet1").Range("s65536").End(xlUp).Row + 1
Sheets("sheet1").Range("s" & x) = Range("c19")

StartTimer

End Sub






Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub



THANKS!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default my script wont post, unless i hit a key..
THANK YOU!!!

Ive been trying to get this to work for lol 3 years!...

but just started trying VB out 3 weeks ago, read everything asked
questions everywhere.. and you did it.. THANKS!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
wont save sussertown[_2_] Excel Discussion (Misc queries) 2 January 26th 09 07:46 PM
Wont Sum Phil Excel Worksheet Functions 3 May 18th 07 07:07 PM
Date format from excel to CVS file wont. Change in CVS wont stay. Fish''s Mermaid Excel Worksheet Functions 1 October 14th 06 12:28 AM
Should I generally request "post a poll" when I post a new thread? Joe Miller Excel Discussion (Misc queries) 2 January 7th 06 04:46 PM
Excel is there but it wont pop up amelia Excel Discussion (Misc queries) 1 July 19th 05 12:13 PM


All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement