VBA

[m.addy]

I am a new user to VBA and have been writing custom
macro's for Excel. Most of the coding seems fine and easy
with regards to objects, properties and methods but
there's one that just doesn't work........well I can't get
it to work though everyone say's it should. It concerns
the Auto_Open feature of VBA. I have used this build in
funcion to automatically run a macro on startup of the
workbook (see example below)

Auto_Open()
Msgbox "Hello"
End Sub

when saving this code within a module contained in that
workbook it does not seem to have the desired effect as it
should.......does anyone have any ideas ?

Secondly, I want a macro to run contuously whilst i'm in a
workbook which could constantly keep a track of what cells
i'm clicking into and note their addresses and perform
some other action as a result. I know how to construct do,
do until and do while loops but, cannot get the update of
cell addressing.

Any help on either of these would be most grateful...thanx

 

[Harald Staff]

Hi

Auto_open won't run when a macro opens the workbook. Would that be the case ? Also, it's supposed to be named
Sub Auto_Open()
you should receive an error of some sort if your code looks like the one provided.

For tracking cells; paste this in the ThisWorkbook module:

Private Sub Workbook_SheetSelectionChange(ByVal _
Sh As Object, ByVal Target As Range)
MsgBox Sh.Name & "!" & Target.Address
End Sub

 

[John Wilson]

m.addy,

That should be
Sub Auto_Open()
and it should be in a regular module (not a sheet or the workbook
module).
It'll run once whenever the workbook is opened.

Secondly, I want a macro to run contuously whilst i'm in a
workbook which could constantly keep a track of what cells
i'm clicking into and note their addresses and perform
some other action as a result.

A continuously running macro will not work for this.
There are events that you could trap to test what cells
have been selected or changed. Post back with more info
and we'll try to steer you in the right direction.

John