Welcome to the Excel Forum

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Excel Help Forum » Microsoft Office Application Help - Excel Help forum » Excel General » File wont accept VBA

Please Register to Remove these Ads

Please Register to Remove these Ads



Reply
  #1  
Old 07-02-2009, 09:38 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
File wont accept VBA

Please Register to Remove these Ads

Anybody know why this could be? My code is correct, I have confirmed this with several people. However, the code does nothing. Any theories?
Reply With Quote
  #2  
Old 07-02-2009, 09:51 AM
DonkeyOte's Avatar
DonkeyOte DonkeyOte is offline
Forum Guru
  
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute
Re: File wont accept VBA
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.
Reply With Quote
  #3  
Old 07-02-2009, 09:59 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
Re: File wont accept VBA
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
Reply With Quote
  #4  
Old 07-02-2009, 10:16 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
Re: File wont accept VBA
ive also tried deleting the code that was there and it doesnt help at all
Reply With Quote
  #5  
Old 07-02-2009, 10:23 AM
DonkeyOte's Avatar
DonkeyOte DonkeyOte is offline
Forum Guru
  
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute
Re: File wont accept VBA
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 ?
Reply With Quote
  #6  
Old 07-02-2009, 11:16 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
Re: File wont accept VBA
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.
Reply With Quote
  #7  
Old 07-02-2009, 11:21 AM
DonkeyOte's Avatar
DonkeyOte DonkeyOte is offline
Forum Guru
  
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute
Re: File wont accept VBA
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
Reply With Quote
  #8  
Old 07-02-2009, 11:25 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
Re: File wont accept VBA
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
Reply With Quote
  #9  
Old 07-02-2009, 11:27 AM
DonkeyOte's Avatar
DonkeyOte DonkeyOte is offline
Forum Guru
  
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,644
DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute DonkeyOte has a reputation beyond repute
Re: File wont accept VBA
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.
Reply With Quote
  #10  
Old 07-02-2009, 11:36 AM
stin25 stin25 is offline
Registered User
  
Join Date: 15 May 2009
Location: Springfield Missouri
MS Office Version:Excel 2003
Posts: 46
stin25 is becoming part of the community
Re: File wont accept VBA
Unfortunately I cannot post the file because it contains some relatively sensitive information.

Thank you for you help
Reply With Quote


Reply

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump

Contact Us - ExcelTip.com - Archive - Top