auto_open vba

[pullmyefinger]

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?

 

[capparfrank]

Please post your code so we can look at it and try to figure out why it's not behaving as expected.

 

[chuckchuckit]

Welcome to the board!

This should work:

Sub Auto_Open ()
 
     'Put any code here you want to run automatically as soon as workbook is opened.
 
End Sub

The above "Sub Autpen ()" 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]

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]

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]

Your IF statments are referencing cells like excel formulas, not like VBA.

If T11 = 1 Then ...

must be

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

or

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

 

[pullmyefinger]

Thank you Frank. Glad it was something simple. Yeah, been writing ifs for so long just never thought twice about the VB version.