Excel 2010: Macro-enabled workbook error

Posted on 2015-02-19
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.
Question by:Perfishent
  • 5
  • 5
LVL 85

Expert Comment

by:Rory Archibald
ID: 40619239
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.

Author Comment

ID: 40619262
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 40619305
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?
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.


Author Comment

ID: 40619326
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
                If rs!pay_amt > 0 Then
                    Cells(i, 31) = Round(rs!pay_amt, 2)
                End If
            End If
            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
'                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
            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
'                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
        End If

    Next i
    MsgBox "Update complete."

End Function

Private Sub btnImportEarnings_Click()
    Call getGross
End Sub

Author Comment

ID: 40619884
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 40620754
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.

Author Comment

ID: 40628524
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.
LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 40628586
I agree, but I think you've run into this issue:

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.

Author Closing Comment

ID: 40644735
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.
LVL 85

Expert Comment

by:Rory Archibald
ID: 40644806
How exactly would you expect me to repair a buggy Microsoft patch for you??

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

815 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