See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm
You would want to use the workbook_open event in the Thisworkbook Module
In the vbe, go to the project explorer and in the listing for your workbook,
double click on the thisworkbook entry.
this should open a module with two dropdown boxes at the top. In the left
dropdown, select workbook and in the right dropdown select Open.
this will put in the declaration
Private Sub Workbook_Open()
end Sub
You would put your code here
Private Sub Workbook_Open()
Dim sh as Worksheet
Dim ans as Variant
Dim rgLastCell as range
Thisworkbook.Worksheets(1).Activate
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
ans = InputBox("Please enter the desired Value")
If isnumeric(ans) then
rngLastCell.Offset(1,0).Value = cdbl(ans)
End If
End Sub
--
Regards,
Tom Ogilvy
"Hesham" wrote in message
...
tom,
your help is very very highly appreciated. things are alot easier now.
i have a little bit more challenging (i think) request. Say that i have a
table in a spreadsheet. i want as soon as i open the file, a message box
pops
up, asks me to input a value, which is then recorded after the last filled
cell in a column in that table.
if that is doable i would appreciate your help with it.
thanks
hesham
"Tom Ogilvy" wrote:
For a value
You asked for the value this time, so for a value:
Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub
for a formula
Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub
--
Regards,
Tom Ogilvy
"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different
sheet.
can you help me with that.
Thanks alot.
Hesham
"Tom Ogilvy" wrote:
Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub
--
Regards,
Tom Ogilvy
"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target
cell.
2) can we also do the same thing you did to the current cell to
the
current
worksheet?
thanks
"Tom Ogilvy" wrote:
Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub
--
Regards,
Tom Ogilvy
"Hesham" wrote in message
...
Tom,
thanks once again. your help is highly appreciated. just 1
last
request.
in the code you gave me i have to specify which cell to put
the
results
into. can we make the code such that it puts the address of
the
cell
that
is
selected in the spreadsheet automatically? rather than me
changing
the
code
everytime.
or....is it possible that excel asks me to which cell i want
to
put
the
results. popup msg or so.
thanks alot.
regards,
hesham
"Tom Ogilvy" wrote:
Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub
to Alt+F11 to go to the Visual Basic Editor. In the menu
there,
do
Insert=Module
Paste the code in the module that is inserted. Change the
code
to
reflect
the sheet, cell and column as appropriate
do Alt+F11 to get back to excel.
When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run
--
Regards,
Tom Ogilvy
"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code
into
the
sheet.
where
do i actually write it?
thanks
"Tom Ogilvy" wrote:
Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value
if the objective cell ig G9 and the "another" column is
column A
as
an
example.
--
Regards,
Tom Ogilvy
"Hesham" wrote in
message
...
Hi
how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled
cell
in
another
column.
thanks
"sebastienm" wrote:
Hi VJ,
Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Regards,
Sébastien
"VJ" wrote:
Hi,
Is there any way I can find out the last filled
cell
in a
column?
For exmaple I have col A and in that column first
five
cells
filled up
and
then next three cells are empty and then again
next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.
Help would be appreciated.
Thanks in advance.