Excel VBA create listboxes not working - error 1004

[oitbc]

Hi,

Done some searching and sheet is not protected as others have had the issue with - its just being created as a new workbook.

So this is part of a word macro I am updating that I wrote before, but inside the macro I call the Excel creation function. The goal is I want to create an excel workbook that has all the comments in it. I've done this when word extracted to another word document fine, but this time the client needs an excel sheet for the comments. I can create a workbook, do all the headings, formatting etc. but when I get to create a listBox for an area, it complains with an Error 1004 and I don't know why. I'm sure its somethign obvious and I am just not seeing it.

You just need top see the last Range command below, but I put everything in so you can see what it is doing beforehand that works.

'Create New Excel Workbook
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set cwb = objExcel.Workbooks.Add

'Insert table headings
With objExcel

.ActiveSheet.Name = "Comments"
'Create merged cell headings
.Range("A1:J1").Merge
.Range("K1:O1").Merge
.Range("P1:Q1").Merge
'Create merged cells values
.Range("A1").HorizontalAlignment = xlCenter
.Range("K1").HorizontalAlignment = xlCenter
.Range("P1").HorizontalAlignment = xlCenter
' Centre merged cells
.Range("A1").Value = "Reviewer to complete (Stage 1)"
.Range("K1").Value = "Author to complete (End of Stage 1)"
.Range("P1").Value = "Reviewer to complete (Stage 2)"
'Create secondary headings
.Range("A2").Value = "Date"
.Range("B2").Value = "Reviewed Version"
.Range("C2").Value = "Ref"
.Range("D2").Value = "Section"
.Range("E2").Value = "Page"
.Range("F2").Value = "Referenced Quote/Context"
.Range("G2").Value = "Comment"
.Range("H2").Value = "Reviewer Name"
.Range("I2").Value = "Organisation"
.Range("J2").Value = "Content or Format comment"
.Range("K2").Value = "Accept/Reject/Defer"
.Range("L2").Value = "Author Response/Comment"
.Range("M2").Value = "Author Initials"
.Range("N2").Value = "Update complete"
.Range("O2").Value = "Updated in version"
.Range("P2").Value = "Reviewer response"
.Range("Q2").Value = "Resolved to review satisfaction?"
.Range("R2").Value = "Status - Open/Closed"
'Set colours for Cells
.Range("A1:J2").Interior.ColorIndex = 40
.Range("K1:O2").Interior.Color = RGB(204, 255, 204)
.Range("P1:Q2").Interior.ColorIndex = 40
.Range("R1:R2").Interior.ColorIndex = 1
'Set Text to White for specific cell
.Range("R1:R2").Font.Color = RGB(255, 255, 255)
'Bold Text
.Range("A1:R2").Font.Bold = True
'set border around cells
.Range("A1:R500").Borders.LineStyle = xlContinuous
.Columns("A:R").WrapText = True
'Ensure Col F and G are reasonable size
.Columns("A:B").ColumnWidth = 10
.Columns("C").ColumnWidth = 7
.Columns("D").ColumnWidth = 20
.Columns("E").ColumnWidth = 10
.Columns("F:G").ColumnWidth = 60
.Columns("H").ColumnWidth = 16
.Columns("I").ColumnWidth = 12
.Columns("J").ColumnWidth = 10
.Columns("K:L").ColumnWidth = 20
.Columns("M").ColumnWidth = 7
.Columns("N:O").ColumnWidth = 8
.Columns("P").ColumnWidth = 30
.Columns("Q").ColumnWidth = 12
.Columns("R").ColumnWidth = 14
.Range("K3:K500").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer"
End With

 

[oitbc]

It is being checked in the Word VBA Editor. Interestingly I just moved it up in Prioirity, so I know have VB for Apps, Ms Word 16 Object library and then MS Excel 16 Object library and it now works!

 

[oitbc]

Does that mean if I push the macro out ot other people to use, I need to add step for them to add the Excel item into their library and then they have to move it up in prioirty as well for it to work?

 

[RoryA]

No, the reference is saved with your Word doc. If you need to worry about different versions of Excel, then you should simply declare the Excel constants yourself and skip the reference.

 

[oitbc]

Thank you all for your help. Much appreciated.