File wont accept VBA

**stin25** · 07-02-2009, 10:38 AM

Anybody know why this could be? My code is correct, I have confirmed this with several people. However, the code does nothing. Any theories?

**DonkeyOte** · 07-02-2009, 10:51 AM

Are Macros Enabled ?

Check Tools -> Macro -> Security and ensure it is either set to Medium (my preferred) or Low ... if High Macros are disabled, Medium will prompt you to Enable when you open the file with options of Enable / Disable Macros.

Assuming Macros are enabled we would need to see the Code I think... pending nature of Code if Events are disabled the code may not fire.

**stin25** · 07-02-2009, 10:59 AM

this is my code

Code:

Private Sub workbook_open()
    Sheets(15).Range("BF3").Formula = "=AVERAGE(B3:BB3)"
    Sheets(15).Range("BF4").Formula = "=AVERAGE(B4:BB4)"
    Sheets(15).Range("BF5").Formula = "=AVERAGE(B5:BB5)"
    Sheets(15).Range("BF6").Formula = "=AVERAGE(B6:BB6)"
    Sheets(15).Range("BF7").Formula = "=AVERAGE(B7:BB7)"
End Sub

this is the code that was already in the file

Code:

'
' Upload Macro
' Macro designed 1/4/99 by Fawzy Simon
'
'

'   Declarations

Dim lgws(18) As String

Sub UpLoad()

'   Establish worksheet names array

    lgws(2) = "LG Volume Summary 1100"
    lgws(3) = "LG Volume Summary 1200"
    lgws(4) = "LG Volume Summary 1300"
    lgws(5) = "LG Volume Summary 1400"
    lgws(6) = "LG Volume Summary 1500"
    lgws(7) = "LG Volume Summary 1700"
    lgws(8) = "LG Volume Summary 1800"
    lgws(9) = "LG Volume Summary 1900"
    lgws(10) = "LG Volume Summary 2520"
    lgws(11) = "LG Volume Summary 2575"
    lgws(12) = "LG Volume Summary 2597"
    lgws(13) = "LG Volume Summary 2598"
    lgws(14) = "BU Volume Summary Cream Cheese"
    lgws(15) = "LG Volume Summary 16000"
    lgws(16) = "LG Volume Summary 17000"
    lgws(17) = "BU Volume Summary DiGiorno"
    lgws(18) = "Plt.Vol. Summary"
        
    inputpassword = InputBox("Enter Password For Update.")
    If inputpassword <> "green5" Then
        MsgBox ("I asked you not to. Come on now.")
        End
    End If
    inputweek = InputBox("Solve for which week number?") ' Determine current week
    
'   Error handling for current week determination
    
    If Not (IsNumeric(inputweek)) Then
        MsgBox ("You have not entered a numeric value...macro exiting.")
        End
    End If
    
    Application.ScreenUpdating = False ' Turn screen updating off
    week = inputweek + 6 ' Variable indicating working column for data insertion
    opencounter = 7 'Variable indicating working row for data extraction
    For lgcounter = 2 To 18 ' Master Counter
        Sheets(lgws(lgcounter)).Select ' Choose each Load Group worksheet to be updated from array
        
'       Upload Plan, QT and Actual data
        
        For counter = 1 To 3
            lg = counter + 7 ' Variable indicating working row for data insertion
            wsu = counter + 20 ' Variable indicating working row for data extraction
            transportvariable = Sheets("Weekend SAIM Upload").Cells(wsu, lgcounter) ' Extract P/Q/A
            Cells(lg, week).Value = transportvariable ' Insert P/Q/A
        Next counter
        
'       Upload Opening Inventory
        
        opencounter = opencounter + 13
        transportvariable = Sheets("Monday SAIM Upload").Cells(opencounter, 3)
        Cells(6, week).Value = transportvariable
        transportvariable = Sheets("Monday SAIM Upload").Cells(opencounter, 5)
        Cells(7, week).Value = transportvariable
        
'       Upload customer and intercompany forecast data
        
        For vcounter = 2 To 9
            For hcounter = (-4) To 5
                transportvariable = Sheets("Monday SAIM Upload").Cells((opencounter + vcounter), (6 + hcounter))
                Cells((9 + vcounter), (week + hcounter)).Value = transportvariable
            Next hcounter
        Next vcounter
        
'       Upload distress orders data

        For vcounter = 10 To 11
            For hcounter = (-4) To 5
                transportvariable = Sheets("Monday SAIM Upload").Cells((opencounter + vcounter), (6 + hcounter))
                Cells((10 + vcounter), (week + hcounter)).Value = transportvariable
            Next hcounter
        Next vcounter
    Next lgcounter
    Sheets("Plt.Vol. Summary").Select ' Return to front door
    Application.ScreenUpdating = True ' Turn screen updating back on
    Calculate ' Calculate values since calculation turned off to speed macro
End Sub

and

Code:

' Module to Print Charts
' by Fawzy Simon
' 3/30/99

' Declarations

Dim ChartPages(40) As String

**stin25** · 07-02-2009, 11:16 AM

ive also tried deleting the code that was there and it doesnt help at all

**DonkeyOte** · 07-02-2009, 11:23 AM

You didn't answer the question re: Macro Security.

Presumably the Workbook_Open event is placed within ThisWorkbook in VBEditor ? If it is, go to it, place your cursor on the first line and hit F8 - what happens ?

**stin25** · 07-02-2009, 12:16 PM

macro security is set to low.
when I press F8 it says there is an error but its with the code that was aready in the file

Code:

Sheets(lgws(lgcounter)).Select ' Choose each Load Group worksheet to be updated from array

.
I dont know enough about VBA to know why this is wrong.

**DonkeyOte** · 07-02-2009, 12:21 PM

I have to say I'm confused... you're saying if you step through the Workbook_Open event it generates an error in an entirely different routine ? I don't think that's actually happening.

Is your Workbook_Open event located in ThisWorkbook in VBE or in another Module ?

The only think that I can see that would cause your open routine to fail when being stepped through would be if you did not have 15 sheets in your file and/or sheet 15 was password protected (preventing your from inserting the formulae).

Without seeing the file it's going to be hard to help on this one... unless someone else has a bright idea

**stin25** · 07-02-2009, 12:25 PM

When I press F8 an error pops up, I click debug and it takes me to the code and highlights the part that i qutoed earler. The other thing is I have tried it using 5 different sheets in the file and none or them work

**DonkeyOte** · 07-02-2009, 12:27 PM

You're going to need to post your file - what you're saying is happening when stepping through the Open event does not make any sense (to me at least), sorry for not being more help... the error you're brining up belongs to an entirely different sub routine which is not affected by the open event... that said the error on the line you mention would imply you're missing some sheets.