Go Premium for a chance to win a PS4. Enter to Win


Excel 2010: Macro-enabled workbook error

Posted on 2015-02-19
Medium Priority
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 1000 total points
ID: 40628586
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.

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

963 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