Ghetto script - need help! one last thing
 

to explain it better then trying to explain it, do this below..

here... this is probably SO not right ... but hey it works..

tell me what i could use to not have to hit space bar to update sheet 1
everytime (original is web queried but whats below will give you and
idea of the problem im having).

open a new workbook..
fill sheet2 with numbers all over it (up to F20 squared).

put this in the vba or mvb. ( its at bottom of post) but read on

and go back to sheet 2 find any blank area click it and press spacebar.


can I eliminate pressing the spacebar??

I need an auto update every 1 min.


pressing the space bar everyminute...its ghetto like. ( but so is my
script i guess lol hey it works... almost)

heres my ghetto script, remember I am 3 days new at vba or mvb.

---------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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")

End Sub


thanks!!! I promise I wont clutter the message boards up with questions
after this.



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

Ghetto script - need help! one last thing
 

nice I left out...
look at sheet 1 and your number should be there.. after pressing the
space bar in sheet 2
thats all right, but pressing the space bar very minute isnt fun.

need automation, every 1 min.

thanks again



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

Ghetto script - need help! one last thing
 

does it have to do with this line??

Private Sub Worksheet_change(ByVal Target As Range)

"change"??

I changed it to Worksheet_selection change

and it would only update with my mouse?? moving the mouse to another
cell (keyboard didnt work then). looked back at sheet 1 and the entries
were there.

so i changed it back to Worksheet_change

now its back to keyboard again..?

need auto update/ hands free tho.

thanks.



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

Ghetto script - need help! one last thing
 

Look at Chip Pearson's site for information on Application.OnTime

http://www.cpearson.com/excel/ontime.htm

--
Regards,
Tom Ogilvy

0o0o0 wrote in message
...
does it have to do with this line??

Private Sub Worksheet_change(ByVal Target As Range)

"change"??

I changed it to Worksheet_selection change

and it would only update with my mouse?? moving the mouse to another
cell (keyboard didnt work then). looked back at sheet 1 and the entries
were there.

so i changed it back to Worksheet_change

now its back to keyboard again..?

need auto update/ hands free tho.

thanks.



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

Ghetto script - need help! one last thing
 

but im gettin... constants fixed length strings, arrays user defined
types and declare statements not allowed as public members of object
modules.

thats for the USING WINDOWS TIMERS




the ontime technique the site gives me..

just keeps coming up cant use constants error.

any other suggestions .. im up for anything!


thanks



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

Ghetto script - need help! one last thing
 

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
TimerSeconds = 15 ' how often to "pop" the timer.
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf
TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'
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



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

Ghetto script - need help! one last thing
 

Putting
Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long


in a sheet module gives you the same error - Not sure why you can figure
this out and can't figure out how to use ontime.

--
Regards,
Tom Ogilvy

"0o0o0" wrote in message
...
Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
TimerSeconds = 15 ' how often to "pop" the timer.
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf
TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
'
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



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

Ghetto script - need help! one last thing
 

If you are putting the declaration in a class module (or a sheet module, the
ThisWorkbook module, or a Userform module), change the declarations from
Public Function to Private Function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"0o0o0" wrote in message
...
but im gettin... constants fixed length strings, arrays user defined
types and declare statements not allowed as public members of object
modules.

thats for the USING WINDOWS TIMERS




the ontime technique the site gives me..

just keeps coming up cant use constants error.

any other suggestions .. im up for anything!


thanks



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

Ghetto script - need help! one last thing
 

I changed them to private lastnight and NO MORE ERRORS!!! its runs...

but nothing works lol.

I still have to hit the space bar ( or any key) in sheet two.. to make
the data pop up on sheet 1.

any ideas there. Im new with vb but I know html.. I searched for
anything similiar to POST in vb but not the same.

Maybe I could make this script even more ghetto and make a automatic
"spacebar" click every minute?? hey if its handfree and does what i
want I dont mind if this is even possible.

Im so sorry guys, but thank for the patience considering I am like 5%
vb coherient now.

- the script is now private
- it runs
- no errors
- but still doesnt make data pop up on sheet1
- still requires pressing spacebar to work, which isnt handsfree

I would have posted this last night although the board here was down.

thanks again!



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

Ghetto script - need help! one last thing
 

I run my script alone the original, press play and it still never posted
anything on sheet 1 ..... only when I pressed a key.



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

Ghetto script - need help! one last thing
 

I used both examples from your site..

I ended up troubleshooting (3hours lol) and went back to your original
ontime setup FROM YOUR SITE!.. (that what im realizing)

here it is it works.. but pressing a key only makes it come up on
sheet1

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



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

Ghetto script - need help! one last thing
 

to the last 3 messages?



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