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

 

[Yongle]

I do not see any code to create a listbox
- are you referring to the line that adds a "list" via data validation ?

 

[oitbc]

Yes, sorry a dropdownlist for each cell - .Range("K3:K500").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer" should create as documented here Validation.Add method (Excel) and I did find a sample piece of code here Code or Program a Data Validation List - VBA Code Examples which aligns with mine

 

[mikerickson]

Have you tried it with commenting out the .Merge line for that column?

.Range("K1:O1").Merge: Rem comment me out

 

[oitbc]

I just tried it now and it makes no difference having commented out the merge for that set of Cols. - still brings back a Run-time error 1004: Application -def or object def error. The same if you move it so it goes directly under the renaming of the sheet before you do anything else.

 

[mikerickson]

Have you tried

With Range("K3:K500").Validation
    .Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer"
End With

The .Delete line doesn't seem necessary, but its in what the Macro Recorder gave me.

 

[oitbc]

Tried the code and it didnt like Range, so I added a . in front and it was then happy with that line. Debug shows it is now failing on the same part as before

.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer"

and I thought maybe it had validation twice so I changed it to

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer"

but it made no difference.

With .Range("K3:K500").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Accept,Reject,Defer"
End With

 

[RoryA]

Do you have a reference set to the Excel object library? You need one with that code as written, or you need to declare the Excel constants that you are using.

 

[oitbc]

Ok, thats strange. I go to Tools, Refeences and select MS Excel 16.0 Object Library and confirm it is ticked and click ok. I then go back to Tools, references and I can see it in my top 5 items ticked. I then run the macro from word and it stops with the usual error. I go back into Tools References and MS Excel 16.0 is not ticked any more.

I do still have BA for Apps, MS Word 16 Object Library, OLE Automation, MS Office 16.0 Object library all still ticked though.

 

[RoryA]

The reference needs to be checked in Word, not Excel.