auto_open vba

pullmyefinger

Board Regular
Joined
Mar 9, 2011
Messages
69
i have a worksheet that need certain tasks performed when the worksheet is opened.

i read and found the workbook.open sub, but it doesn't seem to work like i hoped it would. I need to:

open a worksheet and have the workbook.open sub do things like


activate the worksheet called "this month"
[Calc] i think this works

check the value in a cell using an if statement

if the condition is true, i need to copy/paste special a value in one cell to
another

in a nested if below the one above, i need to check the value of another cell and copy/paste special from one cell to another.

--------------------------------------------
It seems like even though Both IF statments are True, it Ignores the code therein and all I get is the msgbox after the nested IFs.


Do I need to do anything else that microsoft isn't telling me?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

capparfrank

New Member
Joined
Feb 11, 2011
Messages
41
Please post your code so we can look at it and try to figure out why it's not behaving as expected.
 

chuckchuckit

Well-known Member
Joined
Sep 18, 2010
Messages
541
Welcome to the board!

This should work:
Code:
Sub Auto_Open ()
 
     'Put any code here you want to run automatically as soon as workbook is opened.
 
End Sub
The above "Sub Auto_Open ()" will automatically run by itself as soon as you open your workbook. Provided you have the above code somewhere in one of the VBA modules.
 

pullmyefinger

Board Regular
Joined
Mar 9, 2011
Messages
69
Please post your code so we can look at it and try to figure out why it's not behaving as expected.

Thank you. Code below, .xls file is stored in My documents subdirectory.

Cell value in T11 IS IS IS 1 (out to 6 decimal places) and U3 is empty so its value is 0. I must be having a brain fart cuz I have been doing this since the Lotus 1-2-3 days when XL didn't exist.

the last msgbox command b4 the end sub is the only thing i see on the screen. T3, U3 are both empty after this sub executes and they shouldn't be when I manually change the date and CALC to test it.



Private Sub Workbook_Open()
' Don't use Auto Open, it runs before it loads this worksheet.
' Macro recorded March 4, 2011. This fixes the payday SNAFU.

' Declare Variables

Dim x As String

Workbooks("B.xls").Worksheets("This Month").Activate

Worksheets("This Month").Calculate


If T11 = 1 Then ' 1 day till payday, move appropriate value
x = MsgBox("T11=1 only", vbOKOnly, "Inside")
If U3 = 0 Then ' 1 = static pay moved, 0 = static pay not moved

x = MsgBox("T11=1, U3=0", vbOKOnly, "Inside")
Range("U3").Select
ActiveCell.FormulaR1C1 = 1
Range("T4").Select 'Paste Next Paydate to T3 as Text
Selection.Copy

Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else

x = MsgBox("T11=1, ELSE Part of IF", vbOKOnly, "Else")
Range("U3").Select
ActiveCell.FormulaR1C1 = 0

End If

End If

' ActiveWorkbook.Save

x = MsgBox("T11=1, U3=0", vbOKOnly, "OUTSIDE")

End Sub
 

pullmyefinger

Board Regular
Joined
Mar 9, 2011
Messages
69

ADVERTISEMENT

Thank you. Code below, .xls file is stored in My documents subdirectory.

Cell value in T11 IS IS IS 1 (out to 6 decimal places) and U3 is empty so its value is 0. I must be having a brain fart cuz I have been doing this since the Lotus 1-2-3 days when XL didn't exist.

the last msgbox command b4 the end sub is the only thing i see on the screen. T3, U3 are both empty after this sub executes and they shouldn't be when I manually change the date and CALC to test it.



Private Sub Workbook_Open()
' Don't use Auto Open, it runs before it loads this worksheet.
' Macro recorded March 4, 2011. This fixes the payday SNAFU.

' Declare Variables

Dim x As String

Workbooks("B.xls").Worksheets("This Month").Activate

Worksheets("This Month").Calculate


If T11 = 1 Then ' 1 day till payday, move appropriate value
x = MsgBox("T11=1 only", vbOKOnly, "Inside")
If U3 = 0 Then ' 1 = static pay moved, 0 = static pay not moved

x = MsgBox("T11=1, U3=0", vbOKOnly, "Inside")
Range("U3").Select
ActiveCell.FormulaR1C1 = 1
Range("T4").Select 'Paste Next Paydate to T3 as Text
Selection.Copy

Range("T3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else

x = MsgBox("T11=1, ELSE Part of IF", vbOKOnly, "Else")
Range("U3").Select
ActiveCell.FormulaR1C1 = 0

End If

End If

' ActiveWorkbook.Save

x = MsgBox("T11=1, U3=0", vbOKOnly, "OUTSIDE")

End Sub
ok. i did find one mistake and that was that the complete path was not in the .ACTIVATE statement so it wasn't loading the worksheet???

The problem is as follows and is the reason I want to use the workbook open():

There is a date in T4 that represents next payday. T11 contains the number of days until next payday.

When T11=1 then "payday is tomorrow", but it is not that simple. When T11=0 and it is the next month, CALC rears its ugly head and figures out when next payday should be. I figured that if I could copy the current payday in T4 to T3 as a value and not a formula, that would preserve the current payday regardless of what Calc does next month. The reason is that the Nested IFs have to handle the current payday on a weekday or a weekend; If payday is on the 31st, then the formula should say payday is today AND when ??/1/???? comes the next day it should not say it's payday (is should say there are X days until next payday because payday came early).

Capiche? I need to circumvent the CALC manually and have CALC work for everything else. That is why I want to manually paste values from T4 to T3.
I also put a "flag" in cell U3 to tell me that I did move the current payday and it is not necessary to do it every time I open the worksheet.

Cell U3 =0 means the date was not moved, U3=1 means the date was moved (copy/paste spec/values only).

I can send the worksheet if need be, but i need to know how to attach it and it must be drastically gutted due to there being financial info in it.
 

capparfrank

New Member
Joined
Feb 11, 2011
Messages
41
Your IF statments are referencing cells like excel formulas, not like VBA.

Code:
If T11 = 1 Then ...

must be

Code:
if Range("T11").Value = 1 Then...

or

Code:
if Cells(11,20).Value Then ...
 

pullmyefinger

Board Regular
Joined
Mar 9, 2011
Messages
69
Thank you Frank. Glad it was something simple. Yeah, been writing ifs for so long just never thought twice about the VB version.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,427
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top