Ghetto script - need help! one last thing

0

0o0o0

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.
 
0

0o0o0

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
 
0

0o0o0

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.
 
0

0o0o0

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
 
0

0o0o0

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
 
T

Tom Ogilvy

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.
 
C

Chip Pearson

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 (e-mail address removed)
 
0

0o0o0

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!
 
0

0o0o0

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

0o0o0

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, procedure:=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, _
procedure:=cRunWhat, schedule:=False
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transfer array element offset value to a matched value 3
Excel VBA help: Text file formatting 19
Compating all rows in two sheets 2
my script wont post, unless i hit a key.. 3
copy entire row and paste values only to another sheet 12
Similar Book to Book Code, one works other does not 11
What is wrong with this code?!? 1
help with syntax for copy and pasting rows across sheets 4

Top