Solved

Excel VB code isn't working

Posted on 2015-02-03
28
165 Views
Last Modified: 2015-02-09
I have a spreadsheet that I have done some VB code in it.  Over the past couple of weeks the codes have stopped working.  It was suggested that I look for *.exd files delete them and that would resolve my problem.  It did the first time but now I keep getting these two files MSForms.exd and RefEdit.exd that pop up each time I open my database.  I delete them multiple times and ever time I open the database these keep occurring.  Once I delete them and then open the database and I get a runtime error of 32809, application-defined or object defined error.    I had never had this issue before.  When I debug it I see that I had put some code in "ThisWORKBOOK" that basically called up the Home page as I wanted users who open the workbook to get the home page which has some button navigation on it for them.  The code says that   Sheets("Home").Activate  is where the issue resides, but this has worked for the last 5 months without issue.  I have checked to make sure that  under Macro security Enable all macros is selected but none of my code works now.  But none of my macros work now.    I tried uploading the file but it tells me that a virus was detected in the uploaded file.  How do I scan the file and fix or remove it.  I am on a locked odwon PC and have win7 and office 2010.  Can anyone help me here?
0
Comment
Question by:Rrave26
  • 17
  • 5
  • 4
  • +1
28 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40586591
How does the code not work?

Are you getting errors or is just not working?
0
 

Author Comment

by:Rrave26
ID: 40586602
I get a runtime error of 32809, application-defined or object defined error and then most of the code simply isn't working.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40586611
now I keep getting these two files MSForms.exd and RefEdit.exd that pop up each time I open my database
What do you mean when you say they pop up?

Can you attach your workbook?
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40586613
The .exd issue refers to a windows update released at the end of last year which screwed up a load of active-x libraries.

Here's a link. See if it solves your problem.

http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled


If not you're going to have to (at least) post your code, and preferably, upload your entire workbook.

Ta
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40586619
When you say
I tried uploading the file but it tells me that a virus was detected in the uploaded file.
 
do you mean you tried uploading it to Experts-Exchange?
0
 

Author Comment

by:Rrave26
ID: 40586626
When I open the database I notice my VB code doesn't work.  In a previous question I asked the same question and I as told to look for *.exd files, delete them and then the code should work.  So  have been doing that and I have noticed that MSForms.exd and RefEdit.exd show up when I search my C: for them.  I delete them, go back into the database and then get the run time error and the VB code doesn't work.  

I have tried to attach my spreadsheet but when I try to upload it, I am told that it has a virus and wont let me upload it.  How do I check my file for virus's?
0
 
LVL 33

Expert Comment

by:Norie
ID: 40586628
How is the workbook being opened?
0
 

Author Comment

by:Rrave26
ID: 40586636
Yes I can't upload it to experts exchange.
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40586643
@Rrave26 - are you using the words 'spreadsheet' and 'database' interchangeably, or does this issue in fact affect both a spreadsheet, and a database?
0
 

Author Comment

by:Rrave26
ID: 40586647
It is an excel spreadsheet James.  My apologies.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40586664
Do you have anti-virus software? If so which one? When you were told that you had a virus, did you try to remove it?
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40586672
Open your spreadsheet. Hit Alt+F11 to open the VB Editor.

Click Tools=>References

Are there any words like 'Missing' or 'Error' or 'Not Found' next to any of the items that are ticked?
0
 

Author Comment

by:Rrave26
ID: 40586675
I have McAffee 8.8  I just noticed that there was an issue when I tried to upload the file to experts exchange.  I am trying to run the scan now.
0
 

Author Comment

by:Rrave26
ID: 40586693
@ James, I have looked and did not find any missing, errors or not found entries next to any of the selected options.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Expert Comment

by:James Elliott
ID: 40586703
Can you copy/paste the code as an alternative to uploading the file?

IE. Open VB Editor (ALT+F11) => Double click all objects on the left, in turn, and copy/paste all code on the right into this thread.
0
 

Author Comment

by:Rrave26
ID: 40586725
Modules

Mod 1
Sub WeeklyTTIR(StDate As Date, EndDate As Date)
Dim WS As Worksheet
Dim WSRaw As Worksheet
Const ColOddRow = 12040422
Dim MaxRow As Long, MaxCol As Long, I As Long, J As Long, LCount As Long, FirstRow As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> initialise Variables
Set WS = ActiveSheet
Set WSRaw = Sheets("IM Raw Data")
MaxRow = WSRaw.Range("A" & WSRaw.Rows.Count).End(xlUp).Row
MaxCol = WSRaw.Columns(WSRaw.Columns.Count).End(xlToLeft).Column
J = 4

'---> Clean Present Report
WS.Range("4:" & WS.Rows.Count).EntireRow.Delete

'---> Filter Data in IM raw Data for the selected period
If WSRaw.FilterMode = True Then WSRaw.ShowAllData
WSRaw.UsedRange.AutoFilter Field:=WSRaw.Columns("I").Column, Criteria1:="=HBCA", _
        Operator:=xlOr, Criteria2:="=HBUS"
WSRaw.UsedRange.AutoFilter Field:=WSRaw.Columns("K").Column, Criteria1:=">=" & StDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

'---> Start Process
For I = 2 To MaxRow
    If WSRaw.Range("A" & I).EntireRow.Hidden = False Then
        If FirstRow = 0 Then FirstRow = J
        '---> Affect Data to Cells
        WS.Range("A" & J) = J - 3
        WS.Range("B" & J) = WSRaw.Cells(I, "K")
        WS.Range("C" & J) = WSRaw.Cells(I, "B")
        WS.Range("D" & J) = WSRaw.Cells(I, "C")
        If WSRaw.Cells(I, "AR") <> "" Then WS.Range("E" & J) = TimeValue(WSRaw.Cells(I, "AR"))
        If WSRaw.Cells(I, "AU") <> "" Then WS.Range("F" & J) = TimeValue(WSRaw.Cells(I, "AU"))
        WS.Range("G" & J).Formula = "=F" & J & "-E" & J
        WS.Range("H" & J) = WSRaw.Cells(I, "G")
   
        '---> Format the Row
        If J Mod 2 <> 0 Then WS.Range("A" & J & ":H" & J).Interior.Color = ColOddRow
        WS.Range("A" & J & ":H" & J).HorizontalAlignment = xlCenter
        WS.Range("A" & J & ":H" & J).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        WS.Range("A" & J & ":H" & J).Cells.Borders.LineStyle = xlContinuous
        WS.Range("B" & J).NumberFormat = "dd-mmm-yyyy"
        WS.Range("E" & J & ":F" & J).NumberFormat = "hh:mm"
        WS.Range("G" & J).NumberFormat = "hh:mm:ss"
       
        '---> Increment Counters
        J = J + 1
        LCount = LCount + 1
       
    End If

Next I

If FirstRow <> 0 Then
   
    '---> Make final Formating and Display Totals
    WS.Range("A" & FirstRow & ":H" & J - 1).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
    WS.Range("F" & J + 1) = "Average TTIR"
    WS.Range("F" & J + 2) = "Percent sent on time"
   
    '---> formulas
    '=TEXT(AVERAGE(G4:G14),"h:mm:ss")
    WS.Range("G" & J + 1).Formula = "=AVERAGE(G" & FirstRow & ":G" & J - 1 & ")"
    WS.Range("G" & J + 1).NumberFormat = "hh:mm:ss"
   
    '=TEXT(COUNTIF(G4:G14,"<=0:15:00")/COUNT(G4:G14),"0.0%")
    WS.Range("G" & J + 2).Formula = "=COUNTIF(G" & FirstRow & ":G" & J - 1 & "," & Chr(34) & "<=0:15:00" & Chr(34) & ")/COUNT(G" & FirstRow & ":G" & J - 1 & ")"
    WS.Range("G" & J + 2).NumberFormat = "0.0%"
   
    '---> Format Totals
    WS.Range("F" & J + 1 & ":G" & J + 2).Font.Bold = True
    WS.Range("F" & J + 1 & ":F" & J + 2).HorizontalAlignment = xlLeft
    WS.Range("G" & J + 1 & ":G" & J + 2).HorizontalAlignment = xlCenter
   
    '---> Change Title
    WS.Shapes("TextBox4").OLEFormat.Object.Text = "TTIR Weekly Report Ending " & Format(EndDate, "dd-mmm-yyyy") & " for Incident Management "
End If

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'---> Advise User
If FirstRow <> 0 Then
    MsgBox ("Weekly Report for period ending " & EndDate & " gerated " & LCount & " records successfully.")
Else
    MsgBox ("No Data was found in this interval.")
End If

'---> Unfilter Data
If WSRaw.FilterMode = True Then WSRaw.ShowAllData


End Sub
0
 

Author Comment

by:Rrave26
ID: 40586730
mod 2
Sub DQRReport(StDate As Date, EndDate As Date)
Dim WS As Worksheet
Dim WSRaw As Worksheet
Const ColOddRow = 12040422
Dim MaxRow As Long, MaxCol As Long, I As Long, J As Long, LCount As Long, FirstRow As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> initialise Variables
Set WS = ActiveSheet
Set WSRaw = Sheets("IM Raw Data")
MaxRow = WSRaw.Range("A" & WSRaw.Rows.Count).End(xlUp).Row
MaxCol = WSRaw.Columns(WSRaw.Columns.Count).End(xlToLeft).Column
J = 4

'---> Clean Present Report
WS.Range("4:" & WS.Rows.Count).EntireRow.Delete

'---> Filter Data in IM raw Data for the selected period
If WSRaw.FilterMode = True Then WSRaw.ShowAllData
WSRaw.UsedRange.AutoFilter Field:=WSRaw.Columns("J").Column, Criteria1:=">=" & StDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

'---> Start Process
For I = 2 To MaxRow
    If WSRaw.Range("A" & I).EntireRow.Hidden = False Then
        If FirstRow = 0 Then FirstRow = J
        '---> Affect Data to Cells
        WS.Range("A" & J) = J - 3
        WS.Range("B" & J) = WSRaw.Cells(I, "J")
        WS.Range("C" & J) = WSRaw.Cells(I, "B")
        WS.Range("D" & J) = WSRaw.Cells(I, "C")
        WS.Range("E" & J) = WSRaw.Cells(I, "D")
        WS.Range("F" & J) = WSRaw.Cells(I, "AE")
       
        '---> Format the Row
        If J Mod 2 <> 0 Then WS.Range("A" & J & ":F" & J).Interior.Color = ColOddRow
        WS.Range("A" & J & ":F" & J).HorizontalAlignment = xlCenter
        WS.Range("A" & J & ":F" & J).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThin
        WS.Range("A" & J & ":F" & J).Cells.Borders.LineStyle = xlContinuous
        WS.Range("B" & J).NumberFormat = "dd-mmm-yyyy"
        WS.Range("E" & J & ":F" & J).NumberFormat = "hh:mm"
        WS.Range("F" & J).WrapText = True
       
        '---> Increment Counters
        J = J + 1
        LCount = LCount + 1
       
    End If

Next I

If FirstRow <> 0 Then
   
    '---> Make final Formating and Display Totals
    WS.Range("A" & FirstRow & ":F" & J - 1).Cells.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
     
       
    '---> Change Title
    WS.Shapes("TextBox4").OLEFormat.Object.Text = "DQR Follow Up Report Ending " & Format(EndDate, "dd-mmm-yyyy") & " for Incident Management "
End If

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

'---> Advise User
If FirstRow <> 0 Then
    MsgBox ("Weekly Report for period ending " & EndDate & " gerated " & LCount & " records successfully.")
Else
    MsgBox ("No Data was found in this interval.")
End If

'---> Unfilter Data
If WSRaw.FilterMode = True Then WSRaw.ShowAllData


End Sub
0
 

Author Comment

by:Rrave26
ID: 40586739
Sheet 10 weekly TTIR
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("m/d/yy;@", "mmmm d yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Top = 100
                CalendarFrm.Left = 1000
                CalendarFrm.Height = 190 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 190
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

Private Sub CommandButton1_Click()
If Range("J2") <> "" And Range("K2") <> "" Then
    If Range("J2") > Range("K2") Or Range("K2") < Range("J2") And Range("J2") <> "" And Range("K2") <> "" Then
        MsgBox ("Wrong Dates Sequence Selected From should be Smaller than To and Vice Versa. Check Dates and Try again")
    Else
        If MsgBox("Generate Weekly Report From: " & Range("J2") & " to " & Range("K2") & " ? ", vbQuestion + vbYesNo, "Weekly Report") = vbYes Then
            WeeklyTTIR Range("J2"), Range("K2")
        End If
    End If
End If
End Sub
0
 

Author Comment

by:Rrave26
ID: 40586742
This Workbook
Private Sub Workbook_Open()

Sheets("Home").Activate

End Sub
0
 

Author Comment

by:Rrave26
ID: 40586748
Worksheet Home


Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Private Sub TextBox1_Change()
End Sub

Private Sub CommandButton2_Click()
Sheets("Team Metrics").Select
End Sub

Private Sub CommandButton3_Click()
Sheets("Dashboard").Select
End Sub

Private Sub CommandButton4_Click()
Sheets("Weekly TTIR").Select
End Sub

Private Sub Label1_Click()

End Sub

Private Sub CommandButton5_Click()
Sheets("HBUS Dashboard").Select
End Sub

Private Sub CommandButton6_Click()
Sheets("HBCA Dashboard").Select
End Sub
0
 

Author Comment

by:Rrave26
ID: 40586756
Worksheet Dashboard

Private Sub Worksheet_Activate()
    ActiveWindow.ScrollRow = 1
End Sub

Private Sub CommandButton1_Click()
Sheets("Home").Select
End Sub
0
 

Author Comment

by:Rrave26
ID: 40586767
I am going to unitall 2013 and reinstall 2010 to see if there is an issue.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40586784
I have McAffee 8.8  I just noticed that there was an issue when I tried to upload the file to experts exchange.  I am trying to run the scan now.
What was the result of the scan?
0
 

Author Comment

by:Rrave26
ID: 40587003
The scan showed no virus.
0
 

Accepted Solution

by:
Rrave26 earned 0 total points
ID: 40587004
I am guessing that my file has become corrupt.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40587303
Save it as an xlsb file and see if you can then reopen it.
0
 

Author Comment

by:Rrave26
ID: 40588522
That didn't work either.
0
 

Author Closing Comment

by:Rrave26
ID: 40597927
All of the information and suggestions were excellent in troubleshooting this issue, however in the end the database still doesn't work and I will rebuild the database.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now