3

I would appreciate help solving this vexing problem with inserting a column into a worksheet with VBA. The worksheet is generated from a database. I need to insert a column into the worksheet for data that is not part of the database. The project takes the data from the worksheet and writes it to a series of Word forms. The code works fine when run from a module in the macro enabled worksheet. When I use the code in an Add-In (.xlam) it doesn't insert the column and an Error 438 is captured by Err.Number. When I put the module in a separate workbook and run it, the column is inserted but the error is still captured. I've tried a lot of the suggestions for debugging and fixing my code to run properly in an add-in. The add in is signed with a valid certificate which should allow it to run on our network. I started using the .EntireColumn.Insert but switched to Selection.Insert to see it that would work. Both versions fail to insert the column. I also changed the code to create a specific reference to the target worksheet instead of relying on ActiveWorkbook or Activesheet.

Edit: The worksheets are not protected.

I was using @Rory method for Excel VBA Add In Control. I tried @ErikEidt's answer to Standalone code for Excel to use a button on the Excel Quick Access Toolbar to run the Add-In.
I still get the error message but the code exectues and the column is inserted successfully.

Here is the relevant extract of my code.

Option Explicit
Sub FormfromExcell()

'Note:Requires Reference to Microsoft Word and Excel(15.0 for  2013)Object Library set in Tools>References

'DECLARE AND SET VARIABLES
' Excel Objects
    Dim WBA As Workbook 'The Active workbook
    Dim WBAname As String
    Dim wksht As Worksheet 'Excel Worksheet
    Dim shtName As String
    Dim myLastRow As Long
    Dim myLastCol As Long

' Initialize the Excel Objects
    Set WBA = ActiveWorkbook 'The workbook that calls the Add-In
    WBAname = ActiveWorkbook.Name
    shtName = WBA.Worksheets("Dynamic Risk Report").Name
    Set wksht = WBA.Worksheets("Dynamic Risk Report")
    'Set wksht = Workbooks("WBAname").Worksheets("shtName")

Application.StatusBar = "Checking for Status and Action Columns"

' Check for presence of non-database columns (Action and Status) and insert them if not there.
     If wksht.Cells(1, "D") = "Action" Then GoTo Checkforstatus

        'wksht.Range("D1").EntireColumn.Insert
         wksht.Columns("D:D").Select
         Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

     If Err.Number <> 0 Then MsgBox "Error" & Err.Number
        wksht.Cells(1, "D").Value = "Action"

Checkforstatus:

'Rest of code
Community
  • 1
  • 1
PFunk
  • 31
  • 4
  • Is there a way you can trap what line the error is being trapped? The only thing I can think of is that it might be generated on line `wksht.Columns("D:D").Select"` - and that might happen if the worksheet is not active. Does it make any difference if you add a line after StatusBar `wksht.Activate` – dbmitch Jun 25 '16 at 16:36
  • Thanks @dbmitch.The error occurs when the insert line executes. The correct row on the worksheet highlighted but the insert doesn't happen. The next step changes the header in the existing column D. I'll have to wait Until Monday to try the activate command, although I think I've tried that earlier in the debuging. – PFunk Jun 26 '16 at 15:50
  • I would check to see if the worksheet you are trying to insert to is protected. – Robin Mackenzie Jun 26 '16 at 16:36
  • I haven't had a chance to try @dbmitch suggestion yet. See my edit for additional information. – PFunk Jul 02 '16 at 16:09

1 Answers1

1

If you can eliminate the Select and just do a straight insert, will that work?

Also you try defining your own constants, just in case Add-In doesn't have access - even though that doesn't make sense to me, seeing as you're runnign this from Excel

Const xlToRight = -4161
Const xlFormatFromLeftOrAbove = 0

Dim rge     As Range

Replace this:

wksht.Columns("D:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

With this

Set rge = wksht.Columns("D:D")
rge.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
dbmitch
  • 5,361
  • 4
  • 24
  • 38