Solved

Excel 2010: Macro-enabled workbook error

Posted on 2015-02-19
10
57 Views
Last Modified: 2016-02-12
I have an Excel workbook with a worksheet that runs VBA code when a button is clicked. On one computer, when I open the worksheet, I get the following error message:

Can't exit design mode because Control 'btnImportEarnings' can not be created

This error occurs even if I'm logged into the computer as an administrator. I don't get this error on other computers.

I'm aware of the Office update in December 2014 that broke macros in office. I've run the "FixIt" on the computer in question, but that did not fix the issue.
0
Comment
Question by:Perfishent
  • 5
  • 5
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Does it work if you replace the button with either a Forms button, rather than ActiveX, or a Shape and assign a macro to it? If so, it's almost certainly related to the December updates.
0
 

Author Comment

by:Perfishent
Comment Utility
There's not a macro to assign, because the button runs Visual Basic code. I think I have to use an ActiveX button to run VBA code.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Macros are written in VBA code. You might need to amend the code to make it run in a normal module - what is the code?
0
 

Author Comment

by:Perfishent
Comment Utility
I'm connecting to a Pervasive SQL database to get data for some calculations. Here's the code:

Option Explicit

Dim beginDate as String
Dim endDate as String
Dim sql As String
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim theCompany As Integer
Dim strConn As String
Dim payDate As String
Dim apexCo As Integer
Dim caseNum As String
Dim caseSSN As Long
Dim caseNo As String
Dim strSQL As String
Dim beginDt As String
Dim endDt As String
Dim lastRow As Integer
Dim lastCol As Integer
Dim curYr As Integer
Dim i As Integer

Private Function getGross()

    sql = ""
    curYr = Year(Date)

    lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    lastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
   
    For i = 1 To lastRow
   
        If UCase(Cells(i, 1)) = "Y" And Cells(i, 2) <> "" And Cells(i, 3) <> "" And Cells(i, 4) <> "" And Cells(i, 4) > 10 And Cells(i, 4) < 20 Then
   
            apexCo = Cells(i, 4)
            caseSSN = Cells(i, 7)
            caseNum = Cells(i, 8)
            beginDt = Format(Cells(i, 2), "yyyy-mm-dd")
            endDt = Format(Cells(i, 3), "yyyy-mm-dd")
            Cells(i, 1) = "N"
       
            ' Get gross pay that does not include non-taxable items (tax_type 4)
            strSQL = "SELECT m.ss_no, SUM(e.pay_amt) AS pay_amt" & _
                " FROM APEX" & apexCo & curYr & ".pr_earn e INNER JOIN APEX" & apexCo & curYr & ".cl_ptype p ON e.pay_code = p.code and e.loc_no = p.loc_no" & _
                " INNER JOIN APEX" & apexCo & curYr & ".pr_mast m ON m.loc_no = e.loc_no and m.emp_no = e.emp_no and m.ss_no = " & caseSSN & _
                " INNER JOIN APEX" & apexCo & curYr & ".pr_ptype pt on pt.code = e.pay_code" & _
                " WHERE pt.yn_6 <> 'N' and e.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "'" & _
                " GROUP BY m.ss_no"
               
                Debug.Print strSQL
                       
            strConn = "Driver={Pervasive ODBC Client Interface};ServerName=xxxxxx.1583;ServerDSN=APEX" & _
                        CStr(apexCo) & CStr(curYr) & ";UID=xxxxx;PWD=xxxxxx;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP:SPX;"
            conn.Open strConn
            Set cmd.ActiveConnection = conn
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
   
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
                If rs!pay_amt > 0 Then
                    Cells(i, 31) = Round(rs!pay_amt, 2)
                End If
            End If
           
            'TAXES
            strSQL = "SELECT m.emp_no, m.loc_no, " & _
            " sum(i.amt_12) as Fed_Tax, sum(i.fica_5) as Soc_Sec, sum(i.medc_5) as Medc, ifnull(sum(i.amt_13),0) + ifnull(sum(i.amt_20),0) as State_Tax," & _
            " MAX(i.per_start) as per_start, MAX(i.per_end) as per_end" & _
            " FROM APEX" & apexCo & curYr & ".pr_mast m" & _
            " INNER JOIN APEX" & apexCo & curYr & ".pr_inp i ON i.emp_no = m.emp_no and i.loc_no = m.loc_no" & _
            " LEFT JOIN APEX" & apexCo & curYr & ".pr_cded cd ON cd.item_no = i.item_no and cd.ref_no = i.ref_no and cd.summ_no = i.summ_no" & _
            " WHERE m.ss_no = " & caseSSN & _
            " AND i.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "'" & _
            " GROUP BY m.emp_no, m.loc_no"
           
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
   
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
'                Cells(i, 27) = Round(rs!per_start, 2)
'                Cells(i, 28) = Round(rs!per_end, 2)
'                Cells(i, 30) = Round(rs!fed_tax, 2)
'                Cells(i, 31) = Round(rs!soc_sec, 2)
'                Cells(i, 32) = Round(rs!medc, 2)
'                Cells(i, 33) = Round(rs!state_tax, 2)
               
                Cells(i, 29) = Round(rs!per_start, 2)
                Cells(i, 30) = Round(rs!per_end, 2)
                Cells(i, 32) = Round(rs!fed_tax, 2)
                Cells(i, 33) = Round(rs!soc_sec, 2)
                Cells(i, 34) = Round(rs!medc, 2)
                Cells(i, 35) = Round(rs!state_tax, 2)
            End If
           
            'OTHER DEDUCTIONS
            strSQL = "SELECT SUM(cd.ee_amt) as other_ded, MAX(md.descrip) as Descrip FROM APEX" & apexCo & curYr & ".pr_cded cd INNER JOIN APEX" & apexCo & curYr & ".pr_mast m on m.loc_no = cd.loc_no and m.emp_no = cd.emp_no" & _
            " and m.ss_no = " & caseSSN & _
            " INNER JOIN pr_mded md ON md.code = cd.code and md.plan = cd.plan WHERE cd.pay_date BETWEEN '" & beginDt & "' AND '" & endDt & "' and cd.code BETWEEN 100 AND 144"
       
            cmd.CommandTimeout = 360 ' Keep queries from timing out after the default of 30 seconds
            cmd.CommandText = strSQL
            cmd.CommandType = adCmdText
            Set rs = cmd.Execute
           
            If Not (rs.BOF And rs.EOF) Then
                rs.MoveFirst
'                Cells(i, 39) = Round(rs!other_ded, 2)
'                Cells(i, 40) = Trim(rs!descrip)
               
                Cells(i, 41) = Round(rs!other_ded, 2)
                Cells(i, 42) = Trim(rs!descrip)
            End If
            rs.Close
            conn.Close
               
        End If

    Next i
   
    MsgBox "Update complete."

End Function

Private Sub btnImportEarnings_Click()
    Call getGross
End Sub
0
 

Author Comment

by:Perfishent
Comment Utility
The bottom line is that there's something different about this one computer that keeps the program from running. And the difference is not specific to any one user profile. Strangely, other macro-enabled (vba coded) Excel spreadsheets will run on this computer.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
You should be able to just move all that code to a normal module, make the btnImportEarnings_Click() routine public and assign it to a forms button or shape.
0
 

Author Comment

by:Perfishent
Comment Utility
My point is that I shouldn't have to change the code in any way, because the code runs just fine on most computers. It's just this one computer where it won't run. I'm looking for a setting or a permission somewhere that is keeping the code from running on the one computer. Maybe I should just re-install Office and see if that fixes it.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
I agree, but I think you've run into this issue: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/

Until there's a proper patch for the problem (and in fact even then), I think it is better to avoid ActiveX controls on worksheets if at all possible.
0
 

Author Closing Comment

by:Perfishent
Comment Utility
This is more of a workaround than a true solution. The Active X control works on all of the computers but one.  Fixing Active X on the one computer would be a true solution.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
How exactly would you expect me to repair a buggy Microsoft patch for you??
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

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,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 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

11 Experts available now in Live!

Get 1:1 Help Now