Link to home
Create AccountLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Call a sub on WS Activation

Hi,

I have two Private Subs which i want to call on workbook opening.

The WS i called "Check" and is going to be very hidden.

The subs are below.

What is the best code to use to call these subs on WB opening?

Thanks
Seamus
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Use the Workbook-Open event (in the ThisWorkbook module)?
Avatar of ragnarok89
ragnarok89

The first sub in your workbook must be called   Auto_open()

Auto_open() will have 2 statements:
call x
call y

where x and y are the names of your private subs.

Al
Avatar of Seamus2626

ASKER

Ive tried Stephen, but it calls the subs undefined?

Cheers,
Seamus
Seamus - can you post a sample workbook?
Avatar of Rory Archibald
Why are they Private if you want to call them from another module? Make them Public instead.
Hi.

I put this in the workbook opening


Public Sub Workbook_Open()
Call Workbook_BeforeClose
Workbook_Open
End Sub

I called the subs Public and it gives me the message when the file opens

"Argument Not Optional"

Thanks
Seamus
SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Im not trying to close it Stephen, when this work book opens i want it to apply the below code.

It basically shows what time the user enters and after they close what time they left.

Thanks
Seamus
Public Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("check")
    .Range("B64000").End(xlUp).Offset(0, 2) = Now()
End With
ActiveWorkbook.Save
End Sub

Public Sub Workbook_Open()
With Sheets("check")
   .Range("B64000").End(xlUp).Offset(1, 0) = Environ("USERNAME")
   .Range("B64000").End(xlUp).Offset(0, 1) = Now()
End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You might have some of the same code in these two subs.  No problem, since they are both simple.  If more is involved, you could make some more public subs and call several of them (different selections) from the WorkbookOpen and WorkbookClose subs.
Thanks all

Got it too work
Cheers,
Seamus