Macro debug is referring to other macro code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cb95amc - 29 Jun 2007 14:19 GMT

I have a file I created in Excel 2003, but in Excel 2000 when some of
the macros are run they give a Debug error....However, when you open
the debug window the line of code being referred to is in a different
macro to the on that should be running....

For example - On one sheet I have a combo box and a couple of command
buttons....In Excel 2003 everything works as it should....
However, in Excel 2000 the combo box works fine, but when you click
either of the command buttons it gives the following

run time error '1004' -Select Method of Range class failed

When you hit debug the line that is highlighted is in the CombBox
macro...The "Columns("D:AX").Select" line shown below.

All was well with the file a week ago, and I haven't changed any of
the macro code since then.....

Macros are shown below....

Private Sub ComboBox1_Change()

If Range("A5").Value = "Market" Then
HIDEALL
Columns("D:AX").Select
Selection.EntireColumn.Hidden = False
Columns("U:AK").Select
Selection.EntireColumn.Hidden = True
Application.Goto Reference:="R52C4"
Application.Goto Selection, True

End If

End Sub

Private Sub CommandButton1_Click()
ActiveSheet.Calculate
End Sub

Private Sub CommandButton3_Click()
With Columns("U:AK")
.Hidden = Not .Hidden
End With
ActiveSheet.Calculate
End Sub

Any suggestions...

Thanks

Andy

Reply to this Message

Dave Peterson - 29 Jun 2007 17:22 GMT

I gonna guess that you have either a linkedcell for that combobox on the same
sheet as the combobox or the listfillrange is on that same sheet.

xl2003 recalculates differently than xl2k. And I think that the recalculation
is either changing that linkedcell or the listfillrange (or thinks that it may
change because of the recalc).

And that fires the combobox1_change event.

If you have either (or both) of those, maybe you could put the linked cell and
listfillrange on a different sheet--or even use code to populate the combobox
with the list and use code to populate the cell with the value of the combobox.

> I have a file I created in Excel 2003, but in Excel 2000 when some of
> the macros are run they give a Debug error....However, when you open
[quoted text clipped - 47 lines]
>
> Andy

Signature

Dave Peterson

Reply to this Message

cb95amc - 05 Jul 2007 10:42 GMT

Thanks for the suggestion Dave..I'll give it a go...

However, the one thing I don't understand is why it worked fine in
Excel 2000 only a week ago....If the method of calculation was
fundamentally different, wouldn't this error have cropped up from the
start?

Thanks

Andy

Reply to this Message

Dave Peterson - 05 Jul 2007 12:28 GMT

I don't know--maybe other things changed in the workbook, too??????

> Thanks for the suggestion Dave..I'll give it a go...
>
[quoted text clipped - 6 lines]
>
> Andy

Signature

Dave Peterson

Reply to this Message

cb95amc - 10 Jul 2007 11:59 GMT

Did as you suggested Dave and put the listfillrange etc on a different
sheet......This seemed to work initially as the person I sent it to
reported it working OK...
However, now it seems that it has stopped working again, and even the
new file I sent across that worked OK last week is now giving the same
error message, and referring to the same line of code on the macro...

Can't get my head round this one :-)

Any ideas?

Reply to this Message

Dave Peterson - 10 Jul 2007 12:07 GMT

Maybe it's time to drop the listfillrange and linkedcell and do everything in
code?

> Did as you suggested Dave and put the listfillrange etc on a different
> sheet......This seemed to work initially as the person I sent it to
[quoted text clipped - 6 lines]
>
> Any ideas?

Signature

Dave Peterson

Reply to this Message

cb95amc - 10 Jul 2007 14:24 GMT

Forgive my ignorance Dave, but what is the best way to do this...If
you hadn't guess already Macros aren't my strong point...

Thanks

Reply to this Message

Dave Peterson - 10 Jul 2007 14:49 GMT

Depending on when you want that combobox populated, you could do something like
this in a general module:

Option Explicit
Sub auto_open()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet2")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("sheet1")
.ComboBox1.Style = fmStyleDropDownList
.ComboBox1.List = myRng.Value
End With
End Sub

And behind the worksheet that owns that combobox:

Option Explicit
Private Sub ComboBox1_Change()

If Me.ComboBox1.ListIndex = -1 Then
'nothing selected, so do nothing
Else
Me.Range("a1").Value = Me.ComboBox1.Value
End If

End Sub

> Forgive my ignorance Dave, but what is the best way to do this...If
> you hadn't guess already Macros aren't my strong point...
>
> Thanks

Signature

Dave Peterson

Reply to this Message